Using a list box or other control form to activate different macros

Hi,

I am designing (badly!) an excel file that is intended to be as user friendly as possible. I would like to add in a navigation feature using a list box or other appropriate control form that will take the user to the correct tab in the workbook depending on which item they choose from the list.

My aim is too hide all the tabs at the bottom whilst not crowding my page with multiple command buttons, and still be able to navigate easily through the workbook.

Any suggestions? Your help is greatly appreciated.

Thanks & best regards,

Stuart (Melvin)

One way of many.

Create a named range that contains the sheet names to include in the list. Call this range SHEETNAMES.

Add a combobox from the Forms toolbar to the sheet.
Set the Input Name of the control to SHEETNAME
Assign the following macro to that control.

Code in standard code module.

Please Login or Register  to view this content.

You can now copy that control to any other sheet(s)

Thanks Andy!

Excuse my naivity, but I can't find the "Input Name" field anywhere!?? It doesn't appear to be on the Properties or the Format Control field. I have a ListFillRange and ListRows option but that seems to be it. I have Excel 2003 if that helps.

Thanks for any further input you can provide. Your help means a lot.

Stuart.

Hi melvin,

I've attached a sample workbook with Andy's instructions so you can have a look at how it works

Sorry to trouble you again Andy,

I put in "SHEETNAMES" into the ListFillRange field and the combo box seemed to pick them up.. unfortunately when I ran the code there was a runtime error with the following message;

Run-time error '13':

Type mismatch

It seemed to bug at the line, "With ActiveSheet.Shapes(Application.Caller)"

Thanks for your support.

Stuart.

Thanks SOS.

melvin, sounds like you used the ActiveX control rather than the forms.

Sorry Andy,

Hate to be a nuisance but still having problems.. any idea how I can debug the code to give me the result I'm looking for?

Sorry, meant to state that I need more help in the last post.

Regards,

melvin

If you are using a activex control then you can add code to the change event

Please Login or Register  to view this content.

You will need the code in the event of each combo box.

Thanks chaps,

Both methods work a treat! A great help to me.

All the best.

melvin

Actually, while we're on this subject, I'm looking to have a similar function with a Combo Box Form or Active X Combo Box control whereby the user picks from a list of words and depending on which word he/she picks, the active cell is color coded accordingly. I assume the tool would be the same, but not sure how the code would look? Any suggestions?

I realise I may be pushing my luck here!

Once again, tremendous help so far, any further advice would be a bonus.

melvin

Which control type, ActiveX or Forms, did you end up using?

If the choice of colours is limited conditional formatting maybe sufficient.

I ended up trying both, however both have a minor problem I'm trying to work out... when I select say, "sheet 1" from sheet 3's combo box, then I go from sheet 1 back to sheet 3 and try to select "sheet 1" again, "sheet 1" is already showing in the pick list, therefore when I try to select it, the form / Active X control doesn't recognise a change and therefore doesn't switch over to sheet 1. Is there anyway I can default / reset all of them to a 'blank' so that I will always be selecting a different value from the one shown?

Alternatively, can you think of a better control type to use for navigation?

Extremely grateful for the help.

melvin

For dropdown you can use

Please Login or Register  to view this content.