Help with Immediate Window

[Ayo]

I have this:
Application.EnableEvents = False
in my code. And I think it is causing some problems with the way the code
works. I know there is something I can do in the Immediate Window to clear it
but I don't remember the function to use.
Any ideas.

 

[Mike H]

Hi,

Application.EnableEvents = False

This simply suppresses error messages that you otherwise have received if
(for example) you deleted a worksheets with VB code.

To get a more helpful answer you will probably have to post the offending
code and in addition describe the 'problem' including what it is you want to
'clear'

Mike

 

[Ayo]

The Worksheet_Activate() sub is not working but the code jumps straight to
the Worksheet_Change(ByVal Target As Range) sub. This is where the error
comes in because I don't get the chance to select values for Range("D3")
which is dependent on Range("A3") which is dependent on the
Worksheet_Activate() sub.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("A3") = ""
Range("D3") = ""
Range("G3") = ""
Range("A3").Select

With Range("A3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="CENTRAL,NORTHEAST,SOUTH,WEST"
End With
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim marketWS As Worksheet, rngmarketWS As Range

If Target.Address() = "$A$3" Then
Application.EnableEvents = False
Application.ScreenUpdating = False

Range("D3") = ""
Range("G3") = ""
Range("D3").Select

Select Case Target.Value
Case "CENTRAL"
With Range("D3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _

Formula1:="ARKANSAS,CHICAGO,CINCINNATI,CLEVELAND,COLUMBUS,DENVER CO,DES
MOINES,DETROIT MI," & _
"INDIANAPOLIS IN,KANSAS CITY KS,KNOXVILLE
TN,LOUISVILLE,MILWAUKEE,MINNEAPOLIS MN," & _
"NASHVILLE,OKLAHOMA CITY OK,OMAHA,PITTSBURGH
PA,ST.LOUIS,TULSA OK,WICHITA KS"
End With

Case "NORTHEAST"
With Range("D3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="CENTRAL PA,CONNECTICUT,LONG ISLAND - NY,NEW
ENGLAND MARKET,NEW JERSEY NJ,NEW YORK NY," & _
"NY (UPSTATE),PHILADELPHIA
PA,VIRGINIA,WASHINGTON DC"
End With

Case "SOUTH"
With Range("D3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="ATLANTA,AUSTIN TX,BIRMINGHAM,CAROLINA,DALLAS
TX,HOUSTON TX,JACKSONVILLE,MEMPHIS," & _
"MIAMI FL, MOBILE,NEW ORLEANS,ORLANDO,PUERTO
RICO,TAMPA FL"
End With

Case "WEST"
With Range("D3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="ALBUQUERQUE NM,EL PASO TX,HAWAII HI,INLAND
EMPIRE,LA NORTH,LAS VEGAS,LOS ANGELES," & _
"PHOENIX,PORTLAND OR,SACRAMENTO,SALT LAKE CITY
UT,SAN DIEGO,SAN FRANCISCO,SEATTLE WA," & _
"SPOKANE WA"
End With
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address() = "$D$3" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Range("G3").Value = Evaluate("=SUMPRODUCT(--('BO
Download'!$A$4:$A$30000=$A$3),--('BO Download'!$B$4:$B$30000=$D$3),--('BO
Download'!$H$4:$H$30000=""Selected""))") & " Sites"


'""""""""""""""""""""""""""""""""""""""""""""""""""""""'
'" DETERMINE THE START AND END ROWS OF THE MARKET "'
'""""""""""""""""""""""""""""""""""""""""""""""""""""""'
Dim marketName As String
Dim startRow As Long, endRow As Long

marketName = Range("D3").Value
startRow = firstRow(marketName)
endRow = lastRow(marketName, startRow)
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

 

[Mike H]

Hi,

Try disabling events

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
application.enableevents=false
Range("A3") = ""
Range("D3") = ""
Range("G3") = ""
Range("A3").Select

With Range("A3").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="CENTRAL,NORTHEAST,SOUTH,WEST"
End With
Application.ScreenUpdating = True
application.enableevents=true
End Sub

 

[Gord Dibben]

I think Mike meant to say the statement disables events from firing.

Depending upon the code, it is most often beneficial to disable events to
prevent continuous re-firing of the event.

Test with this code with enablevents set to True.

You will see 199 Ayo's in Immediate Window which is how many times it
fired.............seems to be a VBA limit of 199

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Debug.Print "Ayo"
'Application.EnableEvents = False
If Target.Address = "$A$1" Then
Range("a2").Value = Range("a1").Value
Else
If Target.Address = "$A$2" Then
Range("a1").Value = Range("a2").Value
Else
End If
End If
Application.EnableEvents = True
End Sub

Now run it with events disabled after clearing Immediate Window. Only one
Ayo

If you do disable events make sure you re-enable before ending sub

Best to set a trap for errors.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
stuff gets done here
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Mike H]

I think Mike meant to say the statement disables events from firing

I did mean that Gord, It's late and I'm tired, Thanks for the correction

Mike

 

[Rick Rothstein]

To answer the question you asked, just execute this in the Immediate Window
(I think you are going to kick yourself when you see what it is)...

Application.EnableEvents = True