Using a ComboBox in a Userform to create a dropdown list to select specific Worksheets

I need to create a dropdown list (CombobBox) in the user form that can select which worksheet the data will be entered in.

Now, I have a few worksheets and I want to allow the user to select only the worksheets for the months (I have 12 worksheets named after each month).

After they select the month on the drop down box in the user form, they will enter the rest of the data in the form as per normal. What I need is that this data goes to the spreadsheet for that specific month when they click the command button to insert it.

here are codes:-

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
Dim sheet As String
sheet = ComboBox1.Value
Worksheets(ComboBox1.Value).Activate

Range("A3").Value = DTPicker1.Value
Range("B3").Value = TextBox1.Text
If OptionButton1.Value = True Then Range("D3").Value = TextBox2.Value
If OptionButton2.Value = True Then Range("E3").Value = TextBox2.Value

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "apolo"
ComboBox1.AddItem "anil"
ComboBox1.AddItem "abhay"

End Sub

pls reply

I presume you need something like this.

Please Login or Register  to view this content.

Thanks igormigor
I am now able to put data in desired worksheet..

Now I am facing some problem in data validation in combobox.
as I am using worksheets as combobox list items. and with referene to codes given by worksheets gets acitvated.
Now in userform if combobox is empty and I click on command button then a error msg 'subsript out of range' pops up.
though I tried some validation methods to that combobox.

here are code (debugging showing problem in underlined codes )

Please Login or Register  to view this content.

Pls reply

sorry, but I can not understand what do you want to do. can you share sample workbook.

Thanks mikerickson

Its working perfect now.