Thread: Populating textbox from combobox options, then clearing form

Error Help.xls

I'm extremely new to this and I'd love a little help. Seems like it might be easy for you lifesavers out there... An example is attached.

If you open the attachment, you can see that I'd like to choose a date from the combobox and have the corresponding "date code" (that's what I'm calling it - it's for a different, more complex purpose) appear in the text box. For this to happen, I'm using the following code:

Private Sub ComboBox1_Change()

TextBox1.Value = Range("B" & ComboBox1.ListIndex + 1)

End Sub

This alone works fine. Then I decided I would like to add a button that logs the "date code" from TextBox1 in a cell. I also want that button to clear both ComboBox1 and TextBox1 once it's clicked. This is the code I use for that:

Private Sub CommandButton1_Click()

    Sheets("Sheet1").Activate
    ActiveSheet.Range("J11").Select
    
    Do

    If IsEmpty(ActiveCell) = False Then

        ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = TextBox1.Value

    ComboBox1.Value = ""
    TextBox1.Value = ""

End Sub

This is where I encounter a problem with the FIRST code. It returns "Run-time error '1004': Method 'Range' of object '_Worksheet' failed". Makes me sad. Any thoughts?

(The attached example is just a very condensed version of the file I'm working on. I realize logging information on the same sheet you got it from is silly - the real file isn't quite like this. )

Hello karen000,

The problem is 2-fold. You are using the default event, ComboBox_Change(), for the ComboBox. So, anytime the Combox's contents change or a selection is made from the drop down list, this event will fire.

Whenever this event fires, the ComboBox will update the ListIndex pointer. Since the ListIndex is a zero based offset (first element is zero, second is 1, etc.), negative one is used to indicate that no selection has been made in the drop down list..

However, Range and Cell addresses are 1 based. Your code errors because when nothing is selected in the ComboBox, you are trying to address $B$0. This is not a valid Range address and Excel generates an error.

Change you ComboBox1_Change() event code to what is below and it will work.

Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex > - 1 Then
     TextBox1.Value = Range("B" & ComboBox1.ListIndex + 1)
  Else
     TextBox1.Text = ""
  End If
End Sub