VBA code ok in Excel 2003, but crashes Excel 2002 & 2000

[tomgreen1000]

Hello. I have a very complicated (and poorly constructed) legacy
program/spreadsheet that I must now support. I have found a VBA code segment
that is causing some problems. The actual code is excessively complicated,
so I have recreated this simple example.

Open a new workbook. On Sheet 1, place one text box and one command button.
Sheet 2 should be completely blank. Sheet 3 can be deleted or left as is.
Click into the textbox. The intent of the VBA is to allow you to use Tab,
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 step
thru the code, everything executes ok until the last End Sub. I need this to
work in Excel 2002, so any ideas or work arounds will be greatly appreciated.

I am attempting to cut/paste the code which is located on the Sheet1 object.

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

 

[tomgreen1000]

Not sure what happened, but the third to last line of code should be

Sheet2.Cells(1, 1).Value = "test"

 

[Peter T]

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]

Peter, thank you so much!!! KeyUp works not only in this simple example but
also the legacy program that I am trying to support. The setting of focus
(when the control already had focus) and the ScreenUpdating are remnants of
this much more complicated code. I left them in the simple example because I
put it together in such a hurry.

You said to use KeyUp "for any routine that will lead to changing the active
sheet." Is that do to something inherent with the KeyUp behavior? How did
you know this?

Thanks again! You have solved a week of frustration.

Regards,

Tom

 

[Peter T]

You said to use KeyUp "for any routine that will lead to changing the

active sheet."
Is that do to something inherent with the KeyUp behavior?

I suspect, more likely the problem relates to the changing state of the
control while no longer on the active sheet.

How did you know this?

I've been hit by that before, and other issues over the years. There was
much more to contend with in XL97 but it seems newer versions become less
susceptible to such problems.

FWIW, although changing focus within the active sheet in the KeyDown event
works, it might be worth moving everything to the KeyUp

Regards,
Peter T

 

[tomgreen1000]

You said to use KeyUp "for any routine that will lead to changing the

I suspect, more likely the problem relates to the changing state of the
control while no longer on the active sheet.

That makes sense (now!).

FWIW, although changing focus within the active sheet in the KeyDown event
works, it might be worth moving everything to the KeyUp

Done, per your previous advice.

Thanks again!

Tom

-----------------