CODEWITHSUNDEEP
excelvba
Julie S.
Julie S.

Reputation: 13

Excel VBA for-loop which checks if multiple ListBoxes have values

I have a Userform (Userform1) which has 4 listboxes (ListBox1 through 4) which all have entries in them. The form-user selects the entry in the list, and then hits a CommandButton (CommandButton_1). The VBA Macro then writes the value from each Listbox into cells on an excel log sheet. The code to populate the listboxes with items and to write the results into the spreadsheet are working.

However, I would like to be able to check to ensure that all the listboxes have a selection. They must always all be filled in prior to writing the data, or else we lose data integrity during later analysis.

I have figured out how to check if an individual listbox has no selection, but I'd like to generalize it to be able to account for any number of listboxes. So far I have tried:

'Attempt 1

     For Each Control In Me.Controls
         If Controls.Name Like "Listbox*" Then
             If Controls.Value <> "" Then
                 Else: MsgBox ("You must select a value for every list.")
                 GoTo ERROR_ENDSUB
             End If
         End If
     Next

A1 gave me error 438: Object doesn't support this property or Method (If Controls.Name line).

'Attempt 2

     Dim sh as Worksheet
     Set sh = ThisWorkbook.Sheets("Downtime")
     For i = 1 To 4
         If "sh.listbox" & i & ".value" <> "" Then
             Else: MsgBox ("You must select a value for every list.")
             GoTo ERROR_ENDSUB
         End If
     Next

A2 just doesn't function, I can leave the lists empty and it still writes data with no Msgbox.

'Attempt 3
    
    Dim LB as ListBox
    For i = 1 To 4
        Set LB = ListBoxes("Listbox" & i)
        If LB.Value <> "" Then
            Else: MsgBox ("You must select a value for every list.")
            GoTo ERROR_ENDSUB
        End If
    Next

A3 gave a compile error: Sub or function not defined (highlighted Listboxes()).

I'm not 100% familiar with the VBA Object model of functions/methods. I'm a chemical engineer who got a reputation for being good at Excel (the formula side of things), and my company has been giving me larger and larger spreadsheet-automation tasks. Eventually they got too big, and now I've been trying for the past week or so to pick up VBA with no prior coding experience.

Upvotes: 1

Views: 64

Answers (3)

FunThomas
FunThomas

Reputation: 29586

Attempt 1 is close. As Black Cat mentioned in the comments, your iteration variable is Control so you need to write If Control.Value <> "" Then.

Attempt 2 has 2 major problems. First is that you are not looping over controls of your userform, you are trying to access something from a worksheet. However, you are even not doing that: What your are really doing is you compare a string "sh.listbox1" with an empty string.

Attempt 3 has thrown the compiler error because the Collection ListBoxes doesn't exist for a user form (it exists for a worksheet).

I would strongly suggest to change the syntax and the logic of your If and Else-statements. Firstly, don't leave the If-branch empty and put only code into the Else-branch. Instead, revert the If-condition:

If Control.Value = "" Then
    MsgBox "You must select a value for every list."
    GoTo ERROR_ENDSUB
End If

If you need an Else-branch, don't use this strange syntax using a colon to add the first statement directly after the Else. It is ugly and makes the code harder to read. Furthermore, indent the code correctly:

If Control.Value <> "" Then
    MsgBox "You did a good job, good boy"
Else
    MsgBox "You must select a value for every list."
    GoTo ERROR_ENDSUB
End If

That said: When you really want to check the names of the controls, attempt 1 (with the mentioned correction) is the way to go. However, if you process with coding, you will learn that it's better to rename controls and if them meaningful names, like lbCountry, lbState, selectManager or whatever. In that case, checking for names can easily fail. You should consider to loop over the controls and check the type of the control. Tim Williams answer shows how to do this (check the TypeName of the control).

Upvotes: 1

Abu Ben Reaz
Abu Ben Reaz

Reputation: 184

You can try with this simple code

Private Sub CommandButton1_Click()
Dim contr As Control

For Each contr In UserForm1.Controls
    If TypeName(contr) = "ListBox" Then
        If contr <> "" Then
    
        Else
            MsgBox ("You must select a value for every list.(" & contr.Name & ")")
            Exit Sub
        End If
    End If
Next
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166755

For example:

Private Sub UserForm_Activate()
    Dim con As Control
    For Each con In Me.Controls
        If TypeName(con) = "ListBox" Then
            Debug.Print "Found listbox: " & con.Name
        End If
    Next con
End Sub

Upvotes: 2

Related Questions