More then 1 worksheet_change event

[John]

Hi
I'm just starting to work with VBA and below are my codes.
I would need several routine like this one on the same worksheet but VBE won't
let me ( Can't have more than 1 worksheet_change ).
Can anyone help me ?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("B1")) Is Nothing Then
If Range("B1") = "on" Then Me.Shapes("Autoshape 5").Visible = True
If Range("B1") = "off" Then Me.Shapes("Autoshape 5").Visible = False
End If
If Range("B1") = "on" Then
Range("A12").Value = "Please go to the next Question"
End If
If Range("B1") = "off" Then
Range("A12").Value = ""
Range("B1").Value = ""
End If

Range("B1").Select
Application.EnableEvents = True
End Sub

Best regards
John

 

[John Bundy]

If i understand correctly, you have several different pieces of code you want
to run on worksheet change? If so you would want to create a new sub, and
call that sub, one for each thing you wanted to do.

Private Sub Worksheet_Change(ByVal Target As Range)
NameOfRoutine()

End Sub

Private Sub NameOfRoutine

If Not Application.Intersect(Target, Range("B1")) Is Nothing Then
If Range("B1") = "on" Then Me.Shapes("Autoshape 5").Visible = True
If Range("B1") = "off" Then Me.Shapes("Autoshape 5").Visible = False
End If
If Range("B1") = "on" Then
Range("A12").Value = "Please go to the next Question"
End If
If Range("B1") = "off" Then
Range("A12").Value = ""
Range("B1").Value = ""
End If

Range("B1").Select
Application.EnableEvents = True
End Sub

 

[Mike H]

John,

You can only have one worksheet change event for each sheet but theres
nothing to have you having boolean statemenst in that event code to decide
which part to execute:

For example


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1")) Is Nothing Then
MsgBox "Run the code associated with B1"
'your b1 code
Exit sub
ElseIf Not Application.Intersect(Target, Range("C1")) Is Nothing Then
MsgBox "Run the code associated with C1"
'your c1 code
End If
End Sub

Mike

 

[Jeff]

Hi John, Sounds like you need to trap the Workseet_change event. This is
done by creating a class module I named mine "wsChangeEvent" in that class
module place Somthing like this:

option explicit

Public WithEvents Worksheet As Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Your Code here"
End Sub

In Each work sheet that you want to have your code run put somthing like

Dim ws As wsChangeEvent

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set ws = New wsChangeEvent
Set ws.Worksheet = ActiveSheet
End Sub

Every time the "worksheet_Change" Event is fired your code will run.

HTH

 

[John]

Thank you all for your answers.
I will need to work with the answers I got and see if I can make it work for me,
I'm new to this. Will get back with answer.
Thank you All again
Regards
John

 

[john]

As mike already stated, worksheet change event for each sheet.
Is this approach something like what you are trying to do?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Target, Me.Range("B1")) Is Nothing Then

If LCase(Me.Range("B1").Value) = "on" Then

Me.Shapes("Autoshape 5").Visible = True

Me.Range("A12").Value = "Please go to the next Question"

Else

Me.Shapes("Autoshape 5").Visible = False

Me.Range("A12").Value = ""

End If

ElseIf Not Application.Intersect(Target, Me.Range("C1")) Is Nothing Then



'do more stuff

ElseIf Not Application.Intersect(Target, Me.Range("D1")) Is Nothing Then

'do more stuff
End If


Application.EnableEvents = True

End Sub

 

[Otto Moehrbach]

John
What you want to do is really rather simple and is very common. If you
will post back and provide a few of the things you want to happen, someone
will be able to write a sample of what you need. HTH Otto

 

[John]

Hi John
I think you got it, from what I can see, it should do the trick.
I didn't try it yet, I'm new to this and very slow.
Thank you

Thank you

 

[John]

Hi Otto
I'm trying to build a Simulator questionnaire.
There would be around 30 questions each with 3 to 5 answer to choose from.
With each question, I would like to have a shape appear and possebly have in
some case change color depending on the answer they will pick. So I need about
30 shapes that will popup at different question and activated by different
cells.
I think I got the answer from the Guys before you but just in case, I didn't get
a chance to try it yet.
I was going to ask this question later but here goes.
I'm using Autoshapes for my test but would like to have my own, can I had
autoshapes of my choice. I need drawings that in some case can change the color,
I know how to change the color of the Autoshapes.
Thank you
Regards
John