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
|
Similar Threads
