Selected cell contents running a macro

[ChuckDrago]

Cheers everyone!
Question: Assume you have a cell containing a data validation list. One of the values in the list is "Service". Given this, is it possible to fire a macro whenever the user chose "Service", while doing nothing if any other value was selected?
Chuck

 

[Joe4]

Sure! Use Event Procedure VBA code, specifically the Worksheet_Change event that fires whenever a value is changed.

Here is a write-up on Event Procedures: http://www.cpearson.com/excel/Events.aspx

Here is a little snippet of code that will pop-up a message box whenever the value in cell I1 is changed to "Service" (just so you can see how this works).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("I1").Address And Target = "Service" Then
        MsgBox "Macro Triggered!"
    End If
End Sub

 

[ChuckDrago]

Joe, many thanks but I need some guidance here...

I adapted the snippet to my needs, thus:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Sheets("Form").Range("D8").Address And Target = "Service" Then
Call ChoiceEnabler
End If
End Sub

Installed this in the ThisWorkbook module. ChoiceEnabler is a sub residing in Module 1 and what it does is to open a form for the user to continue choosing options.

When I selected the value "Service" form the list shown on range("D8") nothing happened, which points to my request for additional help.

 

[Joe4]

The macro must be placed in the Worksheet module that it is running from (looks like the sheet name is "Form"). The easiest way to get there is from Excel, right click on the sheet tab name and select "View Code", and paste the code there.

Since it only applies to the current sheet it is located in, you do not need the Sheet reference in your code.

 

[ChuckDrago]

Super!

Joe, it works like a charm!
Many, many thanks!

Chuck

 

[Joe4]

Great! Glad to help!

Those Event Procedures are a powerful tool. You can have VBA code automatically run upon such events as changing a value, selecting a cell, opening the workbook, saving the workbook, etc.

 

[ChuckDrago]

Wholehartedly agree!

I used them copiously in opening workbooks, before save, before close, etc. but never came across to the cell value change one... Now I did and it will be treasured in my arsenal.
Thanks again, Joe

Chuck