Keypress

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.