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

Answer 1

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