Macro to activate drop down in cell

[fgd]

My current macro asks the user for the employee ID takes the user to the row that contains that employee ID then stops. The cell it stops on contains a drop down box.
How do I get the macro to continue and display the drop down menu, allow the user to make a selection from a list of five options, then move the cursor a number of cells to the right based on the menu item selected?
Thanks.

 

[Tom Urtis]

By "drop down box" I assume you mean data validation. If that's the case (as opposed to a combobox or listbox you drew on the sheet), then at the point in your macro where the cell is activated, make this be the next line of code:

SendKeys "%{down}"


Regarding the act of moving to another cell to the right, that really should go into a separate Change event procedure in your worksheet module.

Further assuming that your version of Excel is after Excel 97, try this. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, 5).Activate
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Modify for range of interest and offset parameters. Be advised, if you are using xl97, Change events are not triggered by data validation dropdown activity so this would not work.

 

[fgd]

Tom,
Great help - the data validation box drops down exactly as I had hoped.

As for moving the cursor after the selection, I'm lost.

My data validation is for the kind of donation and is located in cells Q1 thru Q5 and is:
Payroll
Check
Bill at Home
Credit Card
Cash

If donation is via Payroll, I'd like the cursor to move one cell to the right on the same row. If not Payroll, move cursor 4 cells to the right on the same row.

You've already made our lives easier.
Many Thanks.

 

[Tom Urtis]

OK then, so far so good. Do this:

Right click on your sheet tab, left click on View Code, and delete the SheetChange procedure I posted for you yesterday.

In its place, now that we know the actual range (Q1:Q5), and what you want to have happen (which you've described well), paste the following procedure right there in your sjeet module, and then press Alt+Q to return to the worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Q1:Q5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Select Case Target.Value
Case "Payroll"
Target.Offset(0, 1).Activate
Case Else
Target.Offset(0, 4).Activate
End Select
End Sub


This should do what you need, based on your description. If you get stuck, post back.

 

[fgd]

Tom,
That did it! It will save us lots of time and effort. Couldn't have done it without your help!
Thanks again.

 

[fgd]

About 90% of the time the payment will be via payroll. Any way to make payroll the default so 90% of the time the user simply hits enter and the menu item payroll is selected?
Many thanks

 

[HalfAce]

Hi fgd,
I don't see Tom at the moment so maybe this will help.
Try opening your dropdown list and choose Payroll. Then select another cell and save the workbook. Now when you run your code the dropdown should still drop down, but Payroll should already be selected.

If there's a proper way to do this then I'm sure Tom will know it when he gets a chance to check this post again.

In the meantime, hope this helps.

 

[fgd]

I thought i noticed that effect at the office but here at home on XP is doesn't seem to work.
Might modifying the macro do it?
Thanks again.

 

[Tom Urtis]

If it is as common as 90%, you might consider just adding a line of code in your existing macro to bypass the drop-down, enter "Payroll" and be done with it, such as
ActiveCell.Value = "Payroll"

If you want to go the distance by showing the drop-down and have the act of hitting the Enter key default to entering the value "Payroll" and have the appropriate cell to the right activated, then that takes a bit more code.

In the worksheet module, in addition to your existing Change code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Q1:Q5")) Is Nothing Then
Application.EnableEvents = False
Target.Value = "Payroll"
Application.EnableEvents = True
Application.OnKey "{ENTER}", "EnterPayroll"
Else
Application.OnKey "{ENTER}"
End If
End Sub

In a standard module (NOT the sheet module), enter this macro:

Sub EnterPayroll()
ActiveCell.Value = "Payroll"
End Sub

 

[fgd]

We're in production. Great help.
Many thanks.