Change userform tab order based on combobox entries.

[jasonb75]

Hi all

I think I may be looking for something that doesn't exist but wonder if anyone has some alternative ideas that might work.

The theory that I'm looking at is a userform with around 8 comboboxes and a button at the end to save, with a double tap of the enter key activating the save routine (I'm sure that's how I did it last time).

The twist that I need to add in is that I want the tab order to skip the button if any of the comboboxes are left blank. I don't want the button to be completely disabled though, the user needs to be able to override if the blanks are intentional.

Not sure if I'm missing something simple or if I'm trying to do the impossible, this is only the third time I've set up a form since joining the forum and my memory on the last attempt is more than a little rusty.

Thanks in advance for any suggestions.

 

[Artik]

Instead of combining with skipping the button, let the button be pressed. In the button procedure, check that all fields are filled in. Only when they are all filled let the rest of the code execute.

Private Sub CommandButton1_Click()
    Dim ctl As MSForms.Control
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            If Len(ctl.Value) = 0 Then
                ctl.SetFocus
                Exit Sub
            End If
        End If
    Next ctl
    
    Stop
    'Your code after verification of controls
End Sub

Artik

 

[jasonb75]

Thanks, @Artik but that's not going to work. As mentioned in my original post the user still needs to be able to override by intentionally clicking the button.

For 99% of entries all boxes will be filled so I want to set it up for quick entry, for the exceptions the user needs to be able to click the button to save with 1 (or more) empty boxes.

What I'm looking for is a way to be certain that the empty box is intentional and that the user hasn't missed one by accident.

My thoughts before posting the question were to use a check method like yours and have the user enter N/A in the empty boxes before they can continue.

I've got a couple of other crude workaround methods in mind that I'm not too sure about. It's likely going to be a case of trying a few things when I've got the rest set up to see which, if any, work as needed.

 

[myall_blues]

I don't think what you specifically want is possible. An alternative I have used before is basically the same as @Artik suggestion but if it encounters a field left blank throw up a message box that allows the user the option to either go back and fill it in, or continue and accept it being blank.

 

[Artik]

Sorry, it was already dark and I didn't notice that you need the ability to accept despite some deficiencies in ComoBoxes.
In that case, try it this way. Program only the last (last in Tab Order) ComboBox control, e.g.ComboBox8:

Private Sub ComboBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Call Check
End Sub

Sub Check()
    Dim ctl As MSForms.Control
    
    CommandButton1.TabStop = True
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            If Len(ctl.Value) = 0 Then
                CommandButton1.TabStop = False
                Exit Sub
            End If
        End If
    Next ctl
    
End Sub

Artik

 

[jasonb75]

throw up a message box that allows the user the option

Thanks for this, that was one of the workaround methods that I had in mind but wasn't sure if it could be done with the form open.

Looking at @Artik's suggestion in the post after yours, I think that might be the ideal solution for what I'm trying to do but I'm keeping your suggestion in mind as backup.

@Artik, thank you very much for the follow up suggestion. I believe that what you propose is going to do exactly what I need. I'm still in very early stages of a spare time project that I'm doing to help a friend out so it may be a few days, or even weeks before anything is up and running but I'll pop back and give an update as soon as I've had the chance to test the theory.

Thank you both very much for the suggestions.

Jason