VBA Exiting Textbox by mouse click versus tab

[Dan Waterloo]

Here is a simplified version of my situation on an Excel VBA form. I have a checkbox and three text boxes. Let's call them Checkbox, Textbox1, Textbox2, and Textbox3. This is also their tab order.
Checkbox1 and TextBox1 are related by code:
1. When Checkbox1 is checked then Textbox1 is enabled by setting its background to opaque and the focus is placed on it.
2. When Checkbox1 is unchecked then the contents of Textbox1 are set to blank and is is disabled and its background set to transparent.
3. If the user exits Textbox1 while leaving it blank then Checkbox1 gets unchecked (and so the logic in the 2. above gets executed.)

The problem that I am having is that it seems to matter whether I exit TextBox1 by mouseclick or by <Tab> or <Enter>. <Tab> and <Enter> both behave the same way so I'll just refer to both as tab.
Here is the behavior I see:
1. If I go to Checkbox1, leave it unchecked, and press tab, the cursor goes to Textbox2, as expected.
2. If I go to Checkbox 1 and check it then I end up in Textbox 1 as expected. If I now enter non-blank input and exit the textbox by mouseclick or tab, then Textbox1 will have the valid value in it. If I mouseclicked out then the control I clicked on will have focus. But if I tabbed out then TextBox2 will have focus. This is all as expected.
3. If I go to Checkbox 1 and check it then I end up in Textbox 1 as expected. If I now enter blank input or do nothing at all and thereby leave it blank and then exit by mouseclick, then Checkbox1 becomes unclicked, Textbox1 has its background changed to opaque, and the focus goes to Textbox3. This is what I expect to happen.
3. However, if I go to Checkbox 1 and check it then I end up in Textbox 1 as expected. If I now enter blank input or do nothing at all and thereby leave it blank and then exit by TAB, then Checkbox1 becomes unclicked, Textbox1 has its background changed to opaque, and the focus goes to Textbox4. This is NOT what I expect to happen.

Help would be appreciated. Code for the two controls follows below.

Private Sub CHECKBOX1_Change()
   Dim isChecked As Boolean
  
   With myForm
      isChecked = .CHECKBOX1.Value
     
      .LABEL_TEXT1.Enabled = isChecked
      .TEXT_TEXT1.Enabled = isChecked
     
      If isChecked Then
         .TEXT1.BackStyle = fmBackStyleOpaque
         .TEXT1.SetFocus
      Else
         .TEXT1.BackStyle = fmBackStyleTransparent
         .TEXT1.Value = ""
   End With
End Sub

Private Sub TEXT1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If Trim(TEXT1) = "" Then CHECKBOX1.Value = False
End Sub

 

[Worf]

1. If I go to Checkbox1, leave it unchecked, and press tab, the cursor goes to Textbox2, as expected.


Do you mean text box 1?


and the focus goes to Textbox4. This is NOT what I expect to happen.

You said there were only three text boxes.

Please clarify.

 

[Dan Waterloo]

Thanks for responding, Worf.
I have been able to figure this out in the meantime and should have updated this to say so.
First, I will answer your questions. Sorry for the ambiguities.
1. No, I meant Textbox2. When Checkbox1 is left unchecked, then Textbox1 is disabled. I wasn't clear in the description, but the initial condition is that Checkbox1 is unchecked and Textbox1 is disabled. So tabbing past Checkbox1 will cause control to go directly to Textbox2.
2. The reference to Textbox4 is just plain wrong. I meant Textbox3.

When I look at the code, I also see that I was referring to both .TEXT_TEXT1 and .TEXT1 and intending those to mean the same thing when they are not. I was working from an actual project and was trying to make the code more generic but I wasn't accurate. After I made my first post, I went and made an actual simplified prototype with real code so I could test it. Here's what the form looks like

when initialized:

after CheckBox1 is checked:

and then unchecked while Textbox1 is empty:

after tabbing out of empty TextBox1 before the fix:
I was expecting the cursor to be in TextBox2 but it was ending up in TextBox3.

And here is the code related to the various controls:

Private Sub CheckBox1_Change()
   Dim isChecked As Boolean
  
   With UserForm1
      isChecked = .CheckBox1.Value
     
      .Label1.Enabled = isChecked
      .TextBox1.Enabled = isChecked
     
      If isChecked Then
         .TextBox1.BackStyle = fmBackStyleOpaque
         .TextBox1.SetFocus
      Else
         .TextBox1.BackStyle = fmBackStyleTransparent
         .TextBox1.Value = ""
      End If
   End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If Trim(TextBox1) = "" Then CheckBox1.Value = False
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If (KeyCode = vbKeyTab Or KeyCode = vbKeyReturn) And Trim(TextBox1) = "" Then
      CheckBox1.Value = False
      KeyCode = 0
   End If
End Sub

The solution is in that third bit of code. What was happening was that when I exited from TextBox1 when it was blank by using the Tab key (or Enter key) then the Exit action caused the CheckBox1.value to be set to False. Since CheckBox1 changed, the logic related to that was then invoked so TextBox1 was disabled and focus passed to TextBox2. However, the Tab (or Enter) was still waiting to occur and that caused the focus to pass to TextBox3. The TextBox1_Keydown code checks for this situation and neutralizes the Tab (or Enter) but only if TextBox1 is blank.