More then 1 Private Sub Worksheet_Change

  • Thread starter FIRSTROUNDKO via OfficeKB.com
  • Start date
F

FIRSTROUNDKO via OfficeKB.com

HI,

can i have ore then 1 "Worksheet_Change" sub?

i.e

I havea sub, Private Sub Worksheet_Change

but, Private Sub Worksheet2_Change

will not work
 
R

Rick Rothstein

No, you will just have to combine the code you want in your two Change
events into the Change event that VB provides. You can use If..Then blocks
to separate the functionality you wanted in your proposed separate Change
events.
 
J

JLGWhiz

Not fot the same sheet. However, you can have the worksheet change do more
than one thing by using If...Then statements. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
Set sh = ActiveSheet
If sh.Range("A1").Value > 0 Then
'Do one thing
End If
If sh.Target.Value = "Do Something" Then
'Do another
End If
If Not Intersect(sh.Selection, Target) Is Nothing Then
"Do somethiing else
End If
End Sub

Only when the If statement is true will the code execute a command to do
something. If none of them are true then it executes no commands. If all
of them are true it executes all three commands.
 
Top