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.
