adding items to combobox using "Tag" control to limit

[bob cochran]

The following was submitted to me from another post. (Thanks Dick) The
only changes I made were changing "UserForm_Initialize" to
"frmMaintest_Initialize".

The items are not showing in the combobox(s). Do I still need to
include a reference in the Row Source of the ComboBox(s)? I am using XP
and think this could be to blame. Can someone take a look and see if
something is missing?

I want to use the same list, but limit which combobox(s) show which
items.

Thanks in advance!
Bob C.

Sheet1.Range("a1:a3") contains

Card1
Card2
Card3

Userform1 has three comboboxes with Tag properties of

Card1;Card2
Card2;Card3
All

respectively. In the initialize event of the form, I populate the
comboboxes based on their Tags

Private Sub UserForm_Initialize()

Dim CardRng As Range
Dim Cell As Range
Dim Ctl As Control
Dim AllowedCards As Variant
Dim i As Long

Set CardRng = Sheet1.Range("a1:A3")

For Each Ctl In Me.Controls
If TypeName(Ctl) = "ComboBox" Then
If Len(Ctl.Tag) > 0 Then
If Ctl.Tag = "All" Then
For Each Cell In CardRng.Cells
Ctl.AddItem Cell.Value
Next Cell
Else
AllowedCards = Split(Ctl.Tag, ";")
For Each Cell In CardRng.Cells
For i = LBound(AllowedCards) To _
UBound(AllowedCards)

If Cell.Value = AllowedCards(i) Then
Ctl.AddItem Cell.Value
End If
Next i
Next Cell
End If
End If
End If
Next Ctl

End Sub

 

[Dick Kusleika]

You're welcome, Bob.

Don't change the name of the sub. It has to be UserForm_Initialize
regardless of what you name your userform. You can use the dropdown boxes
at the top of the code pane for the userform's class module to make sure you
have it right. Select Userform from the left and Initialize from the right.

 

[bob cochran]

In that case, could you help me with an error? The reason I changed it
was because I was getting the error - Run-time error "91": Object
variable or With block variable not set.

When I go to Debug, it takes me to the code that loads the userform.

Sub showforms()
frmMaintest.Show 'this line is highlighted.
End Sub

I need to get all the VB Help files loaded. Do they explain in detail
what the error messages mean?

Thanks for your help and patience. I've only been working with VBA for
a few weeks now.

 

[Dick Kusleika]

Bob

First, go to Tools - Options - General (in the VBE) and make sure that it's
set to break on errors in class modules. If you have an error in your
Initialize event and don't have this type of error handling set, all errors
that if finds in the userform's class module will show up as an error on
that line.

Post back if that doesn't work. If it doesn't, you have bigger problems and
I will tell you how screwed you are. Hopefully, that will highlight the
offending code in the Initialize event. If it does, post the code that's
highlighted.

 

[steve]

Bob,

Make sure that the name of the form is "frmMaintest". Check the properties
window for "(name)".

Works fine for me in Excel97.

 

[bob cochran]

That did it! Not certain I understand it fully yet, but I'll get there
someday. Thanks so much for your help and patience. Now back to the
grind.

Bob