Excel 2010 VBA Code to Open a Drop Down List

[Hotmail]

I'm trying to create a worksheet that will allow for minimal user input (mouse clicks and keyboard interaction). I have cobbled together the start of what I need based on what I've already found in the forum. The first is to enter a time stamp into a cell (within a range in column A) when it's double-clicked, then move to the adjacent cell in column B. In the cells in column B I have a data validation list. So the second thing I want to happen is to automatically expand the drop down list in the column B cell so my user can simply select a choice that's shown. And then the third and final thing I'm wanting to happen is that after the selection is made for column B I want the cell selection to move to the right again, to the adjacent cell in column C.

So this for example... user double-clicks A2 and the time is entered, then B2 is automatically selected and shows a data validation drop down list, the user selects a choice, and then is automatically taken to C2.

I was able to get the date in column A and then move to column B using this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    With Target
            .Value = Time
            .Offset(0, 1).Activate
    End With
End If
End Sub

And I was able to make my data validation drop down list appear when I "manually" selected a specified cell by clicking on it by using this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Err1:
    If Target = Range("B10") Then
        Application.SendKeys ("%{UP}")
    End If
Err1:
    do nothing
End Sub

Since I don't want manually select a cell (by clicking it) I figured I could plug the "SendKeys" line under the ".Offset(0, 1).Activate" line in the time stamp code. When I test it I think I can see the drop down list flash on the screen, but it doesn't stay so the selection can be made. Any thoughts on how to fix this? Or is there a more efficient way to accomplish this? Thanks!

 

[realPrincessApril]

@Galaxea I used your code 4 times in the same worksheet (4 ref columns, with each one having two cols to right that would clear then fire dropdowns).

This is partially to help someone potentially in the future who stops by and partially to see if I am potentially running into trouble lol, but it seems to be working with these changes:

1. Based on some research it seemed as though the Exit Sub lines might be problematic when multiple worksheet changes are going on. I'm not sure if this was necessary but I applied that advice and changed from:

    If Target.Count > 2 Then Exit Sub

    If Intersect(Target, rng) Is Nothing Then Exit Sub

    If Target.Count > 2 Then End If

If Not Intersect(Target, rng) Is Nothing Then End If

2. After playing for a while, with dropdowns firing all over the place, I realized that repeating the code 4 times, even within the same sub, was not working, at least not how I was doing it. So again some research and found that I could just add the two lines of code with the target column as multiple IFs (not nested at least it didn't seem right to nest I just listed them like this):

    Application.EnableEvents = False
    If Left(Target.Address(, False), 1) = "G" Then ActiveSheet.Range(Target.Offset(, 1), Target.Offset(2, 2)).ClearContents
    Application.EnableEvents = True
  
    If Left(Target.Address(, False), 1) = "K" Then ActiveSheet.Range(Target.Offset(, 1), Target.Offset(2, 2)).ClearContents
    Application.EnableEvents = True

'repeat for other target cols as needed