| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.EnableEvents = false not disabling events
Hello, I'm having a problem that I was hoping .enableevents = false would
solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
Brad,
I've run into this problem before. From your question it seems that events such as TextBox23_Change() is getting triggered when your form is starting. Is that correct? I tried .EnableEvents as well with no success. What I ended up doing was declaring a boolean variable at the top of the code (general, declarations section). Then I use this variable to turn on/off sections of the code. Dim MyEnableEvents as Boolean UserForm_Activate() MyEnableEvents = False TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" .... YOUR CODE ... MyEnableEvents = True End Sub TextBox23_Change() ... use this format in the code you don't want triggered. If MyEnableEvents = True Then ... Your code ... End If End Sub See my discussion with Jon Peltier "suspend processing for TextBox_Change events when initializing" drhalter "Brad" wrote: Hello, I'm having a problem that I was hoping .enableevents = false would solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
drhalter,
That is exactly what I have textbox23.change(). I have done as you suggested and it works perfectly, but like you said to Jon, it is a pain to have to add that statement to all of the events. Thank you for your help, it's much appreciated. "drhalter" wrote: Brad, I've run into this problem before. From your question it seems that events such as TextBox23_Change() is getting triggered when your form is starting. Is that correct? I tried .EnableEvents as well with no success. What I ended up doing was declaring a boolean variable at the top of the code (general, declarations section). Then I use this variable to turn on/off sections of the code. Dim MyEnableEvents as Boolean UserForm_Activate() MyEnableEvents = False TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" ... YOUR CODE ... MyEnableEvents = True End Sub TextBox23_Change() ... use this format in the code you don't want triggered. If MyEnableEvents = True Then ... Your code ... End If End Sub See my discussion with Jon Peltier "suspend processing for TextBox_Change events when initializing" drhalter "Brad" wrote: Hello, I'm having a problem that I was hoping .enableevents = false would solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
drhalter,
Here is something strange, maybe you (or someone) can shed some light onto this. Hopefully this makes sense because it's weird (at least to me). I did what you suggested with the MyEnableEvents and that works fine, however if look at my code (before using your suggestion), it triggers individual events: Start Code' If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets End Code' However, in another part of the code the events don't get triggered. Start Code' LP5.Value = Sheets("data").Range("line5lp").Value 'event doesn't trigger LP5 = WorksheetFunction.Text(Range("line5lp"), "$0.00") 'event doesn't trigger SP5.Value = Sheets("data").Range("line5sp").Value 'event doesn't trigger SP5 = WorksheetFunction.Text(Range("line5lp"), "$0.00") 'event doesn't trigger Total5.Value = Sheets("data").Range("Line5tot").Value 'event doesn't trigger Total5 = WorksheetFunction.Text(Range("Line5tot"), "$0.00") 'event doesn't trigger EOS5.Value = Sheets("data").Range("eosdte5").Value 'event doesn't trigger ComboBox5.Value = Sheets("data").Range("model5").Value 'event doesn't trigger end code' Why would LP5 (etc..) not trigger, but LP1 (etc..) trigger? Hopefully that makes sense. Thanks, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
What event is still firing?
Application.enableevents will stop worksheet, workbook, application events. It won't stop combobox1_change events. But you can do your own housekeeping. At the top of your userform module: Option Explicit Dim BlkEvents as boolean 'now your code Private Sub UserForm_Activate() 'Don't hide your errors! 'On Error Resume Next blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered blkproc = false blkproc = true Enddate.Value = Range("h56").Value 'event gets triggered blkproc = false ..... private Textbox697_change() if blkproc = true then exit sub 'rest of real code end sub And if you're doing lots in a row, you could stack them: blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value .... blkproc = false Brad wrote: Hello, I'm having a problem that I was hoping .enableevents = false would solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
Dave, I've tried this and it works. However, the full code is very big. If
you take the below section of code and repeat 29 additional times (the variables changes, LP2, SP2, Total2 - LP3, SP3, Total3, through LP30, SP30, Total30). So, I've added the errorhandling and it comes back with "Out of stack space". I've stepped through my code and at: LP1.Value = Sheets("data").Range("line1lp").Value it triggers the event: Private Sub LP1_Change() LP1.Value = Sheets("data").Range("line1lp").Value LP1 = WorksheetFunction.Text(Range("line1lp"), "$0.00") end sub Which goes into a continous loop. Any ideas? Also, it only triggers events from the code lines that contain LP1, SP1, Total1, through LP4, SP4, Total4. Everything else steps through without triggering an event. I can post the entire code if you'd like, it is very repetitive (probably could use looping but don't know how) and very large. Let me know if it kosher to post full code. Thanks. "Dave Peterson" wrote: What event is still firing? Application.enableevents will stop worksheet, workbook, application events. It won't stop combobox1_change events. But you can do your own housekeeping. At the top of your userform module: Option Explicit Dim BlkEvents as boolean 'now your code Private Sub UserForm_Activate() 'Don't hide your errors! 'On Error Resume Next blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered blkproc = false blkproc = true Enddate.Value = Range("h56").Value 'event gets triggered blkproc = false ..... private Textbox697_change() if blkproc = true then exit sub 'rest of real code end sub And if you're doing lots in a row, you could stack them: blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value .... blkproc = false Brad wrote: Hello, I'm having a problem that I was hoping .enableevents = false would solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
application.EnableEvents = false not disabling events
It's fine to post the full code, but if it's too complex, you may not get many
people to read through it. But this sub: Private Sub LP1_Change() LP1.Value = Sheets("data").Range("line1lp").Value LP1 = WorksheetFunction.Text(Range("line1lp"), "$0.00") end sub is trying to change the value of LP1 (twice!) And as soon as the first line executes, the lp1_change event has to fire. And over and over and over. It'll take awhile to even get to the second line of that procedure! Option Explicit Dim BlkProc as boolean Private Sub LP1_Change() if blkproc = true then exit sub blkproc = True 'if the cell is already displays what you want: LP1.Value = Sheets("data").Range("line1lp").Text 'or use VBA's version of the =Text() worksheet function 'but don't use both! LP1.Value = Format(sheets("data").range("line1lp").value, "$0.00") blkproc = false end sub But I don't understand what this subroutine is doing. If the user changes the value of whatever LP1 is, then you're overriding it with what's in that cell. Why let the user change anything--just plop the value into a label and show it to them. Brad wrote: Dave, I've tried this and it works. However, the full code is very big. If you take the below section of code and repeat 29 additional times (the variables changes, LP2, SP2, Total2 - LP3, SP3, Total3, through LP30, SP30, Total30). So, I've added the errorhandling and it comes back with "Out of stack space". I've stepped through my code and at: LP1.Value = Sheets("data").Range("line1lp").Value it triggers the event: Private Sub LP1_Change() LP1.Value = Sheets("data").Range("line1lp").Value LP1 = WorksheetFunction.Text(Range("line1lp"), "$0.00") end sub Which goes into a continous loop. Any ideas? Also, it only triggers events from the code lines that contain LP1, SP1, Total1, through LP4, SP4, Total4. Everything else steps through without triggering an event. I can post the entire code if you'd like, it is very repetitive (probably could use looping but don't know how) and very large. Let me know if it kosher to post full code. Thanks. "Dave Peterson" wrote: What event is still firing? Application.enableevents will stop worksheet, workbook, application events. It won't stop combobox1_change events. But you can do your own housekeeping. At the top of your userform module: Option Explicit Dim BlkEvents as boolean 'now your code Private Sub UserForm_Activate() 'Don't hide your errors! 'On Error Resume Next blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered blkproc = false blkproc = true Enddate.Value = Range("h56").Value 'event gets triggered blkproc = false ..... private Textbox697_change() if blkproc = true then exit sub 'rest of real code end sub And if you're doing lots in a row, you could stack them: blkproc = true Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value .... blkproc = false Brad wrote: Hello, I'm having a problem that I was hoping .enableevents = false would solve but it doesn't work at all. The events are still being triggered. The problem is when I activate the userform, it triggers events that are associated with the particular object. Below is my code with comments next to the lines that have events that are being triggered: How can I stop these events from triggering? I've tried Application.EnableEvents = False but it's not stopping them. Code- Private Sub UserForm_Activate() Application.EnableEvents = False On Error Resume Next Startdate.Value = Range("e56").Value 'event gets triggered Enddate.Value = Range("h56").Value 'event gets triggered TextBox697.Value = Sheets("data").Range("F9").Value 'event gets triggered TextBox23.Value = Sheets("data").Range("J101").Value * 100 & "%" 'event gets triggered If Sheets("data").Range("abselect1").Value = "Y" Then Abuse1.Value = True End If If Sheets("data").Range("inwarnty1").Value = "Y" Then inwty1.Value = True End If Term1.Value = Range("trm1").Value LP1.Value = Sheets("data").Range("line1lp").Value 'event gets triggered SP1.Value = Sheets("data").Range("line1sp").Value 'event gets triggered Total1.Value = Sheets("data").Range("Line1tot").Value 'event gets triggered EOS1.Value = Sheets("data").Range("eosdte1").Value 'event gets triggered ComboBox1.Value = Sheets("data").Range("model1").Value 'event gets triggered Application.EnableEvents = True -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Application.Enableevents = False doesn't stop all events? | Excel Programming | |||
| if AddIn_1 isRunning vbcr application.enableEvents=False vbcr end if: how to do this?? | Excel Programming | |||
| preventing Application.EnableEvents = False | Excel Programming | |||
| Application.EnableEvents = False not working | Excel Programming | |||
| Problems with BeforeSave and Application.EnableEvents = False | Excel Programming | |||
Hybrid Mode
