Refer to combobox in userform with variable

[lif]

Hi,

I'm new to the forum and also to excel macros - so what you'll see
below is likely an ugly hack.

My question is (I hope) simple - I have a userform with many
comboboxes.
each combobox is called similarly like pre1, pre2, pre3...

I would like to be able to populate each combobox with the same
information refering to each combobox with a variable in a for loop.

I get a "type mismatch" error - I believe the issue is related to the
fact that a combobox is an object and I am trying to use a string and
integer variables to name an object - I just don't know how to go about
fixing it?

Hopefully this is clear enough?

Private Sub UserForm_Initialize()

Dim i As Integer
Dim totalwaferspre As Integer
Dim averagepre As Single
Dim fieldnamepre As Object
Dim combobox As String
Dim g As Integer

totalwaferspre = Worksheets("Pre_Rawdata").Range("g2").Value
averagepre = Worksheets("Pre_Rawdata").Range("n2").Value

For g = 1 To totalwaferspre

combobox = "pre" & g
Set fieldnamepre = combobox
fieldnamepre.Clear

For i = 2 To 2 + totalwaferspre
fieldnamepre.AddItem Worksheets("Pre_Rawdata").Range("H" &
i).Value

Next
Next

End Sub


Many Thanks,
Luis

 

[MaC]

It might be more useful to replace your looping thru comboboxes with
for..each syntax
e.g.

Dim cbCtl As Control
For Each cbCtl In UserForm1.Controls
For i = 2 To 2 + totalwaferspre
fieldnamepre.AddItem Worksheets("Pre_Rawdata").Range("H" & i).Value
Next
Next

MaC

 

[MaC]

Naturally at first you should check type of control, then my sample code
looks like this:

Dim cbCtl As Control
For Each cbCtl In UserForm1.Controls
If Typename(cbCtl)="Combobox" then
For i = 2 To 2 + totalwaferspre
fieldnamepre.AddItem Worksheets("Pre_Rawdata").Range("H" & i).Value
Next
End if
Next

HTH

MaC

 

[lif]

Thanks MAC,

That makes it so much simpler - you rule. It works like a charm for m
original request but am wondering how i can extend it.

Another quick question if you have a second - I have some comboboxe
named pre* and some named post* - your code works wonderfully but al
comboboxes Pre and post end up wth same data. Is there a way t
seperate it out so that the "pre" have a certain list and the "post
have another list?

something like this - where I'm trying to select only the pre
listboxes :
of course this doesn't work but shows what I'm attempting.

Dim cbCtl As Control
For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" And cbCtl.Name = "pre*" Then
For i = 2 To 2 + totalwaferspre
cbCtl.AddItem Worksheets("Pre_Rawdata").Range("H" & i).Value
Next
End If
Next

also,
I want listbox pre1 to start at h1 and end at h10,
pre2 to start at h2 and end at h1 (h2,h3,h4,h5,h6,h7,h8...h10,h1)
pre3 to start at h3 and end at h2 (h3......h10,h1,h2)
etc.

Is this workable?

I appreciate your help.
Thanks again,
Lui

 

[MaC]

I'm not exactly sure what is a meaning of h1...h10..? Does it refer to any
range of worksheet or variables?

MaC

 

[lif]

Mac,

Sorry about that -

You're correct, I have a list of items located in column "H" (H1,
H2...)

Ideally I'd like each of the comboboxes to start with a different
option - so combobox Pre1 starts with H1 by default, pre2 with H2 and
so on.

I also need to differentiate between comboboxes named pre* and those
named post*, since they will use different ranges.

Thanks again,
Luis

 

[MaC]

Mac,

Sorry about that -

You're correct, I have a list of items located in column "H" (H1,
H2...)

Ideally I'd like each of the comboboxes to start with a different
option - so combobox Pre1 starts with H1 by default, pre2 with H2 and
so on.

I also need to differentiate between comboboxes named pre* and those
named post*, since they will use different ranges.

Thanks again,
Luis

If we assume, that you have comboboxes Pre1...Pre9 and Post1...Post9 and
list for Pre is column H, and list for Post is column I
then try something like this:


For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" Then
For i = 1 To 2 + Totalwaferspre
If Left(cbCtl.Name, 3) = "Pre" Then cbCtl.AddItem
Worksheets("Pre_Rawdata").Range("H" & i + Right(cbCtl.Name, 1)).Value
If Left(cbCtl.Name, 3) = "Pos" Then cbCtl.AddItem
Worksheets("Pre_Rawdata").Range("I" & i + Right(cbCtl.Name, 1)).Value
Next
End If
Next


If each listbox has to have all values from list, e.g. if it starts from h2
it must end with h1, then in column H and I, after row with value h10 add to
list again value h1, etc.

HTH

MaC

 

[lif]

Hi MaC,

I (you) got it working! It took some additional working out though -

Your initial code produced "object doesn't support this property or
method."

here's what I have now that is working:

Dim cbCtl As Control
For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pre"
Then
For i = 1 To 2 + totalwaferspre
cbCtl.AddItem Worksheets("Pre_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
ElseIf TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pos"
Then
For i = 1 To 2 + totalwaferspre
cbCtl.AddItem Worksheets("Post_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
End If
Next


I also found that the .tag property for listbox / combobox allows you
to group the boxes in the userform. If you set the tag then you can
use something like:

Dim cbCtl As Control
For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" And cbCtl.Tag = "Pre" Then
For i = 1 To 2+ totalwaferspre
cbCtl.AddItem Worksheets("Pre_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
ElseIf TypeName(cbCtl) = "ListBox" And cbCtl.Tag = "Post" Then
For i = 1 To 2+ totalwaferspre
cbCtl.AddItem Worksheets("Post_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
End If
Next

But I think your method works best since it reduces the amount of prep
work needed.

i appreciate all your help - this has been very educational.

I guess when you know very little - everything is educational.

Thanks again - now my silly little project works!

Luis

 

[MaC]

Hi MaC,

I (you) got it working! It took some additional working out though -

Your initial code produced "object doesn't support this property or
method."

here's what I have now that is working:

Dim cbCtl As Control
For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pre"
Then
For i = 1 To 2 + totalwaferspre
cbCtl.AddItem Worksheets("Pre_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
ElseIf TypeName(cbCtl) = "ListBox" And Left(cbCtl.Name, 3) = "Pos"
Then
For i = 1 To 2 + totalwaferspre
cbCtl.AddItem Worksheets("Post_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
End If
Next


I also found that the .tag property for listbox / combobox allows you
to group the boxes in the userform. If you set the tag then you can
use something like:

Dim cbCtl As Control
For Each cbCtl In F5DataSummary.Controls
If TypeName(cbCtl) = "ListBox" And cbCtl.Tag = "Pre" Then
For i = 1 To 2+ totalwaferspre
cbCtl.AddItem Worksheets("Pre_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
ElseIf TypeName(cbCtl) = "ListBox" And cbCtl.Tag = "Post" Then
For i = 1 To 2+ totalwaferspre
cbCtl.AddItem Worksheets("Post_Rawdata").Range("h" & i +
Right(cbCtl.Name, 1)).Value
Next
End If
Next

But I think your method works best since it reduces the amount of prep
work needed.

i appreciate all your help - this has been very educational.

I guess when you know very little - everything is educational.

Thanks again - now my silly little project works!

Luis

That's right. I've forgotten the TAG property. In fact, your example is as
well very educational for me.
It was nice to be helpful.

BTW. Where have you seen "object doesn't support this property or method."
message (which line of my initial code)? It has been working without errors
in my sample sheet, I've build to resolve your query. What Excel version do
you use?


Good luck
Mario