Data Validation in UserForm base on worksheet cell value

[mmaeng13]

I am attempting to add data validation to a UserForm with textboxes. I need a way to have the userform alert the person entering data that an entry is out of range. This is for running hours of an engine and is entered daily, so 24 or less hours. I would also like to add in an alert for negative hours. This Userform has a number of textboxes so I would prefer just highlighting the specific textbox instead of a pop up message.

My current code, validates then enables Commandbutton1 to enter the data from the userform. Range("B17").Value is the worksheet cell with the previous days entry.

Private Sub Validate_Click()

ValidateDataDisplayLabel


End Sub


Sub ValidateDataDisplayLabel()


    If PMEHours.Value - Range("B17").Value > 24 Then
        PMEHours.BackColor = &HFF&
    End If
    
CommandButton1.Enable


End Sub

 

[Fluff]

How about

Private Sub Validate_Click()
   Dim AllOk As Boolean
   
   AllOk = True
   If PMEHours.Value - Range("B17").Value > 24 Or PMEHours.Value < 0 Then
      PMEHours.BackColor = &HFF&
      AllOk = False
   Else
      PMEHours.BackColor = vbWhite
      AllOk = True
   End If
   If AllOk Then CommandButton1.Enabled = True
End Sub

 

[mmaeng13]

How about

Private Sub Validate_Click()
   Dim AllOk As Boolean
   
   AllOk = True
   If PMEHours.Value - Range("B17").Value > 24 Or PMEHours.Value < 0 Then
      PMEHours.BackColor = &HFF&
      AllOk = False
   Else
      PMEHours.BackColor = vbWhite
      AllOk = True
   End If
   If AllOk Then CommandButton1.Enabled = True
End Sub

Is there anyway to repeat this within this specific Sub for multiple engines and only enable the command button if all instances are True?

 

[Fluff]

Yes, simply repeat the part in blue like this

Private Sub Validate_Click()
   Dim AllOk As Boolean

   AllOk = True
   
  [COLOR=#0000ff] If PMEHours.Value - Range("B17").Value > 24 Or PMEHours.Value < 0 Then
      PMEHours.BackColor = &HFF&
      AllOk = False
   Else
      PMEHours.BackColor = vbWhite
      AllOk = True
   End If[/COLOR]
   If [COLOR=#ff0000]PMEHours[/COLOR].Value - Range("B17").Value > 24 Or [COLOR=#ff0000]PMEHours[/COLOR].Value < 0 Then
      [COLOR=#ff0000]PMEHours[/COLOR].BackColor = &HFF&
      AllOk = False
   Else
      [COLOR=#ff0000]PMEHours[/COLOR].BackColor = vbWhite
      AllOk = True
   End If

   If AllOk Then CommandButton1.Enabled = True
End Sub

Changing the values in red to suit

 

[mmaeng13]

Awesome. Thank you, you have helped me with a couple issues with this on going project.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Fluff]

Scrub what I said in post#4, I've given you some duff info.
Use this instead

Private Sub Validate_Click()
   Dim AllOk As Long

   If tb1.Value - Range("B17").Value > 24 Or tb1.Value < 0 Then
      tb1.BackColor = &HFF&
      AllOk = 0
   Else
      tb1.BackColor = vbWhite
      AllOk = AllOk + 1
   End If
   
   If tb2.Value - Range("B17").Value > 24 Or tb2.Value < 0 Then
      tb2.BackColor = &HFF&
      AllOk = 0
   Else
      tb2.BackColor = vbWhite
      AllOk = AllOk + 1
   End If

   If AllOk = [COLOR=#ff0000]2 [/COLOR]Then CommandButton1.Enabled = True
End Sub

Change the value of 2 in red to then number of txtboxes you're checking

 

[mmaeng13]

Will do, thanks