Set a combobox as a variable (excel, vba)

[Moreco]

Hi guys,

I am not sure if this is possible, but I wanted to give it a try. I am working with userforms on excel. One of the userform is called "frmTrials" and on it I have a ComboBox1 that the user may choose from 1 to 3. Based on that decision a second userform is openned called "frmComp" where I have three frames. If the user choose 1 then when the frmComp appears, only one frame will be visible and so on. On each frame I have another combobox (where for frame 1 is called cmbLCateg1, for frame 2 is called cmbLCateg2....)

cmbLCateg1, cmbLCateg2 and cmbLCateg3 all have the same information for the user to choose but based on that I will have the textbox underneath the combobox be visible or not.

Here is the question:
Since all 3 combobox have the same information and are supposed to work the same way, can I write a code where my combobox is a variable,
something like:

Private Sub cmbLCategX_Change()

  ''' here I will have my code that is dependent of frmTrials and cmbLCategX (where X can be from 1 to 3 to fufill my three combobox above)

End Sub

and then have another sub that will know if I am changing cmbLCateg1 which will assign X = 1 and then call the above code fro X =1.

Thank you so much for any help.

 

[SkipVought]

Each of your ComboBoxes is a separate object.

However, they can each call the same procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Hi Skip,

So are you saying that I can have my procedure called cmbLCategX_Change() without having a physical combobox called cmbLCategX?

Thanks

 

[SkipVought]

No. I am saying that all three comboboxes can call the same procedure, so you only have ONE.

Check out the Caller method.

Check out the Controls collection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Thank you Skip, but the real problem is when you are doing a Select Case to enter the data on the spreadsheet, for example:

Set r = Worksheets("Trials").Range("B2").CurrentRegion
    ioffset = Sheet3.Range("A54")
    ioffset = ioffset + 1
    Sheet3.Range("A54") = ioffset
    Me.txtTrialsioffset.Value = Sheet3.Range("A54")
    
    Select Case frmComp.cmbLCateg1.ListIndex
  
    Case 1
      If cmbComp.ListIndex = 1 Then
        Set r1 = r.Offset(13, ioffset)
        r1.Cells(1).Value = frmComp.txtVar1.Value

How can I make such that I can have the variable cmbLCategX, so I don't have to write the entire code (that is huge) for cmbLCateg1, cmbLCateg2 and cmbLCateg3?

Thank you so much

 

[PHV]

Select Case frmComp.Controls("cmbLCateg" & X).ListIndex

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886