Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

On way. In a standard code module add this code

Public fChangeEnabled As Boolean

Sub StartOnKey()
Application.OnKey "E", "EnableChange"
Application.OnKey "R", "DisableChange"
End Sub


Sub EnableChange()
fChangeEnabled = True
End Sub

Sub DisableChange()
fChangeEnabled = False
End Sub

and in your change event, test i fChangeEnabled - True. If not, exit.

--
HTH

Bob Phillips

"broogle" wrote in message
ups.com...
Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

This is my suggestion:

'Paste to the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If DisableStatus Then Exit Sub
'Your code in place of MsgBox
MsgBox "Test"
End Sub

'Paste to ThisWorkbook module
Private Sub Workbook_Activate()
With Application
.OnKey "E", "DisableWC"
.OnKey "R", "EnableWC"
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
.OnKey "E"
.OnKey "R"
End With
End Sub

'Paste to a standard module
Public DisableStatus As Boolean

Sub DisableWC()
MsgBox "Worksheet_Change event disabled"
DisableStatus = True
End Sub

Sub EnableWC()
MsgBox "Worksheet_Change event enabled"
DisableStatus = False
End Sub

Regards,
Greg


"broogle" wrote:

Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

Same as mine <g

"Greg Wilson" wrote in message
...
This is my suggestion:

'Paste to the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If DisableStatus Then Exit Sub
'Your code in place of MsgBox
MsgBox "Test"
End Sub

'Paste to ThisWorkbook module
Private Sub Workbook_Activate()
With Application
.OnKey "E", "DisableWC"
.OnKey "R", "EnableWC"
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
.OnKey "E"
.OnKey "R"
End With
End Sub

'Paste to a standard module
Public DisableStatus As Boolean

Sub DisableWC()
MsgBox "Worksheet_Change event disabled"
DisableStatus = True
End Sub

Sub EnableWC()
MsgBox "Worksheet_Change event enabled"
DisableStatus = False
End Sub

Regards,
Greg


"broogle" wrote:

Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

You beat me by 10 mins. Thought I'd posted to broogle. I wrote mine, tested
it, opened broggle's post, noticed no one had replied yet. So I went back and
copied my code (three different code modules). Thought I clicked on broggle's
post when I replied. Got to quite this late night posting.

"Bob Phillips" wrote:

Same as mine <g

"Greg Wilson" wrote in message
...
This is my suggestion:

'Paste to the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If DisableStatus Then Exit Sub
'Your code in place of MsgBox
MsgBox "Test"
End Sub

'Paste to ThisWorkbook module
Private Sub Workbook_Activate()
With Application
.OnKey "E", "DisableWC"
.OnKey "R", "EnableWC"
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
.OnKey "E"
.OnKey "R"
End With
End Sub

'Paste to a standard module
Public DisableStatus As Boolean

Sub DisableWC()
MsgBox "Worksheet_Change event disabled"
DisableStatus = True
End Sub

Sub EnableWC()
MsgBox "Worksheet_Change event enabled"
DisableStatus = False
End Sub

Regards,
Greg


"broogle" wrote:

Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

It was the fact you said 'suggestion' that prompted me to post. I was a bit
unsure about it, it worked but flaky I felt. Then I saw you had the same
idea, so I relaxed <vbg

Bob

"Greg Wilson" wrote in message
...
You beat me by 10 mins. Thought I'd posted to broogle. I wrote mine,
tested
it, opened broggle's post, noticed no one had replied yet. So I went back
and
copied my code (three different code modules). Thought I clicked on
broggle's
post when I replied. Got to quite this late night posting.

"Bob Phillips" wrote:

Same as mine <g

"Greg Wilson" wrote in message
...
This is my suggestion:

'Paste to the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If DisableStatus Then Exit Sub
'Your code in place of MsgBox
MsgBox "Test"
End Sub

'Paste to ThisWorkbook module
Private Sub Workbook_Activate()
With Application
.OnKey "E", "DisableWC"
.OnKey "R", "EnableWC"
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
.OnKey "E"
.OnKey "R"
End With
End Sub

'Paste to a standard module
Public DisableStatus As Boolean

Sub DisableWC()
MsgBox "Worksheet_Change event disabled"
DisableStatus = True
End Sub

Sub EnableWC()
MsgBox "Worksheet_Change event enabled"
DisableStatus = False
End Sub

Regards,
Greg


"broogle" wrote:

Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

I got less than 4 hours sleep last night and so was brain dead this morning.
When I saw your post I got the mistaken impression that I'd responded
accidentally to your post instead of broggle's. This mystified me because
yours hadn't appeared yet when I posted. But it was late and I thought I
screwed up. Just mentioned this in case you were wondering what I was talking
about.

Much appreciate your posts. You and Tom in particular amaze me.

Best regards,
Greg

"Bob Phillips" wrote:

It was the fact you said 'suggestion' that prompted me to post. I was a bit
unsure about it, it worked but flaky I felt. Then I saw you had the same
idea, so I relaxed <vbg

Bob

"Greg Wilson" wrote in message
...
You beat me by 10 mins. Thought I'd posted to broogle. I wrote mine,
tested
it, opened broggle's post, noticed no one had replied yet. So I went back
and
copied my code (three different code modules). Thought I clicked on
broggle's
post when I replied. Got to quite this late night posting.

"Bob Phillips" wrote:

Same as mine <g

"Greg Wilson" wrote in message
...
This is my suggestion:

'Paste to the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If DisableStatus Then Exit Sub
'Your code in place of MsgBox
MsgBox "Test"
End Sub

'Paste to ThisWorkbook module
Private Sub Workbook_Activate()
With Application
.OnKey "E", "DisableWC"
.OnKey "R", "EnableWC"
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
.OnKey "E"
.OnKey "R"
End With
End Sub

'Paste to a standard module
Public DisableStatus As Boolean

Sub DisableWC()
MsgBox "Worksheet_Change event disabled"
DisableStatus = True
End Sub

Sub EnableWC()
MsgBox "Worksheet_Change event enabled"
DisableStatus = False
End Sub

Regards,
Greg


"broogle" wrote:

Please help,

I have a worksheet_change event, I want to disable this event if key
"E" is pressed and enable it again if key "R" is pressed. Is it
possible? How can I do that?

Thanks

Bob and Greg.

Thanks a million.