How to give an array value to different comboboxes in vba excel?

[dpa]

Hi, I have 4 comboboxes in a formcontrol (excel 2007, vba), each combobox has the name: Combobox_01 Combobox_02 Combobox_03 Combobox_04 I want to give all comboboxes the same array values "A","B","C","D","E","F" How do I do this efficiently?, I tried a For-NEXT loop, but I am not sure how to assign the For value from 1 to 4 to each combobox and it gives me an error...

 

[p45cal]

although it doesn't need to be in the click event:

Private Sub UserForm_Click()
ary = Array("A", "B", "C", "D", "E", "F")
For i = 1 To 4
  Controls("Combobox_" & Format(i, "00")).List = ary
Next i
End Sub

 

[dpa]

although it doesn't need to be in the click event:

Private Sub UserForm_Click()
ary = Array("A", "B", "C", "D", "E", "F")
For i = 1 To 4
  Controls("Combobox_" & Format(i, "00")).List = ary
Next i
End Sub

Thank very much, easy and fast, you are good!

 

[dpa]

I have another question related to the first one. After I have the 4 different controls with their respective letters assigned, I need to copy with if each one of the array values are selected, for instance, my code (quite unprofessionally done) is:

If Combobox_01.Value = "A" Then
Worksheets("sheet1").range("A236").Value = "A"

Worksheets("sheet1").range("D236:Q236").Value = Worksheets("sheet1").range("D11:Q11").Value

If Combobox_01.Value = "B" Then
Worksheets("sheet1").range("A236").Value = "B"

Worksheets("sheet1").range("D236:Q236").Value = Worksheets("sheet1").range("D12:Q12").Value

'an so on for each letter (range values to be copied are numbers)


If Combobox_02.Value = "A" Then
Worksheets("sheet1").range("A237").Value = "A"

Worksheets("sheet1").range("D237:Q237").Value = Worksheets("sheet1").range("D11:Q11").Value

If Combobox_02.Value = "B" Then
Worksheets("sheet1").range("A237").Value = "B"

Worksheets("sheet1").range("D237:Q237").Value = Worksheets("sheet1").range("D12:Q12").Value

'and so on for all letters (range values to be copied are numbers)

'and so on for all comboboxes


Any suggestions on how to optimize the code in order to avoid to make 4 times the same copy procedure? I can use similar procedure as the previous answer for the post, any additional suggestions?

 

[p45cal]

I'm not sure of the progression, but it looks something like this:
Combobox_01, 236 (=235+1)
Combobox_02, 237 (=235+2)
Combobox_03, 238 (=235+3)
Combobox_04, 239 (=235+4)

And for the letters:
A, D11:Q11 (=D11:Q11 offset 0 rows)
B, D12:Q12 (=D11:Q11 offset 1 row)
C, D13:Q13 (=D11:Q11 offset 2 rows)
D, D14:Q14 (=D11:Q11 offset 3 rows)
E, D15:Q15 (=D11:Q11 offset 4 rows)
F, D16:Q16 (=D11:Q11 offset 5 rows)

Since we have placed A,B,C etc. in order in each combobox, as luck would have it the .listindex of a combobox corresponds to the letter chosen as above shown in red, so something like (untested):

With Worksheets("sheet1")
  For i = 1 To 4
    Set myCtrl = [COLOR=Magenta]UserForm3.[/COLOR]Controls("Combobox_" & Format(i, "00"))
    .Range("A" & 235 + i) = myCtrl.Value
    .Range("D" & 235 + i & ":Q" & 235 + i).Value = .Range("D11:Q11").Offset(myCtrl.ListIndex).Value
  Next i
End With

The UserForm3. part may need to be adjusted to your userform's name, and depending on where you've put the code, may not need to be there at all.

A variant:

With Worksheets("sheet1")
  For i = 1 To 4
    Set myCtrl = UserForm3.Controls("Combobox_" & Format(i, "00"))
    myRow = 235 + i
    .Cells(myRow, 1).Value = myCtrl.Value
    .Cells(myRow, 4).Resize(, 14).Value = .Range("D11:Q11").Offset(myCtrl.ListIndex).Value
  Next i
End With

 

[dpa]

I'm not sure of the progression, but it looks something like this:
Combobox_01, 236 (=235+1)
Combobox_02, 237 (=235+2)
Combobox_03, 238 (=235+3)
Combobox_04, 239 (=235+4)

And for the letters:
A, D11:Q11 (=D11:Q11 offset 0 rows)
B, D12:Q12 (=D11:Q11 offset 1 row)
C, D13:Q13 (=D11:Q11 offset 2 rows)
D, D14:Q14 (=D11:Q11 offset 3 rows)
E, D15:Q15 (=D11:Q11 offset 4 rows)
F, D16:Q16 (=D11:Q11 offset 5 rows)

Since we have placed A,B,C etc. in order in each combobox, as luck would have it the .listindex of a combobox corresponds to the letter chosen as above shown in red, so something like (untested):

With Worksheets("sheet1")
  For i = 1 To 4
    Set myCtrl = [COLOR=Magenta]UserForm3.[/COLOR]Controls("Combobox_" & Format(i, "00"))
    .Range("A" & 235 + i) = myCtrl.Value
    .Range("D" & 235 + i & ":Q" & 235 + i).Value = .Range("D11:Q11").Offset(myCtrl.ListIndex).Value
  Next i
End With

The UserForm3. part may need to be adjusted to your userform's name, and depending on where you've put the code, may not need to be there at all.

A variant:

With Worksheets("sheet1")
  For i = 1 To 4
    Set myCtrl = UserForm3.Controls("Combobox_" & Format(i, "00"))
    myRow = 235 + i
    .Cells(myRow, 1).Value = myCtrl.Value
    .Cells(myRow, 4).Resize(, 14).Value = .Range("D11:Q11").Offset(myCtrl.ListIndex).Value
  Next i
End With

The solution worked very good so far, what I am missing is this when you choose any of the A,B or C values:

How do you to refer to an specific worksheet depending on a selected value:

For example, I have 4 sheets named: "5", "ac", "ad", "6" (instead of "sheet1", "sheet2", "sheet3", "sheet4"), in the vba code, the user may choose from three options: A or B or C, so:

if the user selects A, I want a variable "select" to have a .value of 5
if the user selects B, I want a variable "select" to have a .value of ac
if the user selects C, I want a variable "select" to have a .value of ad

I tried something like this:

If selection = A then select = 5
If selection = B then select = ac
If selection = C then select = ad

worksheet("6").range(A1:A10).value = worksheet("select").range(A1:A10).value

Therefore, depending on the decision of the user, the values copied are from any of the worksheets 5,ac or ad into worksheet 6
but I am not sure hot to refer this value: worksheet("select").range(A1:A10).value
or how do I let the code know that the "select" value is the nam e of the sheet...?

 

[p45cal]

Don't use select as a variable name, there are two other uses of Select; select a range of cells, and the Select Case contruction. Use ShtName or some such.
I don't know where A, B and C are coming from, but say you have assigned the choice to a variable myLetter:

'myLetter = "B"
Select Case myLetter
    Case "A"
        shtName = "5"
    Case "B"
        shtName = "ac"
    Case "C"
        shtName = "ad"
End Select
worksheet(shtName).range(A1:A10).value…

 

[dpa]

Don't use select as a variable name, there are two other uses of Select; select a range of cells, and the Select Case contruction. Use ShtName or some such.
I don't know where A, B and C are coming from, but say you have assigned the choice to a variable myLetter:

'myLetter = "B"
Select Case myLetter
    Case "A"
        shtName = "5"
    Case "B"
        shtName = "ac"
    Case "C"
        shtName = "ad"
End Select
worksheet(shtName).range(A1:A10).value…

Thank you

I tried something like:

If Combobox.Value = "A" Then wrkst = "5"

Worksheets("6").range("A1:A10").Value = Worksheet(wrkst).range("A1:A10").Value

But after I writte the code, it says: Compile error: Expected: List separator or )

It does not seem to accept the code

Worksheets("6").range("A1:A10").Value = Worksheet(wrkst).range("A1:A10").Value
due to the wrkst, I tried to declare wrkst as: Dim wrkst as worksheet or just leave it without declaration and it both ways does not work..
Any idea why?
Any other way to writte this?

 

[p45cal]

needs an additional s:
Worksheets("6").range("A1:A10").Value = Worksheets(wrkst).range("A1:A10").Value

wrkst should be a String

 

[dpa]

needs an additional s:
Worksheets("6").range("A1:A10").Value = Worksheets(wrkst).range("A1:A10").Value

wrkst should be a String

Hi P45cal, thanks a lot for your help and patience, it was very nice from you . I am glad I joined this forum, it is extremely good. Thank you for the tips and support, really helpful. Hope you can give me a hand in the future again. Sorry for bothering so much.