call code from within a cell via an If type statment 2

[Elbown]

I am a novice whene it comes to VBA within excel but I would like to kick off some code dependent on the result typed into a cell on "Sheet1" ie if(sheet1!a1= a, start code,do nothing)
Any ideas how to do this with out haveing to click buttons etc...?

 

[SkipVought]

HI,

You can use the worksheet_change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Intersect(Target, Range("A1"))
    If Not rng Is Nothing Then MyMacro
End Sub

assuming that A1 is the cell of interest.

You can get to the sheet event code by fight clicking the sheet tab and selecting view code.

Paste this code into the code window.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[michaelbr55]

Hi,
Paste this code into the worksheet change event of the sheet you want the code to operate on.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1&quot.Value = "a" Then
' start you code here
MsgBox "You inserted an 'a', don't do it again! OK", vbCritical, "What the?"
Else: MsgBox "The value of cell 'A1'is " & Range("A1&quot.Value ' alternate code if cell changes but not 'a'
End If
End Sub

Mike

 

[Elbown]

Thanks Guys It all works like a charm