Active Sheet from Combo Box Selection (User Form)

Hello all, and thank you for this wonderful resource.

I have attached a sample document. I would like to be able to select a sheet name from the combo box on Sheet1 and automatically be transferred to the sheet I selected. I have found a VBA script that works if I am using the ActiveX combo box; however, I am having trouble creating a dynamic range using the ActiveX option (the workbook needs to be closed and re-opened for the combo box list to update).

Any thoughts?

Thank you in advance!

Originally Posted by Norie

How/when are you populating the combobox?

The list in the combo box is populated using a named range. The range is determined dynamically. I am using the following formula to determine the range:

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

The combo box selection is made by the user. The combo box is essentially serving as a navigation feature. I will include the combo box on each sheet in the workbook.

Originally Posted by Sean Thomas

this works with an active x combo box

Thank you for this. I would really like to be able to use a user form, as opposed to ActiveX, if possible.

Any thoughts?

Originally Posted by Norie

Or if you are using a userform.

Please Login or Register  to view this content.

Thank you for taking the time to reply to my question, as well as providing a solution for both the ActiveX and User Form scenarios.

I tried pasting the User Form code into the Sample.xlsm workbook to no avail, likely due to my lack of experience with VBA scripts.

Would it be too much to ask for you to add the code to the Sample workbook that I uploaded? All of your help is greatly appreciated!

Kindest regards,
Justin

Originally Posted by Sean Thomas

just run the macro showuserform
then select which sheet and it will take you to that sheet

Thank you for your reply! I appreciate you providing a sample workbook.

I tried your solution, which did work. I ran the macro, a popup window appeared, I selected the sheet I wanted to view and I was taken to the sheet.

Is there any way to modify your code so that I can select the sheet I would like to view form the combo box (user form), instead of the popup window solution?

Thank you again for all of your kind support.

Best regards,
Justin

Originally Posted by Sean Thomas

That was a userform that opened, after you select a sheet, it activates the sheet then closes the userform.
what exactly are you trying to achieve?

I apologize for not being clear.

I want the user of the worksheet to be able to click the drop down arrow of the combo box (user form), click on a sheet name (which is populated using the dynamic range formula) and for the respective sheet name to be activated. I.E.: A user clicks the arrow, selects "Sheet2" and is taken to "Sheet2".

Originally Posted by Sean Thomas

That's more complicated than my version. Why do you want a dynamic range formula for the rowscource? surely selecting the sheet in a list box is doing the same as a combobox?
any reason why you want to make it more complicated?

I interpret your response as two questions:

1.) Why do you want the range of sheet names to be dynamic?
2.) Why do you want a combo box as opposed to pop-up window?

Responses:

1.) I am going to make heavy use of indirect references to sheet names so that I can easily "exchange" sheets as I further develop the sheets. If a new sheet has a new name, I want to be able to change a "directory" of sheet names. By using a dynamic column, or range, of sheet names, I am hoping to make this process as efficient as possible.
2.) Simply clicking a drop-down box of sheet names seems much easier (and more familiar, as it mimics a choosing a recently visited web address in a web browser), than having a pop-up window pop-up and then selecting a sheet and double clicking it (or pressing "Ok").

I don't mean to be burdensome, I am simply searching for the most elegant approach because I want those using the Workbook to make the most of it.

Thank you again, I truly appreciate all of your time!

Kindest regards,
Justin

There is no way I could have stumped you with this! Your help is greatly appreciated.

Originally Posted by Sean Thomas

Here you go hopefully this is what you want.
When you activate the worksheet it will load the combobox with anything in A2:A lastrow.

Sean,

Thank you for taking the time to reply again! I appreciate you uploading the sample workbook as well.

Did you add the code to the combo box or the ActiveX box?

When attempting to make a selection on the combo box, I received the following error: "Cannot run the macro “From Web.xlsm’!DropDown1_Change’. The macro may not be available in this workbook or all macros may be disabled."

The ActiveX box is not populated.

Thank you again for taking the time to respond, I really do appreciate your time.

Thank you for taking the time to reply as well!

I have added the code you provided to the worksheet. When attempting to make a selection, I receive the following error: "Cannot run the macro “From Web.xlsm’!DropDown1_Change’. The macro may not be available in this workbook or all macros may be disabled."

Any thoughts?

Originally Posted by Norie

Or if you are using a userform.

Please Login or Register  to view this content.