VBA Excel Combo Boxes

Question

I have an Excel VBA UserForm which i cannot get to work. The first combobox needs to link to the relevant sheets (the years). The second combobox or listbox then picks up the data within the first column of the chosen sheet (the months) then the data is entered and appears on the relevant sheet in the relevant row. My coding is basic and my referencing is wrong, can you help?

This works, it puts it in the right sheet, but not in the right row (combobox 2):

Private Sub CommandButton1_Click()
With ThisWorkbook.Worksheets(cboTest.Value)
.Range("D2").Value = nightElec
.Range("F2").Value = dayHeat
.Range("G2").Value = nightHeat
.Range("I2").Value = dayWater
.Range("J2").Value = nightWater
dayElec = Empty
End With

End Sub

I am guessing, you want to select the correct row with the selection from combobox2. But I need to see, what information you have in combobox2 and how it gets there, in order to help you further. Its the same thing, like with the worksheet, excel is no magician, when you don't pass this information in your code, how can it be used by excel?! — Jook
Yes, correct. The info in combobox 2 are the months, from Oct-Sept in that order, is the first column on each sheet and is the same on each sheet. This info is typed in as the sheet forms the basis of a template to be replicated each year. — Sara Arnold
Sara, I am sorry, but really, you need to improve your communication-skills. I am not willing to guess what you want and what you have. Until you provide a solid and understandable "this is my code and this is my problem"-edit, I am not able to help you any further. — Jook

Jook Jook · Accepted Answer · 2012-11-07T15:30:38

How about using this as code of your user-form:

Private Sub UserForm_Initialize()
  Dim wks As Worksheet

  'loop through all worksheets
  For Each wks In ThisWorkbook.Worksheets
    'add their names as items to your combo-box
    cboTest.AddItem wks.Name
  Next wks

End Sub

However, this is just one way out of many. Here I assumed you have not saved your worksheet-names in any other form - like i.e. an array - and need to get this information fresh.

Also this is only the routine for the init of the user-form - not an update.

Private Sub UserForm_Initialize()
  InitCbo
End Sub

Private Sub InitCbo()
  Dim wks As Worksheet

  With cboTest
   'delete all current items of cboTest
   .Clear
   'loop through all worksheets
   For Each wks In ThisWorkbook.Worksheets
     'add their names as items to your combo-box
     .AddItem wks.Name
   Next wks
   'select first item
   .ListIndex = 0 
  End With
End Sub

Now you could use InitCbo to update the combo-box. Like on the click of a button or when a new worksheet is added.

You might be interested in the List property as well, as you can set the Items with it too - like in this excel-help example:

Dim MyArray(6,3)

Private Sub UserForm_Initialize()
    Dim i As Single
     'The 1-st list box contains 3 data columns
    ListBox1.ColumnCount = 3        
    'The 2nd box contains 6 data columns
     ListBox2.ColumnCount = 6        

    'Load integer values into first column of MyArray
    For i = 0 To 5
        MyArray(i, 0) = i
    Next i

    'Load columns 2 and three of MyArray
    MyArray(0, 1) = "Zero"
    MyArray(1, 1) = "One"
    MyArray(2, 1) = "Two"
    MyArray(3, 1) = "Three"
    MyArray(4, 1) = "Four"
    MyArray(5, 1) = "Five"

    MyArray(0, 2) = "Zero"
    MyArray(1, 2) = "Un ou Une"
    MyArray(2, 2) = "Deux"
    MyArray(3, 2) = "Trois"
    MyArray(4, 2) = "Quatre"
    MyArray(5, 2) = "Cinq"

    'Load data into ListBox1 and ListBox2
    ListBox1.List() = MyArray
    ListBox2.Column() = MyArray

End Sub

I know, this is an example for a ListBox - but as far as I know, it applies to ComboBox as well or at least simmilar.

Edit:

The code you pasted will always use the active workbook and the active worksheet, so this Range("C2").Value = dayElec won't change the value of the selceted worksheet of your list/combobox.

You will need to modify it to this:

Private Sub CommandButton1_Click()

  With ThisWorkbook.Worksheets(cboTest.value)
    .Range("D2").Value = nightElec
    .Range("F2").Value = dayHeat
    .Range("G2").Value = nightHeat
    .Range("I2").Value = dayWater
    .Range("J2").Value = nightWater
    dayElec = Empty
  End With

End Sub

VBA Excel Combo Boxes

1 Answers