Excel/Running Macro

Advertisement


Question
QUESTION: Hi Tom
is it possible to run a Macro just by changing a value of a specific cell or do you have to make a command button every time?
Thanks
Eric

ANSWER: Eric,

The answer is yes and I assume you will change the value of a particular cell by editing it manually.  

If you right click on the sheet tab where you want this behavior and select view code, it will take you to a vba class module associated with that sheet.  At the top of that module you will see two dropdowns.  In the left dropdown select Worksheet (may already be selected) and in the right dropdown select Change (not SelectionChange).  Doing this will put an event procedure in your class module:  

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


The code placed in this event will fire everytime you exit a cell after you edit it.  So the first code you need to put in it is code to cause it to stop running unless the cells you are interested in are the cell(s) that triggered the change event.  The only argument to the event is Target.  This is a range reference and it will be a reference to the cell or cells that triggered the event - the cell or cells that have changed/been edited.

Assume cell D4


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = $D$4 then

' the code in this section will run only if a cell D4 was edited or you pasted to cell D4
End if

End Sub



as a test, let's have it display a message:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = $D$4 then

' the code in this section will run only if a cell D4 was edited or you pasted to cell D4
 Msgbox "The triggering cell is " & Target.Address(0,0)
End if

End Sub

That should get you started.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom
Thanks for getting back so quickly.
I have followed you instructions and placed the "Target.Address" as you suggested but I am getting an error message "Compile error: Invalid character" associated with the "$" in my cell address $D$6.
The code I am running is as below and I want to change the visibility of some labels as per the number in "$D$6":

   Range("D6").Activate
   PAX = ActiveCell.Value
 If PAX >= 1 Then
   ActiveSheet.Shapes("Label22").Visible = True
 Else
   ActiveSheet.Shapes("Label22").Visible = False
 End If
 
 If PAX >= 2 Then
   ActiveSheet.Shapes("Label25").Visible = True
 Else
   ActiveSheet.Shapes("Label25").Visible = False
 End If

 If PAX >= 3 Then
   ActiveSheet.Shapes("Label23").Visible = True
 Else
   ActiveSheet.Shapes("Label23").Visible = False
 End If

Once again thanks for your help.

Answer
Eric,

That was my typo/omission.  $D$6 is a string, so it must be in double quotes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$6" then

' the code in this section will run only if a cell D4 was edited or you pasted to cell D4
 Msgbox "The triggering cell is " & Target.Address(0,0)
End if

so to add your code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim PAX
If Target.Address = "$D$6" Then

  
 PAX = ActiveCell.Value
 If IsNumeric(PAX) Then
 If PAX >= 1 Then
 If PAX >= 1 Then
   ActiveSheet.Shapes("Label22").Visible = True
 Else
   ActiveSheet.Shapes("Label22").Visible = False
 End If
 
 If PAX >= 2 Then
   ActiveSheet.Shapes("Label25").Visible = True
 Else
   ActiveSheet.Shapes("Label25").Visible = False
 End If

 If PAX >= 3 Then
   ActiveSheet.Shapes("Label23").Visible = True
 Else
   ActiveSheet.Shapes("Label23").Visible = False
 End If
 End If
 End If
End If
End Sub



--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.