Macro to activate drop down in cell

fgd

New Member
Joined
Mar 3, 2004
Messages
49
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

New Member
Joined
Mar 3, 2004
Messages
49
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

New Member
Joined
Mar 3, 2004
Messages
49

ADVERTISEMENT

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

fgd

New Member
Joined
Mar 3, 2004
Messages
49
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

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

New Member
Joined
Mar 3, 2004
Messages
49
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,451
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top