Button clicks and textbox Exit events

[Linc]

I'm having a problem with button Click event handlers not running the
first time
when a button click causes a textbox Exit event handler to write data
to a cell.

Using Excel97 and Windows XP Pro, I have an employee data spreadsheet
set up
like a database with column headers in row 1 and employee records in
rows 2
through 118. The column header cells are each named ranges
corresponding
(usually) to the text in the cells.

Clicking a "Record View" button on the spreadsheet opens a form
containing
textboxes corresponding to the employee data columns on the datasheet.
Each
textbox has Exit event code which checks to see if the textbox data has
changed;
if so, the code validates the data where necessary and writes valid
data back to
the proper datasheet cell.

The form also has a series of buttons at the bottom: New, First, Prev,
Next,
Last, and OK. "New" clears the textboxes and writes any subsequent
entries to
the first empty row in the employee data area of the datasheet. "OK"
simply
calls an End to dismiss the form. The other four buttons navigate
through the
records as one would expect.

If text in a textbox is changed and the Tab key is pressed everything
runs
exactly as intended. However, if I change data in a textbox and click
any of
the buttons, the Exit event handler for the textbox runs and changes
the data in
the datasheet, but the button Click event handler does not run. If I
then make
another change and click the same button, the handlers for both the
Exit and
Click events run, and this becomes the case for any of the other
buttons.

Here's the simplest example of one of my Exit event handlers:

Private Sub txtFirstName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtFirstName <> FieldEntry(1) Then
FieldEntry(1) = txtFirstName
Cells(R, [FirstName].Column).Value = FieldEntry(1)
End If
End Sub

FieldEntry() is a String array containing all of the fields for the
current
record. [FirstName].Column gets the column number for the column header
cell in
row 1 of the datasheet named "FirstName". R is the datasheet row for
the currnet
record displayed by the form.

One of my button Click event handlers:

Private Sub btnNext_Click()
If (R < 118 And Cells(R + 1, 3).Value <> "") Then
R = R + 1
PopulateForm
txtFirstName.SetFocus
End If
End Sub

The first time I make a change in txtFirstName and click the "Next"
button,
txtFirstName_Exit() fires and runs flawlessly but btnNext_Click() fails
to run.
If I click "Next" again, I get the next record, and if I change any
textbox data
and click "Next" again both handlers run. Changing a textbox and
clicking any of
the other buttons will also then work. If I had clicked "Prev" instead,
the same
thing happens, i.e., btnPrev_Click() will not run unless I click it
again.

What I know:

The culprit seems to be the line that writes the data back to the
datasheet:
Cells(R, [FirstName].Column).Value = FieldEntry(1)
If I comment this line out both the Exit and Click event handlers
always run,
but of course the changed data is never written to the datasheet.

When a button is clicked and its Click event fails to run, the button
in
question is highlighted, which means it has taken the focus. However,
each of
the buttons also has an accelerator key assigned, e.g., "n" for "Next."
If I use
Alt-n instead of clicking the "Next" button both handlers run **without
fail,
every time.**

This behavior occurs under both WinXP Pro and Win98SE, and whether I'm
running
the spreadsheet from our Novell network or from a PC.

I usually have to reboot the PC to make the bad behavior begin again
once it
goes away. When closing the spreadsheet and reopening it, or closing
Excel and
reopening it, everything works fine if was working before. The CPU
cache might
have some bearing on this, and I haven't tried opening other programs
to flush
this code.

I can code around this if I have to, but I can't see why it shouldn't
work first
time, every time when I click the buttons.

Any ideas?

Thank you.

 

[Jim Cone]

Linc,
Try setting the TakeFocusOnClick property of the
command button to False.
Jim Cone
San Francisco, USA

 

[Linc]

To anybody who managed to wade through my initial post, my apologies
for the wonky formatting. I didn't think pasting from Notepad with word
wrap turned on would mess things up so much.

Jim, thanks for your suggestion. But I already tried that, to no avail.
I also tried using Range("FirstName") in place of the shortcut
[FirstName] and moved code blocks around, neither of which changed
anything. I'm in total SWAG mode right now.

I started doing some rewriting on a copy of the file in case there's no
answer to my problem. I'll call a sub to write data to the datasheet
from the buttons' Click event handlers and write handlers for the
textboxes' Enter events to do the same thing. The Exit event handlers
will simply send up a "Changed Data" flag for the other handlers to
deal with. Here's hoping it works. Or better yet, that someone out
there can figure out the answer to this problem before I get back to
work on Monday so I don't have to finish the rewrite.

 

[Linc]

I wrote:

"I'll call a sub to write data to the datasheet from the buttons' Click
event handlers and write handlers for the textboxes' Enter events to do
the same thing. The Exit event handlers will simply send up a "Changed
Data" flag for the other handlers to deal with. Here's hoping it
works."

It involves quite a bit more code in comparison to my original, but it
does indeed work. And it's even a bit faster for some reason.

Apparently, when VBA encountered this line in a textbox's Exit event
handler...

Cells(R, [FirstName].Column).Value = FieldEntry(1)

....and the Exit event was triggered by clicking on a button, VBA
updated the cell and then promptly forgot where the focus was supposed
to have gotten to. Therefore, it didn't run the button's Click
handler. By using the button's Click event handler to call a Sub that
in turn writes the data using Cells(), VBA is able to write the data
and continue with the handler. The focus doesn't get "lost" because
VBA doesn't deal with any other control.

I still wish I knew if the original code was encountering a bug in
Excel VBA, or if I was just missing something. Ah well.