Drop down menus in excel

mooman_00

Is there anyway of inserting drop down menus in excel in ms office 2000

Bard

Can you be more specific?

What is the purpose of these drop-down menus?

RicardoSmith

I assume you mean add your own functionality in new menus. But you'll have to use VBA (Visual Basic for Applications) to do it. If you've done any VB its handy enough. Its a little bit more involved than recording a macro and sticking a button on it though. However like Bard said you have to give us more detail on what you want to do. It would probably be more at home in the programming forum though.

mooman_00

im working in a company at the mo that has quite a lot of different projects on the go at one time.Im creating a new timesheet format and ive been asked to add a drop down menu in the time sheet containing all the current project codes so that my co-workers only need to pick the proj no. from the menu rather than write it in......I was wondering is this possible, the proj codes will be listed on a seperate worksheet.....and im only a student, this being the reason why ive been dumped with this task...

RicardoSmith

what you need to look at it VBA for access. For example in excel go into "tools\macro\record new macro" start recording. Do something in excel like enter the codes you are talking about. Then stop recording. (hit enter first etc.) Then go to "tools\macro\visual basic editor". This will launch the VBA editor. If you look at the tree on the left expand the modules folder and click in the module1 icon. This will then show you the code you have just recorded. leave the VBA editor open and flick back to excel. Delete what you have just done. Then flick back to the VBA editor and making sure you have click in the code window somewhere go to "run\run sub\userform" This will rerun the code for you and if you flick back to excel you will see it has repeated you actions again. Cool eh?

Anyway you can attach your macro to a button and put it on the tool bar somewhere. Or you can code a new menu with a new drop down list. The latter is a little bit more involved but its not rocket science either. The main power in this is that you don't have to record a macro to do stuff either. Using the VBA editor you can write you own code and functionality, like reading data off one work sheet and entering it on another. Though you'll need to learn a bit of VBA to do it though.


On http://vbcity.com/forums theres a section for VBA (Visual Basic for applications) read some of the that and you might get an idea of what needs to be done. Otherwise just post back here (or email myself) and someone will give you a dig out : )

BTW fair dues for posting the question. Least you knew where to look for info!

mooman_00

cheers Ricardo ill give that a go after lunch, no point on starting on an empty stomach:D . That doesnt sound too bad, if i have any troubles ill get back to you, thanks...

Earthhorse

Methinks there is an easier way to do this than what Ricardo suggesteth.

1. First select the cell where you want the dropdown list to appear.
   
2. Click on data.
   
3. Click on validation.
   
4. In allow select list from the dropdown box.
   
5. Click on the the little coloured button in the Source box that appears.
   
6. You should then select the cells on the other sheet where the project numbers appear, hit return and you should have your dropdown box; no fuss, no muss.

You can always enter the values manually in the source box but it's quicker selec the cells unless they're over the place.

Hope this helps.

RicardoSmith

Earthhorse- Ummm tried this and it can't seem to select the another worksheet. I also can't move the dropdown list thats created anywhere else. Is there some other step I'm missing? Its probably something simple. I'm just more used to programming it than using the interface LOL.

Earthhorse

Ya know, I never even tested whether selecting another sheet would work or not, I just assumed it would.

It doesn't. You would have to move the project codes into the same sheet if you wanted to do it my way.

You can copy and paste the dropdown box that's created, though I suppose this wouldn't be an ideal solution.

Ultimately I think the way you've suggested would be better but this might be a stop gap measure while mooman_00 teaches himself VBA!