+ Reply to Thread
Results 1 to 5 of 5

Skip hidden control when looping all controls on a userform

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    276

    Skip hidden control when looping all controls on a userform

    Hello, I have this function that stopped working when I added the line "If ctrl.Visible = True Then".

    the function checks all controls on a userform to see if it is filled. I have a bunch of them invisible depending on user selection in another combobox. I need the function to ignore those hidden ones because they won't be filled.

    can someone please help me fix it? Thanks in advance!

    Private Function EverythingFilledIn() As Boolean
    
        Dim AnythingMissing As Boolean
        EverythingFilledIn = True
        
        Dim ctl As MSForms.Control
        
        For Each ctl In Controls
         If ctrl.Visible = True Then
            If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
                If ctl.Value = "" Then
                  ctl.BackColor = rgbPink
    
                  If Not AnythingMissing Then ctl.SetFocus
                  AnythingMissing = True
                  
                  EverythingFilledIn = False
                End If
            End If
            End If
        Next ctl
        
     
    End Function
    Last edited by lynnsong986; 10-16-2020 at 01:41 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,159

    Re: Skip hidden control when looping all controls on a userform

    How about
    Private Function EverythingFilledIn() As Boolean
    
        Dim AnythingMissing As Boolean
        EverythingFilledIn = True
        
        Dim ctl As MSForms.Control
        
        For Each ctl In Controls
          If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
             If ctl.Visible = True Then
                If ctl.Value = "" Then
                  ctl.BackColor = rgbPink
    
                  If Not AnythingMissing Then ctl.SetFocus
                  AnythingMissing = True
                  
                  EverythingFilledIn = False
                End If
            End If
          End If
        Next ctl
        
     
    End Function

  3. #3
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Office365
    Posts
    276

    Re: Skip hidden control when looping all controls on a userform

    oh I see. works perfectly now! Thank you so much Fluff13!!

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,159

    Re: Skip hidden control when looping all controls on a userform

    You're welcome & thanks for the feedback.

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Skip hidden control when looping all controls on a userform

    Try this:


    Private Function EverythingFilledIn() As Boolean
    
        Dim AnythingMissing As Boolean
        EverythingFilledIn = True
        
        Dim ctl As MSForms.Control
        
        For Each ctl In Controls
    
        N = ctrl.Name
    
         If Me.Controls(N).Visible = True Then
            If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
                If ctl.Value = "" Then
                  ctl.BackColor = rgbPink
    
                  If Not AnythingMissing Then ctl.SetFocus
                  AnythingMissing = True
                  
                  EverythingFilledIn = False
                End If
            End If
            End If
        Next ctl
        
     
    End Function
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ 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] Cleaner code for looping through userform controls (beginner)
    By melpa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2019, 08:07 AM
  2. [SOLVED] Find Userform controls based on Control Name
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2017, 02:33 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. Looping through controls on a worksheet to set control icons
    By insomniac53 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2015, 11:04 AM
  5. Replies: 5
    Last Post: 02-27-2013, 12:04 PM
  6. [SOLVED] Looping through controls on a MultiPage control
    By Paul Martin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2005, 01:06 AM
  7. 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