Controlling TabStop

[mikeymay]

I have a UF with textbox, comboboxes, control buttons, etc.

The form required information to be add.ed to the entry controls.

I am using the _Change and _AfterEvent events to pull in the text added, perform checks, etc.

I generally use the TabStop and TabIndex properties to 'move' the cursor to the next control so the user can use the TAB key to progress through the form.

This presents problems though when a textbox requires a specific entry, e.g. a date format. If the entry isn't recognised as a date format (I am using the REPLACE function to replace any full stop/dots with a slash but I can't do anything for an entry such as '23st maar 203'), I want the entry to be removed and the that control retain focus. Using TabIndex and TabStop moves the focus to the next control which when a valid entry is added, I want to happen but I don't if an invalid entry has been added.

I have never been able to devise anything that allows this and it has always bugged me that there isn't any kind of _OnFocus event available for a control.

TIA

 

[RoryA]

BeforeUpdate and Exit events both allow you to cancel leaving the control.

 

[mikeymay]

Thanks @RoryA

I have implemented the _Exit event but I'm still having a problem.

When I enter a W/c date, checks are performed on the entry and if all the checks are good, the focus should move to the Date combobox. What is happening is that focus is moving to Van comboxbox.

Below is a screen shot of the 3 control where the issue is occuring

W/c requires a valid date format, a date > today and a Monday
Date requires the selection of a date from a list of 7 days commencing on the W/c date entered
Days requires a numeric entry for the number of days
Van requires a selection from the drop down list

The above controls are labelled as below
W/C - txtWC
Date - cbDate
Days - txtDays
Van - cbVan

The TabStop for each of the above controls are set to TRUE and their values start at 1 for txtWC, 2 for cbDate, 3 for txtDays and 4 for cbVan.

The code below deals with txtWC control

Private Sub txtWC_Change()

If boolExit = True Then
   Exit Sub
   Else
End If

boolExit = True

txtWC = Replace(txtWC, ".", "/")

boolExit = False

CheckClear

End Sub

Private Sub txtWc_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If boolExit = True Then
   Exit Sub
   Else
End If

boolExit = True

cbDate = ""
cbDate.Enabled = True

Cancel = True

If Replace(Trim(txtWC), "  ", " ") = "" Then
   dtRunWC = 0
   
   cbDate = ""
   cbDate.Enabled = False
   
   Cancel = False
   Else
   If IsDate(Replace(Trim(txtWC), "  ", " ")) Then
      dtRunWC = Replace(Trim(txtWC), "  ", " ")
      
      If dtRunWC < Date Then
         MsgBox ("The Valid From date cannot earlier than todays date"), vbExclamation, "ERROR"
                  
         cbDate = ""
         cbDate.Enabled = False
         
         txtWC = ""
         
         dtRunWC = 0
         
         txtWC.SetFocus
         Else
         If Weekday(dtRunWC, vbMonday) = 1 Then
            txtWC = Format(txtWC, "dd mmm yy")
            
            cbDate.SetFocus
            
            Cancel = False
            Else
            MsgBox ("The date entered is not a Monday"), vbExclamation, "ERROR"
            
            cbDate = ""
            cbDate.Enabled = False
            
            txtWC = ""
            
            dtRunWC = txtWC
                        
            txtWC.SetFocus
         End If
      End If
      Else
      MsgBox ("Enter a valid date format 'dd/mm/yy'"), vbExclamation, "ERROR"
      
      cbDate = ""
      cbDate.Enabled = False
            
      txtWC = ""
      
      dtRunWC = 0
            
      txtWC.SetFocus
   End If
End If

boolExit = False

End Sub

I also included an _Exit event for the cbDate control (I wanted to see if the control was being entered at all) which executes upon the txtWC code completing. So as soon as the End Sub line executes, the 'Private Sub cbDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)' starts.

This makes no sense as there are no keystrokes or code that should cause the control to move an extra step from cbDate.

To move to the next control I have used both the Enter and TAB keys after a date has been entered.

I am pretty much stumped wit this one and I'm hoping it's not a bug......


TIA

 

[RoryA]

I think your problem here is that the date control was disabled. That means that the tab/enter keypress should take you to the next control after that, and that movement was buffered before you enabled the date combo. So your setfocus works, but is then superseded by the original focus change to the next control. If at all possible, I would probably suggest enabling the date combo in your change event, then disabling it again in the exit event if the date is not valid. Also note that you do not need to setfocus back to the control whose exit event you are in. Only set Cancel = True if you want to stay in the control and that will handle it for you. You should not need to set Cancel = False.