Macro IF cells Contains ""; Then ""

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was wondering of somebody was familiar with the possibility to start
different macro's if the contents of a cell I refer to different then I want.

For example: In most cases the Cell A38 on sheet "Quote" contains "General",
if so I want to activate macro "Sub DeleteInseteredStation()" If it contains
something else however; I want it to launch "Sub ClearContentsStation()"

Does Anybody know what Macro code could be used? Thanks in Advance!
 
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A38" = "Quote" Then
DeleteInseteredStation
Else
ClearContentsStation
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob Phillips wrote...
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A38" = "Quote" Then
DeleteInseteredStation
Else
ClearContentsStation
End If

ws_exit:
Application.EnableEvents = True
End Sub
Click to expand...
....

The boolean expression

Target.Address(False, False) = "A38" = "Quote"

will always be false, so it looks like a bug. Did you mean


If Target.Address(False, False) = "A38" Then
If Target.Value = "Quote" Then
DeleteInseteredStation
Else
ClearContentsStation
End If
End If


?

Even if so, I don't believe worksheet-level Change event handlers are
the ideal place for this. Wouldn't it make more sense to put this into
workbook-level SheetCalculate event handler? As in,


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
On Error GoTo Cleanup:
Application.EnableEvents = False

If Worksheets("Quote").Range("A38").Value = "Quote" Then
DeleteInseteredStation
Else
ClearContentsStation
End If

Cleanup:
Application.EnableEvents = True
End Sub
 
Harlan Grove wrote...
....
Even if so, I don't believe worksheet-level Change event handlers are
the ideal place for this. Wouldn't it make more sense to put this into
workbook-level SheetCalculate event handler? As in,
Click to expand...
....

OK, need to prevent this running multiple times when Quote!A38 remains
"General" (not "Quote") but several recalcs take place. That is, keep
track of state transitions.


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static s As Boolean
Dim t As Boolean

On Error GoTo Cleanup:
Application.EnableEvents = False

t = Worksheets("Quote").Range("A38").Value = "General"

If s Xor t Then
If t Then DeleteInseteredStation Else ClearContentsStation
s = Not s
End If

Cleanup:
Application.EnableEvents = True
End Sub


Caveat: [Sheet]Calculate event handlers alone make sense only when
there are volatile functions used in some formulas. Otherwise, need to
have both [sheet]Change and [Sheet]Calculate call the same subprocedure.
 
Back
Top