Forum Discussion
Macros in Excel dropdown box
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?
Thank you for your response.
I was asked to create this spreadsheet for trainers that have very limited Excel knowledge. So have tried to make it easier for them to navigate through the spreadsheets. It will be compiled by the trainer at the time of the assessment. The candidate will be assessed twice, at the beginning of the course and then again at the end to see if they have improved. With the wordy drop down lists, this is needed by the department as it is what is required by the Language, Literacy and numeracy rules within Australia, so I have to use the actual wording.
You wrote: With the wordy drop down lists, this is needed by the department as it is what is required by the Language, Literacy and numeracy rules within Australia, so I have to use the actual wording.
What I'm questioning is not the wording, per se, but the use of that extensive wording in every drop down box. Those are actually very well worded behavioral descriptions, and I can see why they're useful. I would make sure that the assessors are referring to them in coming up with their evaluations.
They remind me of the tennis player rating system, which has a scale from 1 to 7, although most amateurs are in the range of 1 to 5. These are similarly extensive verbal descriptions of what the skills consist of at each level. Here's an example: http://www.bemidji.usta.com/Services_/_Info/NTRPPLAYERRATINGSCALE/
But in practice, although it's useful, very useful, to check the written description, to make which is applicable to any given player, one inevitably uses short-hand and simply says "She's a 3.5 player" or "He's a strong 4.0." Such short-hand can always be challenged by going back to the description, watching the player play, and pointing out variances from the standard. Verbal descriptions are good things.
I would suggest that you take those good verbal descriptions and place them at the top of each screen, pulling them from a table of ALL of the descriptions--the verbal, the mathematical, etc.--associating each skill level with a number from 5 to 1--but have the drop-downs just display the numbers. The descriptions are good descriptions, they need to be in the process, but do they really need to be in the drop-downs?
To be more clear, I'm suggesting that while you're on the verbal skill level, you display at the top of the screen those 4 or 5 descriptors of verbal skill levels. When the cursor moves to mathematical skill, the list at the top of the screen changes, etc. OR, even easier,, provide them with a printed card that has all the descriptors. The reality is that in using this, the assessors are only going to be thinking of a range from 5 to 1 or top to bottom anyway; they're not going to be reading all the words.
Any final output or summary report on Candidate XY would, of course, use a verbal summary to describe his or her actual skill levels. They just don't really need to be intruding into every assessment cell on the spreadsheet, where, in practice, they almost interfere with seeing and navigating the sheet.
Does that make sense? Keep the words, but use them in a different manner; in the process make the spreadsheet a bit simpler and easier to navigate.