VBA to auto display drop down list when cell selected

Hi folks

As a relative newbie to VBA, with any queries I have I try a web search, forum search and then I post! I am pretty sure my issue is not unique and I am pretty sure there will be another post alredy listed... I never quite know the right phrase to enter in the search tools to find what I am looking for!

So, after many searches which result in me going nowhere fast, I apologise for the request in advance!

I have some data validation lists to control data quality in my spreadsheet. As with most users, in order to select a response I must firstly click in to the cell, which in turn displays the dropdown handle, then click the handle and finally make a selection from the list displayed.

Please can you assist me with - How can I achieve an automatic display of the dropdown as soon as I click in to the relevant cell, using VBA?

Will the code be cell specific or can it be a more holistic code, which I would imagine slows down the performance?

I hope the fact that I am a Office for Mac user (Office 365 v16.15) does not further complicate matters... most of the users of my creation will be PC users

Thank you in advance

To do this in VBA requires programming to create mouse clicks. It is complicated and in 20 minutes I could not collect enough information to determine the screen coordinates for the click.

If you are just trying to save clicks, you could use comboboxes instead, which displays the dropdown handle all the time, and you can click directly on that.

Hi Ed

What is the nature of your data validation?

A List or a value within a range?

You can be column Specific

Please Login or Register  to view this content.

Or range specific

Please Login or Register  to view this content.

The code belongs in the worksheet module.

RightClickTab.jpg

Originally Posted by 6StringJazzer

To do this in VBA requires programming to create mouse clicks. It is complicated and in 20 minutes I could not collect enough information to determine the screen coordinates for the click.

If you are just trying to save clicks, you could use comboboxes instead, which displays the dropdown handle all the time, and you can click directly on that.

Thank you for the note about combo boxes, I have considered them. Unfortunately there are so many lines in the sheet where I want the functionality it makes the sheet look really messy.

Originally Posted by mehmetcik

Hi Ed

What is the nature of your data validation?

A List or a value within a range?

Without sounding strange, both!

The data validation is from a table column. I have created an INDIRECT reference in the data validation to point me back to the table that holds the drop down data (so the user can amend the list at another point in time without the need for my intervention)

Thank you for your response

Originally Posted by davesexcel

You can be column Specific

Please Login or Register  to view this content.

Or range specific

Please Login or Register  to view this content.

The code belongs in the worksheet module.

Attachment 584255

Thank you for responding. Unfortunately neither set of options works. I had found another piece of code with the ("%{DOWN}") reference and the code crashed at that point.

Using your code had no effect when I click the cells... I must be doing something wrong!

I'll do a search, maybe there is a different sendkeys for mac.

You could use a selection change macro to open a Userform

Right Click on your sheet name at the bottom of excel and select view code

Paste this code into the module that opens an then close it.

Please Login or Register  to view this content.

Create a Userform containing a list box

Double click on the listbox to view its code/

replace its code with

Please Login or Register  to view this content.

Thank you, that does indeed work, just takes time opening and closing, I do appreciate the effort though. There are literally hundreds of these dropdowns.

Apologies, l I never knew send keys did not work for mac.

If you search "excel sendkeys mac alternative" you could get some results, I can't test them out.

This Ammended Code works for Lists and Ranges

Please Login or Register  to view this content.

Originally Posted by davesexcel

Apologies, l I never knew send keys did not work for mac.

If you search "excel sendkeys mac alternative" you could get some results, I can't test them out.

Thank you. Does that mean it will work for my Mac but not ofr PC users though?

Yes.

If you manually select a cell with a dropdown list and hit the Keys "Alt & Down arrow" the cell list will drop down. Application.sendkeys just does that programmatically.

Thank you. I am grateful for all the responses

A Mash Up of Daves Solution and Mine

Please Login or Register  to view this content.