Open Drop Down List and Date Picker with Target.Address

[TRLOS]

Its been many many years since I've done VB Scripting, so I apologize if this is a simple noob question.

I have created a type of form on Excel which will be used on a touch screen tablet.
I need 2 VBA scripts please.

I have used 2 worksheets. The first is the main form. The second worksheet is where I have a bunch of Data Values which are used in Data Validation Drop Down boxes.

Script 1 :
I have created a Drop Down List in Cell F16. At current, when you select F16, I can see the small arrow to the right of the cell so when you click on it, it pulls the Drop Down box down so you can select a value.

My question is, what is the VBA script so that when I select that particular cell, it opens the Drop Down list automatically (ALT+Down Arrow) ?
Can you make it that you can use the same script for the following 10 cells below the first one ?

I have found a similar code but have no idea how to change it to do what I want

Please Login or Register  to view this content.

(is this the right script or is there another better suited?)

Script 2 :
I have created a Date Picker Calendar (the same as specified on this site 'fontstuff.com/excel/exltut03.htm)
I have added the following in VBA so that it inserts the Selected Date in the Highlighted Cell

Please Login or Register  to view this content.

Is there a way to automatically open the Date Picker when any cell from J16 - M38 is selected ?


The reason I want these scripts is because this Excel sheet will primarily be used on a touch screen, so would make it easier to use by Single-Tapping on the cell and have it automatically open the relevant areas (Drop Down Box & Date Picker)

Any help would be greatly appreciated.

Thanks in Advance

[TRLOS]

I have found a possible solution for Script 1 needed

Please Login or Register  to view this content.

F16 (Merged with G16) is currently a Drop Down List cell. Excel recognizes the cell at F16 though
If I change the Reference to H16 (standard cell), this script works perfectly. When I change it to F16, it doesn't work.
I changed F16 to an unmerged cell and it works.

It seems that the script doesn't like Merged Cells.
Is there a way around this ?

[arlu1201]

Welcome to the forum. 2 pointers -

1. I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

2. Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[TRLOS]

SOLUTION for Script 1

Please Login or Register  to view this content.

Still need help with Script 2