VBA UserForm - stopping the Textbox Enter event from running when selecting the frame

[NicholasPerkins]

Hi everyone,

Long time reader, first time poster.

App/OS: Excel 2003/Windows XP

I am developing a VBA User form which contains three frames. Each frame contains a number of textboxes. Each textbox for date input has a textbox_Enter event which shows a date picker form (using the Calendar control).

However when I click on the frame or on a field in the form, the event triggers for the first tabindex textbox field (if it is a date field).

I understand that this is because when I click to enter the frame, Excel selects the first tabindex textbox first and then moves to the one I have selected.

This also occurs when I have a multipage form. When I move to a different page, the first field is selected and if it is a date textbox, the event fires.

The code I have written for the event isn't very complex:

Private Sub tbEndPhaseThree_Enter()
    SYS_generalCode.strActiveField = "tbEndPhaseThree"
    OpenDatePicker curValue:=Me.tbEndPhaseThree.Value
End Sub

I did consider using a Boolean variable to stop the event firing for the first time, however if I do select the first tabstop field it won't fire at all. And I would need a Boolean variable for each frame/page.

Another idea was to always have a tabindex of 0 on an item that won't trigger the event. It is a last straw really as it isn't elegant.

I'm at a loss, so any suggestions of directions to go in would be appreciated.

Thanks,
Nick

 

[NicholasPerkins]

Well I've had a response away from this board which I've used to solve my problem, so I thought I'd share it here.

Rather than using the _Enter event, I've used the DblClick() event for the textboxes. This is much more elegant, as users can select the current data and copy/paste to other places if they need to. A quick dblclick, or a keystroke (I also have a _KeyDown event) and they have the date selection form pop up for them.

The help came through Twitter by someone reading the issue here and knowing where to find me, so thanks for the help in a round about way.

Cheers,
Nick