+ Reply to Thread
Results 1 to 6 of 6

Cleaner code for looping through userform controls (beginner)

Hybrid View

  1. #1
    Registered User
    Join Date
    05-06-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    41

    Cleaner code for looping through userform controls (beginner)

    I have a UserForm containing two Frames with 3 combinations of visibility/invisibility.
    I have 3 separate codes to control which empty ComboBox takes the SetFocus. (see code below)
    I did it this way to address the errors that were being thrown by the empty ComboBoxes in the invisible Frames.

    My question is in two parts.
    1. How can I make the 3 separate codes into 1?
    2. What modification will I have to make when I have a UserForm with both ComboBoxes and TextBoxes?

    I would appreciate help from anyone who takes the time.

    'When Frame2 is invisible
     
    Sub setFocus1 ()
    
    Dim ctl as MSForms.Control
    
    For each ctl in Frame1.Controls
    If TypeName(ctl)  = "ComboBox" Then
    If ctl.Text = Empty Then
    ctl.SetFocus
    Exit For 
    End if
    End if
    Next ctl
    
    End sub
    'When Frame1 is invisible
     
    Sub setFocus2 ()
    
    Dim ctl as MSForms.Control
    
    For each ctl in Frame2.Controls
    If TypeName(ctl)  = "ComboBox" Then
    If ctl.Text = Empty Then
    ctl.SetFocus
    Exit For 
    End if
    End if
    Next ctl
    
    End sub
    'When both Frames are visible
     
    Sub setFocus3 ()
    
    Dim ctl as MSForms.Control
    
    For each ctl in Me.Controls
    If TypeName(ctl)  = "ComboBox" Then
    If ctl.Text = Empty Then
    ctl.SetFocus
    Exit For 
    End if
    End if
    Next ctl
    
    End sub
    Last edited by melpa; 09-20-2019 at 12:07 AM.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Cleaner code for looping through userform controls (beginner)

    Frame is just another control so you can condition test it

    2.
    you cannot set focus to both control box and/or text...only 1 thing can have focus? so not sure what actually want to do

    anyways i setup a little test file so you can see two things
    frame visible switching and focus swapping via command button
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Cleaner code for looping through userform controls (beginner)

    Sub setFocus4()
        
        Dim ctl As MSForms.Control
        
        For Each ctl In Me.Controls
            If TypeName(ctl) = "ComboBox" Then
                If ctl.Text = Empty And ctl.Parent.Visible Then
                    ctl.SetFocus
                    Exit For
                End If
            End If
        Next ctl
        
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Cleaner code for looping through userform controls (beginner)

    This code expands on that posted by humdingaling.

    I moved the setfocus loop into a function which you can pass the parent control and the type of control you want to have focus. Note this currently only works for controls that support the Text property.

    Private Function m_SetFocus(Parent As Object, Name As String) As Boolean
    
        Dim ctl As msforms.Control
        Dim returnValue As Boolean
        
        returnValue = False
        On Error GoTo ErrSetFocus
        
        For Each ctl In Parent.Controls
            If StrComp(TypeName(ctl), Name, vbTextCompare) = 0 Then
                If ctl.Text = Empty Then
                    ctl.SetFocus
                    returnValue = True
                    Exit For
                End If
            End If
        Next ctl
    
    ErrSetFocus:
        m_SetFocus = returnValue
        Exit Function
    End Function
    
    Private Sub cmdSwitch_Click()
    
        If Me.Frame1.Visible = True Then
            Me.Frame1.Visible = False
            Me.Frame2.Visible = True
            
            ' try for a text box
            If Not m_SetFocus(Frame2, "TextBox") Then
                ' otherwise a combobox
                m_SetFocus Frame2, "ComboBox"
            End If
        Else
            Me.Frame1.Visible = True
            Me.Frame2.Visible = False
                
            m_SetFocus Frame1, "ComboBox"
        End If
        
    End Sub
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    05-06-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    41

    Re: Cleaner code for looping through userform controls (beginner)

    Thank you, thank you, thank you!

    It still blows me away that I can ask a question here and get amazing answers from all over the world.
    Humdingaling and Andy Pope, I have downloaded your files and will study them. Thank you both.
    Humdingaling, I understand that you can't SetFocus on two things at once. I am challenged by them being different types of control and how to address that. I trust I have enough material here now to learn how to do that.
    Finally, thank you AlphaFrog for that beautiful simplictity. Immediate gratification!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Cleaner code for looping through userform controls (beginner)

    That's one of the best Thank Yous I seen in awhile. You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Inserting image into userform controls strictly through code
    By DJams in forum Excel Programming / VBA / Macros
    Replies: 42
    Last Post: 11-24-2023, 03:55 AM
  2. [SOLVED] Choosing particular Userform controls array in a line of code.
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2018, 02:04 PM
  3. [SOLVED] Looping Through Userform Controls
    By omagoodness in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-23-2016, 02:45 PM
  4. Code for userform controls
    By Damperman in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-10-2013, 01:56 PM
  5. Deleting Userform controls through code (Help)
    By stuart010 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-26-2011, 10:12 AM
  6. userform with looping code
    By 00Able in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 02-22-2011, 09:10 PM
  7. [SOLVED] Problem with looping through userform controls
    By Jeremy Gollehon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2005, 10:06 AM

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