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" <lif.29x8jz_1151177403.4221@excelforum-nospam.com> napisał
w wiadomości news:lif.29x8jz_1151177403.4221@excelforum-nospam.com...
>
> 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" <oiram76@poczta.onetDOTpl> napisał w wiadomości
news:449d9bec@news.home.net.pl...
> 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" <lif.29x8jz_1151177403.4221@excelforum-nospam.com>
> napisał w wiadomości
> news:lif.29x8jz_1151177403.4221@excelforum-nospam.com...
>>
>> 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" <lif.29xd6n_1151183404.2201@excelforum-nospam.com> napisał
w wiadomości news:lif.29xd6n_1151183404.2201@excelforum-nospam.com...
>
> 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