Can a macro select from a drop down box?

[tonywatsonhelp]

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

 

[Candyman8019]

It can. There are several posts on this board discussing the topic. Here is one as an example: VBA to select the next item in a data validation list | MrExcel Message Board

 

[Joe4]

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]

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]

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:

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]

Hi joe,
thanks for you help,
i'll see what i can come up with
Tony

 

[Joe4]

You are welcome.