MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Running a Macro if specific text is entered?


Posted by Christopher Patrick on September 18, 2001 12:37 PM

Hi all-

Is it possible to enter a formula/some conditional into a cell which basically states if a cell is equal to a certain value, then execute the macro?

Thanks for any help

Chris


Posted by Juan Pablo on September 18, 2001 1:03 PM

Try this on your worksheet code pane

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value = "SpecificText" Then YourMacro
End Sub

Juan Pablo

---------------

Posted by Christopher Patrick on September 18, 2001 2:09 PM

Thanks for your help, but can you explain what the:
ByVal Target As Excel.Range
means? Do I put my cell range in here?

Thanks again

Chris

Posted by Christopher Patrick on September 18, 2001 2:09 PM

Thanks for your help, but can you explain what the:
ByVal Target As Excel.Range
means? Do I put my cell range in here?

Thanks again

Chris

Posted by Juan Pablo on September 18, 2001 2:23 PM

There are two things that you should change.

First, to be sure you're doing the right thing you should have done this:

Open Tools - Macro - Visual Basic Editor.

Next double click on the sheet that you want to check for the text. In there you should copy the macro i gave you.

Now, to the customization.

Change the "specifictext" string with YOUR string, that means if you want the macro to run everytime the user writes "Whatever" then put "Whatever" in there. The second part is after the "Then", it's says Yourmacro, change this to your macro module name... it could be something like Macro1 or something like that.

Nothing else is to be changed.

Now, to try it, in the sheet you wrote the code (The one that you double clicked on) type the specific text, and you should see your macro run.

If not then post the code you're using and i'll look at it.

Juan Pablo

Posted by Juan Pablo on September 18, 2001 2:35 PM

Sorry, forgot the explanation.

The Target means the Range (Cells) that just changed... for example, if you wrote in A5 "Hello" then Target would be B5... do you understand ?

Juan Pablo

Posted by Christopher Patrick on September 19, 2001 6:17 PM

Got it to work..thanks! Do you know where I can find more info in general to read about the Worksheet_Change subroutine (and other such subroutines), as well as the Excel or other VB objects?

Thanks again

chris