Help with Immediate Window
 

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.

Help with Immediate Window
 

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" wrote:

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.

Help with Immediate Window
 

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,C OLUMBUS,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" wrote:

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" wrote:

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.

Help with Immediate Window
 

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

"Ayo" wrote:

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,C OLUMBUS,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" wrote:

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" wrote:

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.

Help with Immediate Window
 

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

On Tue, 1 Dec 2009 12:25:03 -0800, Ayo
wrote:

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.

Help with Immediate Window
 

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

"Gord Dibben" wrote:

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

On Tue, 1 Dec 2009 12:25:03 -0800, Ayo
wrote:

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.

.

Help with Immediate Window
 

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

--
Rick (MVP - Excel)


"Ayo" wrote in message
...
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.