Vba combobox using non-active sheet

Question

I have a combobox to a userform and the its not an activesheet in excel so not sure how to go about it.

The sheet name is "DoNotPrint - Rate Index" and the values within that sheet to appear in the combobox are the columns C2:AS2.

Private Sub ComboBox1_Change()

   Sheets("DoNotPrint - Rate Index").Range("C2:AS2") = ComboBox1.Value

End Sub

I tried this code and the combobox list isn't populating those column when the combobox list button is clicked.

combobox

Not sure I understand what "the combobox list isn't popping up when the combobox list button is clicked" means. Would a screenshot help? — Mathieu Guindon
What do you mean by "trying to implement a combobox to the userform"? Is the ComboBox on the UserForm? What is it supposed to do when you change it? — Comintern
@Comintern the combobox is on the userform, yes. Just want the user to pick one of the combobox option then that information is put into one of the cells in the excel file — GoldFusion
Try setting the RowSource property of the combo box to 'DoNotPrint - Rate Index'!C2:AS2 in the userform's init event instead of whatever it is you're trying to do in the control's change event. — StoneGiant

JosephC JosephC · Accepted Answer · 2018-11-09T19:19:35

The code you posted is for taking what's in the combobox and putting it on the sheet, when it's selected. But if I understand correctly your issue is you can't get the combobox populated. Because you are using a single row as your dataset and not a single column you will need to transpose your data.

Private Sub UserForm_Initialize()
    ComboBox1.List = WorksheetFunction.Transpose(Sheet1.Range("C2:AS2"))
End Sub

Vba combobox using non-active sheet

1 Answers