Forum Discussion
Macros in Excel dropdown box
Thank you for responding, sorry if I was vague, this is the first time I have asked for assistance.
I am wanting to create a drop down box with the selection of a specific worksheet, and when you activate it, it takes you to that page, the workbook is designed for people who have limited excel understanding. So I am trying to make it easy for them to navigate through it. I have attached the document.
I may be trying to do this the wrong way? If you go to the Examples page, there is a return to results button, this is where I want the option to return to a specific page.
Thank you for trying to help me.
Cathy
10 Replies
Alternatively, you could try using this command in your return to sheets macro to display and use excel's built-in list (clicking on the worksheet in the list will activate it, your code won't need to do anything more).
Application.CommandBars("Workbook Tabs").ShowPopupIf I understand correctly, when the user clicks on the 'go to example page' buttons on the candidate results worksheet, you want the macro attached to the 'back to resulting' button on the 'example answers' worksheet to return to the correct candidate results worksheet?
You could set up a Worksheet_Deactivate event in the Thisworkbook vba module to test worksheets as they are deactivated for names beginning with "candidate results" and store that worksheet into a public variable (note this macro is not attached to a button - it is triggered whenever you switch worksheets as the old worksheet is deactivated and the new one is activated). Then the return to results macro can test that variable and activate the saved worksheet.
I attached a workbook with what I think you're looking for.
- You're quite welcome. Good luck with your project.
Thank you so much for this I will have a look at it today.
Venncathy1 wrote: I am wondering if I can apply macros to a dropdown list. I have tried but not successfully.
I notice that as of this writing you've had 32 views but no responses. I suspect that the reason for the quiet is that so many people reacted as I did: "WHAT??"
Your request is intriguing, but on the surface quite unusual or unexpected. You'd be more likely to get a response, a helpful response that is, if you said a bit more. Answering these questions would help us help you:
- Why are you trying to do this in the first place? A drop down list--the whole idea of it--is to have a user look at it and make a selection, which then gets put in place and becomes part of a bigger process, a bigger picture, a bigger database, a bigger whatever.
- What is it that you're trying to automate? It may be--probably is the case--that there's a more seamless way to do whatever it is that you're trying to automate.
And if you could attach the spreadsheet you're working on, that too would help us help you. Otherwise, you and we are both working in the abstract. Just make sure no confidential info is contained in your working example.
Thank you for responding, sorry if I was vague, this is the first time I have asked for assistance.
I am wanting to create a drop down box with the selection of a specific worksheet, and when you activate it, it takes you to that page, the workbook is designed for people who have limited excel understanding. So I am trying to make it easy for them to navigate through it. I have attached the document.
I may be trying to do this the wrong way? If you go to the Examples page, there is a return to results button, this is where I want the option to return to a specific page.
Thank you for trying to help me.
Cathy
Wow! That is an ambitious spreadsheet. I have to say, having looked at it, that using macros to go from sheet to sheet is not going to be your biggest problem. Now, I say that without knowing the big picture.
- Who are the expected (not skilled with Excel) users?
- Who is it that they'll be evaluating?
- What's the context in which they'll be doing these assessments?
I'm not asking you to answer those questions, just acknowledging that any observations, questions, or suggestions that follow are coming from a position of relative ignorance of the big picture.
- The first basic observation: going from sheet to sheet (tab to tab) is easy! Easy to do. Easy to learn. Having a macro to facilitate that seems overkill. Nice, but not at all a necessary feature.
- On the individual assessment sheets, are the assessors going to be filling these in "in real time" as they interview or observe the candidates? Or will they be taking notes on a paper sheet, an interview template of some kind, after which they (or somebody else) fills in the spreadsheet?
- Why so many redundant row headings (the leftmost column). Are you getting different reviewers describing the same candidate on the same dimension?
- IF, as seems possible, these sheets are going to be filled in by clerical people after the fact, working from written assessment sheets filled in by professional assessors, it would seem to me that you could dispense with the extensive (and wordy) drop down descriptors altogether, and just have a numeric "score" or "grade" for each behavioral dimension, a score or grade that appears on the paper data collection sheet alongside the verbal description. That would enable easier data entry AND you to use Excel to summarize an overall grade or result.......
Disclaimer: I'm not a big believer in macros to begin with. I have a few; I've written a few more. I do know they can come in handy. Instead, I do believe that a good design and full use of Excel's built-in capabilities can often accomplish results more smoothly, with fewer opportunities for "bugs" to creep in and muck things up. I'd be delighted to see one of the other experts here (I'm just a hanger-on myself, especially when it comes to macros) give you the macro you asked for.
My purpose in asking the questions and making those observations is to try to help you re-think the process into which this Excel tool is fitting. Are you making good use of Excel--looking primarily at how those data collection screens are designed--or will this end up being experienced as more burden than relief?
Or, could a re-design make it more user-friendly?
