Using ComboBox Tag property to simply code

[markgrnh]

Good morning.

I trust someone who is more VBA savvy with me can answer my query.

I have a Userform that contains multiple comboboxes, about 25 at the moment that I use to select different options to filter a table and export the results.

At the moment, I have written the code so it checks each combobox to see if the value is blank before running the actual filtering and export, and then gives a warning message, stopping the code until all boxes have a value. Now that is a lot of code, as each combobox has its own line of code, checking for blanks. Eg If CMBox1.value = "" then msgbox "You havent selected all options" exit sub end if blah blah etc... thats not actual code BTW

I have been thinking of ways to improve the code, so my question is, it is possible I can group all the comboboxes using the tag property, so that when it looks for blanks, it looks at them as a bulk. For example, If the item is a combobox and has a tag of "testing" then if any combobox value is "" then msgbox "You havent selected all options" exit sub etc.

Something along those lines, yet I havent done anything with grouping comboboxes as a group or how to actual do the code, so hoping someone on here can help?

Many thanks

Mark

 

[RoryA]

You don't need to group them, just loop through the controls and test the type:

   Dim ctl As MSForms.Control
   For Each ctl In Me.Controls
      If TypeName(ctl) = "ComboBox" And ctl.Tag = "testing" Then
         ' do your thing
         Exit Sub
      End If
   Next

for example.

 

[markgrnh]

You don't need to group them, just loop through the controls and test the type:

   Dim ctl As MSForms.Control
   For Each ctl In Me.Controls
      If TypeName(ctl) = "ComboBox" And ctl.Tag = "testing" Then
         ' do your thing
         Exit Sub
      End If
   Next

for example.

That was quick thanks Rory. Also sorry, might be being dense, but I want it to bring up a msgbox if any of the values are blank, how to add that your code above, would it be crl.value = "" ? so in full;

If TypeName(ctl) = "ComboBox" And ctl.Tag = "testing" And ctl.value = "" Then

Msgbox "Not all values are selected, please check and try again"

Thanks

 

[RoryA]

Put that part into the ' do your thing section - e.g.:

   Dim ctl As MSForms.Control
   For Each ctl In Me.Controls
      If TypeName(ctl) = "ComboBox" And ctl.Tag = "testing" Then
         If ctl.Value = "" then
             msgbox "Not all options selected"
            ctl.setfocus
            Exit Sub
         end if
      End If
   Next

 

[markgrnh]

Amazing, works perfectly thanks Rory. Appreciate your help

 

[RoryA]

Glad we could help.