Re: VBA code ok in Excel 2003, but crashes Excel 2002 & 2000
- From: "Peter T" <peter_t@discussions>
- Date: Sat, 30 Jun 2007 13:07:16 +0100
Hi Tom,
I haven't tried it but slightly surprised it even works in XL2003. Try
changing
Private Sub CommandButton1_KeyDown(to
Private Sub CommandButton1_KeyUp(
- for any routine that will lead to changing the active sheet. Notice if you
press the Space key, which fires the click event, it only runs on KeyUp.
If you particularly prefer KeyDown for the other Case's, for the Case
Enter-key to activate another sheet, call code to do that in macro in an
ordinary module but called with the OnTime method.
In passing,
TextBox1_KeyDown includes TextBox1.Activate
and
CommandButton1_KeyDown includes CommandButton1.Activate
Why, when the respective controls already have focus.
In the snippet you posted there's no need to disable Screenupdating
Regards,
Peter T
"tomgreen1000" <tomgreen1000@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C0C2092-0B5E-4CCA-B0C1-1FA78FD6EDE1@xxxxxxxxxxxxxxxx
Hello. I have a very complicated (and poorly constructed) legacysegment
program/spreadsheet that I must now support. I have found a VBA code
that is causing some problems. The actual code is excessivelycomplicated,
so I have recreated this simple example.button.
Open a new workbook. On Sheet 1, place one text box and one command
Sheet 2 should be completely blank. Sheet 3 can be deleted or left asis.
Click into the textbox. The intent of the VBA is to allow you to use Tab,step
Return, or Down Arrow to get onto the command button. Once on the command
button, Shift+Tab or Up Arrow will return you to the textbox. Tab or Down
Arrow will keep you on the command button. Using Return, while on the
command button, should execute the code associated with the Click Event.
In Excel 2003, everything works as expected. In Excel 2002 or 2000, the
code executes correctly if the command button is clicked. However if you
attempt to use Return to execute the Click Event, Excel crashes. If you
thru the code, everything executes ok until the last End Sub. I need thisto
work in Excel 2002, so any ideas or work arounds will be greatlyappreciated.
object.
I am attempting to cut/paste the code which is located on the Sheet1
Thanks for your help, Tom
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
If bBackwards Then TextBox1.Activate Else CommandButton1.Activate
Application.ScreenUpdating = True
End Select
End Sub
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Dim bBackwards, bForwards As Boolean
Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
bForwards = (KeyCode = vbKeyTab) Or (KeyCode = vbKeyDown)
If bBackwards Then
TextBox1.Activate
ElseIf bForwards Then
CommandButton1.Activate
Else
Call CommandButton1_Click
End If
Application.ScreenUpdating = True
End Select
End Sub
Private Sub CommandButton1_Click()
Sheet2.Cells(1, 1).Value = "test"
Sheet2.Activate
End Sub
.
- Prev by Date: Re: Slow Macro
- Next by Date: Re: Slow Macro
- Previous by thread: UserForm - Disable the 'x' button
- Next by thread: Enter Range value and get report
- Index(es):
Relevant Pages
|
