Refer to combobox in userform with variable

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

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


Użytkownik "lif" <[email protected]> napisał
w wiadomości news:[email protected]...
>
> 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
>
>
> --
> lif
> ------------------------------------------------------------------------
> lif's Profile:
> http://www.excelforum.com/member.php...o&userid=35745
> View this thread: http://www.excelforum.com/showthread...hreadid=555267
>

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

Użytkownik "MaC" <[email protected]> napisał w wiadomości
news:[email protected]...
> 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
>
>
> Użytkownik "lif" <[email protected]>
> napisał w wiadomości
> news:[email protected]...
>>
>> 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
>>
>>
>> --
>> lif
>> ------------------------------------------------------------------------
>> lif's Profile:
>> http://www.excelforum.com/member.php...o&userid=35745
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=555267
>>
>
>

Thanks MAC,

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

Another quick question if you have a second - I have some comboboxes named pre* and some named post* - your code works wonderfully but all comboboxes Pre and post end up wth same data. Is there a way to 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,
Luis

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

MaC

Użytkownik "lif" <[email protected]> napisał
w wiadomości news:[email protected]...
>
> Thanks MAC,
>
> That makes it so much simpler - you rule. It works like a charm for my
> original request but am wondering how i can extend it.
>
> Another quick question if you have a second - I have some comboboxes
> named pre* and some named post* - your code works wonderfully but all
> comboboxes Pre and post end up wth same data. Is there a way to
> 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,
> Luis
>
>
> --
> lif
> ------------------------------------------------------------------------
> lif's Profile:
> http://www.excelforum.com/member.php...o&userid=35745
> View this thread: http://www.excelforum.com/showthread...hreadid=555267
>

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

Użytkownik "lif" <[email protected]> napisał
w wiadomości news:[email protected]...
>
> 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
>
>
> --
> lif
> ------------------------------------------------------------------------
> lif's Profile:
> http://www.excelforum.com/member.php...o&userid=35745
> View this thread: http://www.excelforum.com/showthread...hreadid=555267

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

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

Użytkownik "lif" <[email protected]> napisał
w wiadomości news:[email protected]...
>
> 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
>
>
> --
> lif
> ------------------------------------------------------------------------
> lif's Profile:
> http://www.excelforum.com/member.php...o&userid=35745
> View this thread: http://www.excelforum.com/showthread...hreadid=555267
>

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