MultiPage UserForm TextBox Event+Navigation Glitch

[DixiePiper]

I have a 4 page MultiPage control in a UserForm. On the 2nd page, I have a group of 2 option buttons followed by a series of TextBoxes in which I need to enter dates. To manage the TextBoxes, I have a separate UserForm, named CalendarForm, that serves as a date picker. After as much experimentation as I could manage, the only way I found to launch the CalendarForm was to use "Private Sub TextBox1_Enter()". That works fine for me. The issue I'm now having is with navigation. When entering the page, the CalendarForm launches. I need the CalendarForm restricted to interacting with the TextBox.

I'm using Excel 2013 in a Windows 10 environment. Here's the code snippets:

To launch the CalendarForm

Private Sub startBase_Enter()
    Dim dateVariable As Date
    
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then startBase = dateVariable
    
End Sub

The above repeats for three additional TextBoxes. Disabling the startBase event fixes the problem so I know it's got something to do with this TextBox.

I have navigation buttons located in the UserForm for "Cancel", "Back", "Next", and "Finish". Shown is the code for the buttonNext; the code is similar for buttonBack

Private Sub NextButton_Click()
    MultiPage1.value = MultiPage1.value + 1
    
    Select Case MultiPage1.value
        Case 1
            optionConcurrent.SetFocus
        Case 2
            btnSelectAll.SetFocus
        Case 3
            CommandButton12.SetFocus
    End Select
    
    UpdateControls
End Sub

"Update Controls" refers to a separate process that enables/disables the navigation buttons based on the current page; it's irrelevant to this issue. The SetFocus works just fine. When entering Page2, either via the "Next" or "Back" buttons, the CalendarForm launches and I have to close it to continue. What am I missing?

Thank you in advance for any assistance.

 

[cerfani]

Maybe you can have it always ignore the first event call with a boolean...

Dim mbEvents As Boolean

Private Sub startBase_Enter()
    If mbEvents = False Then
        mbEvents = True
        Exit Sub
    End If

    Dim dateVariable As Date

    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then startBase = dateVariable
End Sub

then maybe when you leave that tab page you set mbEvents back to False

Look here... for the basic idea...

http://dailydoseofexcel.com/archives/2004/06/08/disabling-events-in-userforms/

 

[jkpieterse]

I would probably just place a small button to the right of the textbox that displays the form.

 

[DixiePiper]

Thanks. I'll check out the article. In the meantime, your suggestion stops the CalendarForm from popping up when navigating to the page BUT it also disables CalendarForm when I click the TextBox. This seems to be my dilemma: it's one or the other. There has got to be a "both/and" solution.

 

[DixiePiper]

I would probably just place a small button to the right of the textbox that displays the form.

Thanks for the suggestion. That would be my absolute last resort. The form requires a series of six date ranges. The first two need to be entered; the remaining four are calculations based on the 2nd entry. There really isn't space and, from a design perspective, it unbalances the form. I'm hopeful there is a better solution.

 

[cerfani]

It should disable the event the first time. It is because your textbox gets focused when you navigate to the page, which means you have already entered the textbox, so you can't click it again to make the calendar appear. You need to focus another control first and this will allow you to enter again...

Dim mbEvents As Boolean

Private Sub startBase_Enter()
    If mbEvents = False Then
        mbEvents = True
        'focus something else here so you can click on the textbox and trigger Enter event
        Exit Sub
    End If

    Dim dateVariable As Date

    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then startBase = dateVariable
End Sub

I am not sure about winforms for vba but check to see if the form has the TabIndex property. In other languages like c#, windows forms allows you to say which controls get focused first. Winforms for VBA is not as good and more basic so it might not have that property.

 

[jkpieterse]

Yes Office userforms do have a tabindex for their controls.

 

[DixiePiper]

Okay, apparently the issue with the original solution you provided was where I put the "Dim mbEvents as Boolean" line. When your original suggestion didn't work, I pulled up the article and tried to figure things out. It was in there that I moved the Dim statement under Option Explicit. I didn't realize that was the problem until I tried your second solution and while it didn't work either, commenting out the 'focus something else here part made it work.

Edited: The original solution only works the first time I visit the tab, regardless if I take any action on the tab at all. Apparently I'm still missing something.

 

[cerfani]

you commented out my comment :D

The 2nd set of code I posted, I meant for you to actually call focus on another control on that page like a button maybe, or anything other than the textbox that calls the calendar form. When another control is focused it will allow you to enter the textbox again and at that point, it should work... in theory.

But you can skip all that stuff if you can set the tab index property of the form to have another control be focused when you open the page.

 

[DixiePiper]

Man, does my shorthand get me in trouble. I did understand the comment line in the code you posted. I tried to replace that by setting the focus on another control but it didn't work. Is it possible I didn't word it correctly? I then commented out my command and it worked. That being said, it's still a one-trick pony so to speak. The form launches on page 1, clicking Next takes you to page 2 where everything works beautifully, Next again takes you to page 3. So far so good. If, from page 3, I select Back, when I return to page 2, the CalendarForm pops up. In fact, it pops up an instant before the page changes.

Below is what I put in that didn't work.

Private Sub startBase_Enter()
    If mbEvents = False Then
        mbEvents = True
        optionConcurrent.SetFocus     'this is the line I substituted for your comment and then commented out
        Exit Sub
    End If
 
    Dim dateVariable As Date
 
    dateVariable = CalendarForm.GetDate
    If dateVariable <> 0 Then startBase = dateVariable
End Sub

I am so grateful for continued assistance.