Textbox KeyUp event

[Boog]

Hello Y'all,

Using Vista HP SP1 and Excel 2007 SP2.

I created a workbook using XP Home / Excel 2002. I am updating the workbook
on a Vista system for use with Excel 2007 and saved the workbook as an
..xlsm. The workbook update is to simply make any code changes from Excel
2002 to Excel 2007 and to take advantage of changes in Excel 2007 over Excel
2002 such as a cells gradient color fill, 3D features, etc.

I am starting this thread for any explanation(s) of the following behavior
but I have since found two solutions.

The problem I had was that an ActiveX textbox KeyUp event was not firing for
any alphanumeric characters; only for Ctrl, Shift and Alt. The KeyDown and
KeyPress events worked fine. I have another workbook that I have made
similar changes to and the textbox KeyUp events work fine.

Initially, I deleted the textboxes and recreated them without success.

The first solution I found was to use the textbox KeyPress event in
combination with the Change event.

Then, I copied a textbox from a functioning workbook to the problem workbook
and the KeyUp event worked problem free for the new textbox. The only
difference between the textboxes was that the troubled textboxes had linked
cells and the working ones did not. I eliminated the linked cell from the
textboxes and the KeyUp events started functioning normally. Strangely, I
opened a blank workbook, added a textbox with a linked cell and the KeyUp
event worked fine ???

I searched this NG and spent time Googling and Binging to see if this was a
documented problem but I had no joy.

There is obviously something corrupt in this particular workbook. It
otherwise functions as desired.

I don't have any specific question. I am mentioning this in case others
have encountered this and I am (slightly) curious regarding any possible
explanations. I put my money on stuff happens <LOL>.

Thanks,

Boog

 

[Peter T]

Of the top of my head I can't think of any difference in behaviour with
Textbox events between XL 2002/2007. Why not post your code, also say in
which version the original workbook was created (if not new workbooks in
each respective version).

Regards,
Peter T

 

[Boog]

Hello Peter,

Thank you for your reply and your expertise.

I did mention the workbook was created in XP Home / XL2002. I didn't post
any code at the time because when it ran it performed the desired tasks.
The problem was that no code was running at all with the KeyUp event.

Here is the KeyUp code for one of the textboxes:

Private Sub DateOfSurgery_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If Trim(ActiveSheet.DateOfSurgery) = "" Then
ActiveSheet.PreOpAppointment = ""
ActiveSheet.SurgicalProcessing = ""
ActiveSheet.PostOpAppointment = ""
ActiveSheet.Range("C7") = ""
ActiveSheet.Range("F7:I7") = ""
Call ActiveSheet.ShadeCells("Surgery")
ActiveSheet.ReturnToMain.Visible = False
ActiveSheet.AddWait.Visible = False
InvalidDOSFlag = False
ActiveSheet.Range("A1").Activate
ActiveSheet.DateOfSurgery.Activate
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Exit Sub
End If
If KeyCode = 9 Or KeyCode = 13 Then
If ActiveSheet.ReschedulePatient Then
FocusFlag = True
If ActiveSheet.Range("C7") = "Wait List Entry" Then
ActiveSheet.AddWait.Activate
Else
ActiveSheet.PreOpAppointment.Activate
End If
ElseIf Not ActiveSheet.Patient = "" Then
FocusFlag = True
ActiveSheet.Range("A1").Activate
If ActiveSheet.ReturnToMain.Visible Then
ActiveSheet.ReturnToMain.Activate
ElseIf ActiveSheet.AddWait.Visible Then
ActiveSheet.AddWait.Activate
End If
Else
FocusFlag = True
ActiveSheet.Patient.Activate
End If
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Exit Sub
Else
If ActiveSheet.ReschedulePatient Then
FocusFlag = True
End If
End If
With ActiveSheet.DateOfSurgery
.BackColor = RGB(255, 255, 255) ' White
.ForeColor = RGB(0, 0, 0) ' Black
.FontName = "Times New Roman"
.Font.Bold = False
.Font.Size = 11
End With
End Sub

Ironically, I don't know why I assigned a linked cell to these textboxes
when they were created. I didn't require that. It probably doesn't matter.
Another option for me to try would be to copy the workbook, delete the
sheets with the problem textboxes in the new workbook and copy the sheets
back from the old to the new workbook and resave the new workbook. Perhaps,
their functionality would be restored. Since I have the KeyUp events
working again in the existing workbook that would be a measure of futility.

Any other ideas given the code ?

Boog

 

[Peter T]

Hello Boog,

There's quite a bit of "I wouldn't do it like that" but nothing obvious I
can see that's wrong.

I'm sure the event will fire fine irrespective of what key is pressed.
Here's one scenario that would give the impression that keyup fails only if
shift is not pressed

' keydown event
if shift = 0 then AnotherContol.Activate
or
if shift = 0 then Application.EnableEvents = false
' code
' user releases the key
Application.EnableEvents = true

If focus is not on the control or events disabled when the key is released
you won't get a keyup event.

That's only a couple of possibilities, you may need to add/remove code as a
process of elimination to find the cause. When you do I'll bet it's
something obvious!

Regards,
Peter T

 

[Boog]

Peter,

I initially wrote the code a few years ago. I have, since then, learned to
write more efficient code thanks in large part to this NG. I don't believe
I have the energy at the present time to review all the lines of code in the
workbook to improve on the "I wouldn't do it like that" code. Besides, I'm
not sure the user would notice much change...in speed anyhow. I work on a
federal installation and our network has so much security and encryption
software that a snail's pace is speeding along.

Once I make my current changes, I will likely return to this workbook to
examine and change the code as needed.

Until then, let's consider this matter closed. I certainly appreciate your
time and consideration with my problem.

Thanks very much.

Boog

 

[Peter T]

Boog,

I don't believe I have the energy at the present time to review all the
lines of code in the workbook to improve on the "I wouldn't do it like
that" code.

In hindsight I should have found a different way to say what I meant.
Absolutely no criticism was intended and apologies if it was taken that way.

Regards,
Peter T

 

[Boog]

Peter,

There are no apologies necessary. It was an honest comment from an
individual has more knowledge of this subject than I do. I am always
prepared for constructive criticism and that it how it was perceived.

As I relooked at this workbook to make the conversion to XL2007, it did
cross my mind to make code improvements, Then I thought to myself, there
must be hundreds of subs and functions. I put it in abeyance for antoher
day.

Once again, I appreciate your insight.

Boog