A userform technique for the adv users...

  • A more efficient way to associate cells with controls on a userform.

    Here's something interesting I came up with (or at least I think it's interesting) that has made associating cells with controls on userforms a much simpler task.

    Often times, userform developers make the loading/posting of control data much too cumbersome. Sometimes even assigning code to each control (gag).

    I'd post this in the cool stuff section, but I'm interested in your responses. Perhaps you may even have a better technique to share.

    You've probably already encounted the situation, you've got a userform with lots of controls on it that are going to update cell values when a user clicks OK or something like that.

    You don't want the control to link directly to the cell because maybe the user decides halfway through the form to cancel the action. So, you want to maintain the ability to leave the values in the cells untouched unless the user specifically enters the OK button to post all the changes.

    So, you create some code in an OK button that goes through each control and sets certain cells equal to the values of the controls. For instance...

    Range("A1").Value = Combobox1.Value
    Range("A2").Value = Combobox2.Value
    and so on...

    But then later you decide to go in and add another control. Now you have to update the code in the OK button to account for the new combobox. That's a pain. Here's how I set it up to streamline the operation:

    Controls have a seldom used property called "Tag". It's there basically as a variable for the control to use as you please. I like to store the associated cell addresses for each control using the tag property. This way I can simply loop through every control on the userform and if a tag exists for a control, I assume it's my cell reference for that control and I can just set the value of that cell equal to the value of the control.

    Here's my OK button code:

    Private Sub Btn_OK_Click()
    Dim Ctrl As Control
    For Each Ctrl In UserForm1.Controls
    If Ctrl.Tag <> "" Then
    Range(Ctrl.Tag).Value = Ctrl.Text
    End If
    Next Ctrl
    End Sub

    Now, if I add a new control to my userform and I want it to update a value in a certain cell, all I have to do is add the control and enter the cell address in the tag for the control. The OK button now automatically picks it up!

    Likewise, it makes the code for loading the values in the controls very simple as well. I just include some loading code (very similar to the posting code) in the activate event of the form.

    Private Sub Load_Controls()
    Dim Ctrl As Control
    For Each Ctrl In UserForm1.Controls
    If Ctrl.Tag <> "" Then
    Ctrl.Text = Range(Ctrl.Tag).Text
    End If
    Next Ctrl
    End Sub

    Now I can freely add controls to my userforms and just set the tag to a range ref and I know the control will load and post it's data without modifying any code. This allows me to focus my attention more on how I want the control value to be used in calculations (the important stuff) and I don't need to waste time thinking about how I will manage the loading/posting part.

    Keep in mind, this is different than using the ControlSource property in that it doesn't update the cell live. The idea is to wait for the user to click OK before making all the updates. This way the user can click CANCEL at any time and none of the changes are recorded.

    Feel free to share your thoughts?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Aaron,

    Though I wouldnt put myself in the adv users club yet.... that works very nicely.

    I have used the code found on this site for adding a button to a right click menu and couldnt figure out what the .tag = "brccm" part of the code was doing - I still dont - but now I can have a go :bsmile: .

    I tried to take it a step further and have the user form place a vlaue in another spreadsheet (No particular reason, but it may come in handy some time). I tried setting the tag as:
    'C:/Documents and Settings/Administrator/My Documents/[test.xls]Sheet1'!$A$7
    but it didnt like that, whether the book was open or closed.

    What do you think?

    Thanks for the tip!!!

  • Quote

    Originally posted by Dave Hawley
    Hi Aaron

    I used something similar in my newsletter in March last year, see the VBA section here: http://www.ozgrid.com/News/ConFormatCheckFind.htm

    Great minds think-alike:cheers:

    Yes, I see you have used a control tag in a similar fashion.

    Using the offset like that doesn't exactly convey the same message. But it's nice to see other examples.

    Thanks for pointing it out.

    This is probably taking it a step too far for this discussion. I didn't want to muddy the water of my original post with this but I've even gone so far as to put a call to the following code in the activation event for the userform just before I load values for the controls.

    Sub Control_TagInit()
    Dim Ctrl As Control
    On Error Resume Next
    For Each Ctrl In Me.Controls
    If Ctrl.Text <> "" Then
    Ctrl.Tag = Ctrl.Text
    End If
    Next Ctrl
    End Sub

    So what's the point? Well, I just can't leave well enough alone I guess.

    To make the relationship between controls and the cells (OK, or range names) even more obvious to me in the userform editor I just click on the textbox/combobox and enter the range ref right in the control and don't even bother with assigning the tag. Then at activation I set the tags equal to the text of the controls with that bit of code above.

    Immediately following the TagInit code in the activation, I call the code to read the values into the controls. It all happens instantly, so you don't see the cell refs in the controls, but I sure feel comfy seeing the controls sitting on my userform editor with all the range refs visible in each control. Besides, I'm lazy and don't like to actually have to scroll the property window to actually find the tag property (yes, it is rather pathetic).

    It just happens to work out nicely that labels have a caption property instead of text so they error out on the invalid property and we can step over them with the familiar RESUME NEXT catchall.

    I actually get a chuckle out of the TagInit coding. You just have to laugh a little bit at the idea of it. :lol:

    Yes, I'm easily amused.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Hi Aaron, based on last tip i have ammended some code to this:

    Dim CBox As Control
    Dim I As Integer

    Private Sub UserForm_Activate()
    I = 0
    For Each CBox In Me.Controls
    I = I + 1
    If CBox.Name = "CheckBox" & I Then
    CBox.Caption = Cells(1, I).Text
    CBox.Tag = I
    CBox.ControlTipText = "Show/Hide " & Cells(1, I).Text
    End If
    Next CBox
    End Sub

    Where 26 checkboxes need to display row1 headings for Showing/Hiding catagories. No error check is used as i am specifically checking for checkbox names. Works great.

    Code to show/hide columns:

    Private Sub ShowCols()
    Set CBox = Me.ActiveControl
    If CBox.Value = True And Columns(Int(CBox.Tag)).Hidden = True Then
    Columns(Int(CBox.Tag)).Hidden = False: Cells(1, Int(CBox.Tag)).Select
    Columns(Int(CBox.Tag)).Hidden = True: Cells(1, Int(CBox.Tag)).Select
    End If
    Set CBox = Nothing
    End Sub

    Private Sub CheckBox1_Click()
    End Sub

    Private Sub CheckBox2_Click()
    End Sub

    Private Sub CheckBox3_Click()
    End Sub
    'etc, etc 26 checkboxes

    Question: If I may, how to call 'ShowCols' without having 26 checkbox click calls??

  • If it were me, this wouldn't be a problem because I'd just use a single listbox control to refer to the columns instead of a button for each one.

    Then maybe have a single "OK" button to click after I've made my listbox selections to decide which column(s) would be hidden, etc.

    You can store additional bits of info about each range ref in different fields of your listbox list array (perhaps if column width equals zero you might note the column ref is hidden).

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Good point,

    Something like this seems to work fine for me in a tag if the workbook is open:


    Now, if the workbook is closed it gets more complicated. But you could do it.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • If you reference column numbers in the tag property and then later on you need to insert a new column right in the middle of the spreadsheet (let’s say column 15) ..., all of the current tags from 15 on will need to increase by 1 ... is there a way to loop through all the tags and update them without having to manually adjust all of the tags in the property window ?

  • royUK

    Closed the thread.