Search code examples
excelvbatextboxuserform

Double message display - VBA


In the following code, an error message is displayed when a value is entered incorrectly. Error message is displayed again when deleting the wrong value due to the field being empty. How can I stop the display of the message again?

Private Sub textbox1_Change()
   If TextBox1 < 1 Or TextBox1 > 31 Then
   Beep
   MsgBox "Please enter numbers between 1 and 31."
   TextBox1= ""
   TextBox1.SetFocus
   End If
End Sub

Solution

  • Private Sub textbox1_Change()
        Select Case True
            Case Len(TextBox1.Text) = 0
    
            Case TextBox1.Value < 1 Or TextBox1.Value > 31
                Beep
                MsgBox "Please enter numbers between 1 and 31."
                TextBox1 = ""
                TextBox1.SetFocus
    
        End Select
    End Sub
    

    or

    Private Sub textbox1_Change()
        If Len(TextBox1.Text) = 0 Then Exit Sub
    
        If TextBox1 < 1 Or TextBox1 > 31 Then
            Beep
            MsgBox "Please enter numbers between 1 and 31."
            TextBox1 = ""
            TextBox1.SetFocus
        End If
    End Sub