Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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 "John" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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 -- jb "John" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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" wrote in message ... 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 -- jb "John" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 worksheet_change event
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 "Otto Moehrbach" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorkSheet_Change event | Excel Programming | |||
Worksheet_Change Event | Excel Programming | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
Worksheet_Change Event | Excel Programming | |||
Worksheet_Change Event | Excel Programming |