Set active Worksheet from a ComboBox value.

[MaxSheets]

Hi this is my first post, I've not coded for some time until recently so my vba Knowledge may be a little rusty and not upto date with the newest versions of Excel.

I have a userform with dependant comboboxes, textbox, date etc that works great, but the sheet names are hardcoded. (see below code)

Please Login or Register  to view this content.

I wish to change the hard coding ("Fruit Exp") that sets the active sheet to the same name as selected in the cmbType .

Should I try case function?


I hope I've explained the problem clearly enough.

Thank you in advance for any help you can offer.

Cheers

MaxSheets

Update.

I've Changed this code line and similar, {ThisWorkbook.Worksheets("Data").Range("A1")}
this eliminates one of the hard coded name values.

Please Login or Register  to view this content.

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[MaxSheets]

Thank You AlphaFrog.

It was my assumption you needed a string for a sheet name.
your answer has allowed me to point the cmbType value to the sheet name.

See Below

Please Login or Register  to view this content.

Many thanks

Ps Im not sure how to label this thread solved?

[AlphaFrog]

You're welcome.

cmbType.Value is a string when an item is selected.

This tests if any item from the combobox's list is selected.
If cmbType.ListIndex > -1 Then

I don't follow the need for your If-ElseIf statements, but if it works for you then go for it.