Results 1 to 18 of 18

How to reference Controls in a Userform based on the control's tabindex instead of name?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-21-2013
    Location
    Corvallis, OR
    MS-Off Ver
    Excel 2010
    Posts
    174

    How to reference Controls in a Userform based on the control's tabindex instead of name?

    I have a potentially unique situation... I have a userform with a multipage that contains 20 comboboxes which all reference the same list of items. The rowsource for each of these comboboxes is a list of search options which each have 1 of 3 different logical dependencies (the search text contains, vs the search value is between value1 & value2, vs the search is logical; a true/false option.)

    To the side of each of these comboboxes are a set of textboxes, checkboxes, and labels stacked on top of eachother which are preset to .visible = false. When the value in the combox is changed, its Before_Update event triggers to set the appropriate textbox(s), checkbox(s), and/or label(s) to .visible = true based on the appropriate logical dependency for the selected search option. All but the first of these 20 comboboxes have .visible preset to false as well, and then when the first textbox or checbox changes (again via the before_update even for each), the next combobox is set to .visible = true so that another search option can be picked from the list.

    It is set up this way because the list of search options is overwhelming if they are all visible at the same time. While it is highly unlikely that a user will ever need more than 10 or so of the available options at once, the number of available options exceed 180, so having them all on the form visible is simply not an option...

    The problem I'm having is that when .visible = true is set for the appropriate textboxes, checkboxes, and labels, I can't for the life of me get the focus to set on the appropriate control. The after_Update even doesn't work either. The change event would work, except that I want the users to be able to scroll using up and down arrows through the list of search options in each combobox, so the change event would trigger with each new selection and exit the combobox prematurely... Obviously, I can't have that, so...

    My idea is that in the multipage change event, I could specify that when on this page (page 6 in this case), the event could identify the active control's tabindex and specifically select the next control based on that tabindex. (something like activecontrol.tabindex + 1), but I don't know how or if there is a way to do this.

    Any help is much appreciated!

    For your review, the following is part of the code I have for a single combobox and its associated labels, textboxes, and checkboxes. Aside from setting focus appropriately, the code functions as I had hoped.

    The control names might be a tad confusing, so here's an explanation:
    "SearchOption1" is the first combobox.
    "SO1Contains" is a textbox for searching for text containing said value
    "SO1Between1" and "SO1Between2" are textboxes for values to search between
    "SO1Between3" is a label that goes between them ("and")
    "SO1CB" is a true/false checkbox for searching where a value is true or false
    "LblS2" is the label for 2nd combobox, titled "SearchOption2", both of which are made visible by changes to the above textboxes, and checbox.

    Private Sub SearchOption1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.MP1.Value = 6 Then
    'do what needs done
        Dim OptNum As Integer
        Dim DataAddrs As String
        Dim LgcArg As String
        Dim ArgType As String
        
        If Me.SearchOption1.Value = vbNullString Then
        Cancel = True
        Exit Sub
        Else
        OptNum = Left(Me.SearchOption1.Value, WorksheetFunction.Find(":", Me.SearchOption1.Value) - 1) * 1
        DataAddrs = Sheets("Defs").Range("Q" & OptNum + 100).Value
        LgcArg = Sheets("Defs").Range("R" & OptNum + 100).Value
        ArgType = Sheets("Defs").Range("T" & OptNum + 100).Value
            
            If Me.SearchOption1.Value <> vbNullString Then
            Sheets("Defs").Range("B61").Value = "Yes"
            Sheets("Defs").Range("C61").Value = OptNum + 100
            Sheets("Defs").Range("D61").Value = LgcArg
            Sheets("Defs").Range("E61").Value = ArgType
            Sheets("Defs").Range("J61").Value = DataAddress
            Else
            Sheets("Defs").Range("B61").Value = "No"
            Sheets("Defs").Range("C61").Value = vbNullString
            Sheets("Defs").Range("D61").Value = vbNullString
            Sheets("Defs").Range("E61").Value = vbNullString
            Sheets("Defs").Range("J61").Value = vbNullString
            End If
            
            If LgcArg = "Contains" Then
            Me.SO1Contains.Visible = True
            Else
            Me.SO1Contains.Visible = False
            End If
            
            If LgcArg = "Between" Then
            Me.SO1Between1.Visible = True
            Me.SO1Between2.Visible = True
            Me.SO1Between3.Visible = True
            Else
            Me.SO1Between1.Visible = False
            Me.SO1Between2.Visible = False
            Me.SO1Between3.Visible = False
            End If
            
            If LgcArg = "True/False" Then
            Me.SO1CB.Visible = True
            Me.SearchOption2.Visible = True
            Me.LblS2.Visible = True
            Else
            Me.SO1CB.Visible = False
            End If
            
        End If
        
    
    End If
    End Sub
    
    Private Sub SO1Between1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Sheets("Defs").Range("E61").Value = "Date" Then
        If Not IsDate(Me.SO1Between1.Value) Then
        MsgBox "You must enter an actual date."
        Me.SO1Between1.Value = vbNullString
        Cancel = True
        Exit Sub
        End If
    Else
    If Sheets("Defs").Range("E61").Value = "Value" Then
        If Not IsNumeric(Me.SO1Between1.Value) Then
        MsgBox "You must enter an actual number."
        Me.SO1Between1.Value = vbNullString
        Cancel = True
        Exit Sub
        End If
    End If
    End If
    End Sub
    Private Sub SO1Between2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Sheets("Defs").Range("E61").Value = "Date" Then
        If Not IsDate(Me.SO1Between2.Value) Then
        MsgBox "You must enter an actual date."
        Me.SO1Between2.Value = vbNullString
        Cancel = True
        Exit Sub
        Else
        Me.SearchOption2.Visible = True
        Me.LblS2.Visible = True
        Me.SearchOption2.SetFocus
        End If
    Else
    If Sheets("Defs").Range("E61").Value = "Value" Then
        If Not IsNumeric(Me.SO1Between2.Value) Then
        MsgBox "You must enter an actual number."
        Me.SO1Between2.Value = vbNullString
        Cancel = True
        Exit Sub
        Else
        Me.SearchOption2.Visible = True
        Me.LblS2.Visible = True
        Me.SearchOption2.SetFocus
        End If
    End If
    End If
    End Sub
    
    Private Sub SO1Contains_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.SO1Contains.Value = vbNullString Then
    Exit Sub
    Else
    Me.SearchOption2.Visible = True
    Me.LblS2.Visible = True
    Me.SearchOption2.SetFocus
    End If
    
    End Sub
    Last edited by bmxfreedom; 10-31-2013 at 04:58 PM. Reason: Control names may have been confusing...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-27-2013, 12:04 PM
  2. Replies: 1
    Last Post: 10-12-2012, 04:36 AM
  3. 2 Control buttons, 1 UserForm, Reference 2 Columns
    By Jacques Grobler in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-15-2012, 05:46 AM
  4. Code to format number input in textbox controls (based on tag) in userform
    By asha3010 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-05-2010, 01:09 PM
  5. Reference Userform Multipage Control
    By kingdt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2010, 06:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1