Urgent!! Combo boxes 2 spreadsheet?? Thx

[KrisB_bacon]

Hi

Is it possible in a userform, to have a combo box with data (lets say
different years, 2003,2004 to 2008) and after the user presses a button
on that, for it to takes the user to a worksheet and puts a word in the
appropriate cell (e.g. Yes).

So.........there's a column of years and then, depending on what year
the user selected, the word Yes is entered alongside the year. How do I
get this to work?

Shud I use option buttons/check boxes instead??


Thx for any replies

Chris

 

[Dave Peterson]

A combobox seems ok to me--although if you wanted the user to select multiple
years, you may want to use a listbox.


I made a userform with a combobox on it and one command button.

This is the code I used behind the userform:

Option Explicit
Private Sub CommandButton1_Click()

Dim res As Variant
Dim myYear As Long

If Me.ComboBox1.ListIndex = -1 Then
Exit Sub
End If
myYear = CLng(Me.ComboBox1.Value)
With Worksheets("sheet1")
res = Application.Match(myYear, .Range("a:a"), 0)
If IsError(res) Then
MsgBox myYear & " wasn't found on the worksheet"
Else
.Range("a:a")(res).Offset(0, 1).Value = "yes"
MsgBox "Updated row#: " & res
End If
End With

Me.ComboBox1.ListIndex = -1 'reset it?

End Sub

'some test data??
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 2003 To 2008
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub