Hi,
I have created a userform to add information to a table.
I have some conditional formatting applied to column R that highlights all rows red that meet the specific criteria. See below formatting:
=SUMIF($A$2:$A$531,$A1,$R$2:$R$531)>2272
Is there any way I can apply this to my userform so that when I try to submit the information it will display an error message and not input the data in the table? I will include a copy of my code for the userform below if that will be of any help.
Cheers!
I have created a userform to add information to a table.
I have some conditional formatting applied to column R that highlights all rows red that meet the specific criteria. See below formatting:
=SUMIF($A$2:$A$531,$A1,$R$2:$R$531)>2272
Is there any way I can apply this to my userform so that when I try to submit the information it will display an error message and not input the data in the table? I will include a copy of my code for the userform below if that will be of any help.
Cheers!
VBA Code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Table")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.cboWeek.Value) = "" Then
Me.cboWeek.SetFocus
MsgBox "Please select the Week Number"
Exit Sub
End If
If Trim(Me.txtOrder.Value) = "" Then
Me.txtOrder.SetFocus
MsgBox "Please enter the Order Number"
Exit Sub
End If
If Trim(Me.txtValue.Value) = "" Then
Me.txtValue.SetFocus
MsgBox "Please enter the Order Value"
Exit Sub
End If
If Trim(Me.txtType.Value) = "" Then
Me.txtType.SetFocus
MsgBox "Please enter the Part Number"
Exit Sub
End If
If Trim(Me.cboCategory.Value) = "" Then
Me.cboCategory.SetFocus
MsgBox "Please select a Category"
Exit Sub
End If
If Trim(Me.txtQty.Value) = "" Then
Me.txtQty.SetFocus
MsgBox "Please enter the Quantity "
Exit Sub
End If
If MsgBox("Are you sure the information you have entered is correct?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.cboWeek.Value
.Cells(iRow, 2).Value = Me.txtOrder.Value
.Cells(iRow, 3).Value = Me.txtValue.Value
.Cells(iRow, 4).Value = Me.txtType.Value
.Cells(iRow, 5).Value = IIf(Me.txtReference.Value = "", "N/A", Me.txtReference.Value)
.Cells(iRow, 6).Value = Me.cboCategory.Value
.Cells(iRow, 7).Value = Me.txtQty.Value
' .Protect Password:="password"
End With
'clear the data
Me.cboWeek.Value = ""
Me.txtOrder.Value = ""
Me.txtValue.Value = ""
Me.txtType.Value = ""
Me.txtReference.Value = ""
Me.cboCategory.Value = ""
Me.txtQty.Value = ""
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub LabelWeek_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If
End Sub
Last edited by a moderator: