combo box input to choose worksheet

[Richard H]

Hi everyone

I am working on an excel file with 12 worksheets which are named.
Using a combo box input in a user form with vba I am trying to select the correct sheet for the form's data to be written to.
I have tried various methods and searched the excel forums for examples, all to no avail.
I am new to vba but have managed up to now with examples found on various sites.

I am using Office 2007

Thanks in advance
Richard H

[bhofsetz]

It's not clear from your post exactly what you are wanting your ComboBox to do.

If you are trying to get the ComboBox to be populated with different worksheet names when the userform initializes then I would suggest using the Userform_Initialize()

Put this in the code for your Userform. Change UserForm and ComboBox1 to match those in your workbook.

Please Login or Register  to view this content.

This will populate the combobox. Once the user selects the worksheet from the list you can have the ComboBox selection change event handler take whatever action is desired.

Please Login or Register  to view this content.

If this is not what you were looking for then please explain your needs again.

HTH

[Richard H]

Thanks for that

I have managed to populate the combo boxes ok.

what I want to do is to take all the other data from the form & place it in the sheet relevant to the Combo Box input.

I have managed to populate the current sheet with the data in the correct cells but I want to use the Combo box input to change the worksheet.

I need code to select the correct sheet

Hope that is more understandable

Thanks
Richard H

[bhofsetz]

I guess I'm still not seeing what you are trying to do.

Does all the information on your userform need to go to one worksheet?
Does the information on your userform need to go to whichever worksheet is selected from the combobox?
How does the code know when to place the othe information on the form into the sheet. Is there a button on the userform that the user clicks that trigers sending the information to the appropriate sheet? Do you want all the information sent to the sheet selected in the combobox once the user selects that sheet from the combobox?

If either of the latter two conditions (all information on the userform goes to the sheet selected). Then I would use a with statement and a variable that you can assign to the combobox.value either when the the combobox value changes (as in my example above) or when an appropriate userform button is clicked.

HTH

[Richard H]

Thanks for your help bhofsetz

All the information from the form will be passed to the sheet selected in the combo box.

I have a submit button on the form which sends all the data to the sheet and then clears the form ready for the next set of data which could be passed to a different sheet.

I have been meddling with a variable but without success

Thanks again
Richard H

[bhofsetz]

In your SubmitButton_click event try something like this

Please Login or Register  to view this content.

[Richard H]

Thanks again
I tried the code with my combo box name but it is still putting data onto current worksheet!

Richard H

[bhofsetz]

Richard,

Why don't you post the code for your button and perhaps we can figure out why it is not posting to the correct sheet.