Can a macro select from a drop down box?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I was wondering if a macro could select from a drop down box?

if so could i have a macro that when run selects the option in row two of the dropdown box?

thanks
Tony
 

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
I am not quite clear on what it is you are trying to do.

If you have a drop-down box, you can have macros either run based on the selection of a value in that drop-down box (or using the value selected in the drop-down box).
From your time on the board (6+ years, 2600 posts), I am sure that you have probably come across Worksheet_Change event procedure code, which is VBA code that automatically runs when some value is entered into a cell. So, you could have some code run a when selection is made in the drop-down box. In turn, the code could call other VBA code, if you want to run different code based on what the selection is.

If no value has been selected in the drop-down box, I don't know that it makes any sense to reference it (if nothing has been selected at that point in time).
If you are trying to do something based on the different values listed in the drop-down box, rather than what is selected, I think you may be approaching this problem from the wrong angle, and might need to come up with a better method. If you explain exactly what you are trying to do in more detail, we may be able to provide some options for you.
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Joe,
It is the other way around,
sorry i was probably being to fast,

i have a sheet with lots of dropdown boxes that we want to keep as dropdown boxes,

however as the sheet get filled out there are a lot of answers that go together,

for example if you have said the employee is male,
all the drop down boxes that just determine if its a he or a she could be auto filled,

I would normally do this by writing a macro that said something like if cell J7 = male then cell H4 ="He is a valued employee" else" She is a valued employee" for example,
the problem is i inherited this document and all the dropdown option are written into the data validation not in cells and there are hundreds of them

so what i wondering is if i can say to a macro select first / second option of the dropdown list as first is always the male and second the female

so imagine this,

I have about 50 documents to fill out every week,
a lot of that document is company dribble that is always the same, but the person setting it up was so afraid of offending someone by calling them "they" that they have created the entire form with dropdowns to say "HE" or "SHE" everywhere,

now i could rebuild this and add in simple if statements but its going to take me ages,

it would be great if i could just have a "Him Defaults" and "Her Default" Button that i could press and have it go to cell R17 and select 2nd drop down, J21 select 21nd dropdown etc.

now this might not be posible, but before i start doing overtime the week before christmas i thought i'd ask if anyoone knows a way to triger a dropbox box with a macro,
i can do the reast just need the simple bit of getting dropdown box row 1 into the cell?

hope that helps, and thanks for try
Tony
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think I would approach it this way:

Create three macros:
1. One that does all the necessary "He" work
2. One that does all the necessary "She" work
3. One that does all the necessary "They" work

Then, I would have a Worksheet_Change event procedure that tells it which one to run, based on the selection in our initial drop-down box.
Let's say that our initial drop-down box is in cell A1, and the options are "He", "She", and "They".
Then the Worksheet_Change event procedure code might look something like this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Address = Range("A1").Address Then
        Application.EnableEvents = False
        Select Case Target.Value
            Case "He"
                Call HeMacro
            Case "She"
                Call SheMacro
            Case "They"
                Call TheyMacro
        End Select
        Application.EnableEvents = True
    End If

End Sub
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi joe,
thanks for you help,
i'll see what i can come up with
Tony
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,649
Members
417,159
Latest member
Mayozero

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