Combo box help??

[NPike]

I am wanting to design a "Front End" for a project I am currently working on. This "Front End" is to direct the user to particular tabs, files etc (probably using hyperlinks).

I would like to design this page to be similar to using Microsoft Windows, for example, you would click on "Start", a box pops up, then you can click on "Programmes", another box to the side pops up, then click on "Accessories", the "Paint" and it takes you to the programme.

Any ideas as to how to do this?

Can this be done using the Combo box feature? - I have not used one before so if so some basic help would be very much appreciated.

Kind Regards,

Neil Pike.

 

[Jon von der Heyden]

Hello Neil

One way that should be relatively straight forward is to build a toolbar and atttach it to the workbook.

1. Go View > Toolbars > Customize
2. On the Toolbars tab click 'New' and give your toolbar a name.
3. Go to the commands tab and drag and drop a 'New Menu' from the 'New Menu' category.
4. Right click the new menu and change name to 'Start'.
5. Drag and drop another new menu within the one created in step 3. Right click it and change name to 'Programmes'.
6. Repeat step 5 until your heirarchy is complete.
7. In your lowest tier, drag and drop macro buttons from the 'Macros' category. You can right click these and assign hyperlinks and macros.

Hope this helps.

 

[NPike]

Thanks Jon.

That works really well.

Is there anyway we can keep the pull down menu in the worksheet (e.g. A1) rather than in the menu toolbars above?

Cheers,

Neil Pike.

 

[Jon von der Heyden]

Neil, the nice thing about toolbars is that you can drag and drop it to just about anywhere you want. Although if you only want the toolbar to be active when a the user is on a particular sheet, then you need to code it to hide the toolbar when they navigate away from the sheet. The following should be added in the VBE against the desired sheet:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br>    Application.CommandBars("Custom 1").Visible = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()<br>    Application.CommandBars("Custom 1").Visible = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


and this should be assigned to ThisWorkbook:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    Application.CommandBars("Custom 1").Visible = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Change the toolbar name (currently 'Custom 1') to whatever it is that you called yours.

 

[NPike]

Jon,

I see what you mean, but if I use a toolbar, will other people be able to access the toolbar on a different computer (i.e. another version of Excel)?

The toolbar works well but I need this to be accessible for other people to use on different computers - so I could do with the toolbar in the spreadsheet, so when opened from another computer, the individual can still navagate to their desired page.

Thanks for all your help so far!

Neil.

 

[Jon von der Heyden]

Neil

What versions of Excel might your user community be using?

 

[NPike]

Hello,

I am using Excel version 2002.

The reason why I am after the toolbar within the spreadsheet is that I want to create a "pretty" Front Page, that everyone can use (much like a web page) from any computer.

Everyone else who will be using this will be on Excel version 2002 as well.

Many thanks,

Neil.

 

[Jon von der Heyden]

Fine with xl2002. Make sure you attach the toolbar to your workbook.

View > Toolbars > Customize
On the Toolbars tab click 'Attach'
Copy your toolbar across to 'Toolbars in ths workbook'.

 

[NPike]

Thanks Jon, you've been a huge help!