Set focus on the textbox after the message.

[danbates]

Hi,

I have the following code that triggers a message if one of the text boxes hasn't been entered.

If ((Me.TextBox2.Text = "") * (Me.TextBox3.Text = "") * (Me.TextBox4.Text = "") * (Me.TextBox5.Text = "") * (Me.TextBox6.Text = "") * (Me.TextBox7.Text = "") * (Me.TextBox8.Text = "") * (Me.TextBox9.Text = "")) Then
MsgBox "QTY not entered!", vbInformation, "Milk Room Operator"
    Exit Sub
    End If

What I would like is if the message is triggered, the message is ok'd then that textbox becomes active again with the curser flashing.

Any help would be much appreciated.

Thanks
Dan

 

[dmt32]

Hi,

try

Dim i As Integer
    For i = 2 To 9
    With Me.Controls("TextBox" & i)
        If Len(.Text) = 0 Then
            .SetFocus
            MsgBox "QTY not entered!", 64, "Entry Required"
            Exit Sub
        End If
    End With
    Next i

Dave

 

[danbates]

Hi Dave,

Thanks for you reply.

It comes up with following error and its my fault for not specifying at the start.

Run time error 2110
Cant move focus to the control because it is invisible, not enabled or of a type that does not accept focus.

The other textboxes become invisible when the checkbox is selected but the textbox I want to become the focus is visible.

HTH

Thanks
Dan

 

[dmt32]

Hi,

it's always helpful to share such information when asking for help

try this update

Dim i As Integer
    For i = 2 To 9
    With Me.Controls("TextBox" & i)
        If .Visible And Len(.Text) = 0 Then
            .SetFocus
            MsgBox "QTY not entered!", 64, "Entry Required"
            Exit Sub
        End If
    End With
    Next i

Dave

 

[danbates]

Hi Dave,

Thanks again but unfortunately it doesn't setfocus the visible textbox.

Regards

Dan

 

[dmt32]

Hi Dave,

Thanks again but unfortunately it doesn't setfocus the visible textbox.

Regards

Dan

Does for me - can you post ALL the code you have.

Dave

 

[danbates]

Option ExplicitDim lrcd As Long
Dim hWnd As Long
Dim InputError As Boolean


Private Sub CommandButton1_Click()
Dim i As Long
Dim FirstFull As Long
Dim x As Integer
    
    For x = 2 To 9
    With Me.Controls("TextBox" & x)
        If .Visible And Len(.Text) = 0 Then
            .SetFocus
            MsgBox "QTY not entered!", 64, "Entry Required"
            Exit Sub
        End If
    End With
    Next x




For i = 2 To 9
    With Me.Controls("TextBox" & i)
        If .Text <> vbNullString Then
            If FirstFull <> 0 Then
                .Text = vbNullString
                FirstFull = -1 * Abs(FirstFull)
            Else
                FirstFull = i
            End If
        End If
    End With
Next i


If FirstFull < 0 Then
    Me.Controls("TextBox" & Abs(FirstFull)).Text = vbNullString
    MsgBox "Only fill out one QTY", vbInformation, "Milk Room Operator"
End If




  'Disable Excel Events
  Application.EnableEvents = False
  
  'Unprotect the Sheet
  Call UnprotectTheActiveSheet


lrcd = Sheets("LINE_11").Range("F" & Rows.Count).End(xlUp).Row
Sheets("LINE_11").Cells(lrcd + 1, "F").Select


If CheckBoxCHOCO.Value = True Then Cells(lrcd + 1, "B").Value = 43674720
If CheckBoxCHOCO.Value = True Then Cells(lrcd + 1, "C").Value = "Chocolate Milk"
If CheckBoxFREDDO.Value = True Then Cells(lrcd + 1, "B").Value = 43401235
If CheckBoxFREDDO.Value = True Then Cells(lrcd + 1, "C").Value = "Freddo Milk"
If CheckBoxICEDCAPP.Value = True Then Cells(lrcd + 1, "B").Value = 43676860
If CheckBoxICEDCAPP.Value = True Then Cells(lrcd + 1, "C").Value = "Iced Capp Milk"
If CheckBoxMOCHA.Value = True Then Cells(lrcd + 1, "B").Value = 43401158
If CheckBoxMOCHA.Value = True Then Cells(lrcd + 1, "C").Value = "Mocha Milk"

'THESE CODES BELOW ARE FOR FUTURE ADDITIONS

If CheckBox1.Value = True Then Cells(lrcd + 1, "B").Value = 0
If CheckBox1.Value = True Then Cells(lrcd + 1, "C").Value = "Milk"
If CheckBox2.Value = True Then Cells(lrcd + 1, "B").Value = 0
If CheckBox2.Value = True Then Cells(lrcd + 1, "C").Value = "Milk"
If CheckBox3.Value = True Then Cells(lrcd + 1, "B").Value = 0
If CheckBox3.Value = True Then Cells(lrcd + 1, "C").Value = "Milk"
If CheckBox4.Value = True Then Cells(lrcd + 1, "B").Value = 0
If CheckBox4.Value = True Then Cells(lrcd + 1, "C").Value = "Milk"


'CHOCOLATE MILK
'FREDDO MILK
'CAFÉ AU LAIT
'ICED CAPP MILK
'LATTE MILK
'MOCHA MILK


Sheets("LINE_11").Cells(lrcd + 1, "E").Value = TextBoxSSCC.Text


If TextBoxPO.Value <> "" Then Cells(lrcd + 1, "A").Value = TextBoxPO.Text
If TextBox2.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox2.Text
If TextBox3.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox3.Text
If TextBox4.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox4.Text
If TextBox5.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox5.Text

'THESE CODES BELOW ARE FOR FUTURE ADDITIONS

 If TextBox6.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox6.Text
If TextBox7.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox7.Text
If TextBox8.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox8.Text
If TextBox9.Value <> "" Then Cells(lrcd + 1, "D").Value = TextBox9.Text


If Len(TextBoxPO.Value) < 8 Then
        MsgBox "Process Order number is too short", vbInformation, "Milk Room Operator"
        TextBoxPO.SetFocus
    Exit Sub
    End If
    
If Len(TextBoxSSCC.Value) < 18 Then
        MsgBox "SSCC number is too short", vbInformation, "Milk Room Operator"
        TextBoxSSCC.SetFocus
    Exit Sub
    End If




If ((Me.CheckBoxCHOCO.Value = 0) * (Me.CheckBoxFREDDO.Value = 0) * (Me.CheckBoxICEDCAPP.Value = 0) _
    * (Me.CheckBoxMOCHA.Value = 0) * (Me.CheckBox1.Value = 0) * (Me.CheckBox2.Value = 0) _
    * (Me.CheckBox3.Value = 0) * (Me.CheckBox4.Value = 0)) Then
    MsgBox "Please select the Material you are scanning in!", vbInformation, "Milk Room Operator"
    Exit Sub
    End If


'If ((Me.TextBox2.Text = "") * (Me.TextBox3.Text = "") * (Me.TextBox4.Text = "") _
'    * (Me.TextBox5.Text = "") * (Me.TextBox6.Text = "") * (Me.TextBox7.Text = "") _
'    * (Me.TextBox8.Text = "") * (Me.TextBox9.Text = "")) Then
'    MsgBox "QTY not entered!", vbInformation, "Milk Room Operator"
'
'    Exit Sub
'    End If




Dim ctrl As Control
Dim msg As String


With Me
    For Each ctrl In .Controls


        Select Case TypeName(ctrl)


            Case "TextBox"
                If ctrl.Text = "" Then msg = msg & vbCrLf & "TextBox '" & ctrl.Name & "' with no value selected"
            Case Else


        End Select


    Next ctrl


  End With




ScanINForm.Show


  'Protect the Sheet again
  Call ProtectTheActiveSheet
  
    'Enable Excel Events
  Application.EnableEvents = True


End Sub

Thanks
Dan

 

[dmt32]

Hi,
solution provided loops through textboxes 2 to 9 & if visible & empty, displays msgbox & sets focus on the empty textbox exiting the procedure when msgbox dismissed.

Is this what you expect the code to do? Or do you want to code to set focus on one particular textbox regardless of which textbox is empty?

Dave

 

[danbates]

Hi Dave,

Your description of your code is what I would like to happen but it's just not set focusing on the visible and empty textbox after I ok the message.

Thanks
Dan

 

[dmt32]

I created 9 textboxes on a form & set some to be hidden - Entered data & left some blank. Code correctly found first visible textbox that was empty, displayed msgbox with the focus set to the textbox.

I am a little puzzled why at this is not the same for you - are you able to place a copy of your workbook in dropbox & provide a link to it here?

Dave