Have a Macro run when a certain criteria has been met.

[jdohler]

Hi,

This is my first post so bare with me if I am not to clear. I have a wookbook that contains 2 sheet. The first sheet is an evaluation, where the second sheet contains a list with the names, positions, and ranks of people.

In my Sheet "Evaluation" I have a list box where you can select the name of the person you want to evaluate. When this is done, the information of the personal is displayed in the desired cells. In this case, cell B3 is the list of possible names, while B5 contains the Rank (done through Vlookup off the sheet named "list"). Example: when someone selects "John Doe" from the list of name in B3, the Rank "Cpl" appears in cell B5.

I have multiple option boxes that are grouped together but are set to 0 (unclicked) by default. Once clicked (example: non observed) the value in cell H13 goes to a 1 and increases by one depending on performance.

This is where I need help. I want it so that if someone selects a person that contains the rank of either Pvt or Cpl, the value in H13 goes to a 1 automatically. So I need a macro that runs automatically behind the scenes.

So can someone help? Much appreciated.

[JOHN H. DAVIS]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    Select Case Target.Value
    Case Is = "Pvt", "Cpl"
        Range("H13").Value = 1
    Case Is = "Captain"
        Range("H13").Value = 2
    End Select
End If
End Sub

Not sure I understood so try on a copy first.

[jdohler]

Hi John,

Unfortunately that didn't work. if you have an e-mail, I could send you the file. PM me.

[JOHN H. DAVIS]

Why not attach a file so the entire board can have a look? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[jdohler]

I wish! At work everything is restricted except the option to send e-mails. I'll have to wait a few hours till tonight and do it from home.

[jdohler]

Ok, Here's the attached excel book. To translate: Selectionner Nom is select name and Grade = Rank.

When selecting a name from the drop list, if it returns either Pvt or Cpl, I would like for cells H13, 17, 21 and 25 to have the value 1. (Option Non Observe will be selected automatically).

Thanks

[jdohler]

Any late night help?

[JOHN H. DAVIS]

There no attachment? However since you marked as solved. I guess you worked it out.