Applying conditional formatting to a userform and displaying an error message

lfellows

New Member
Joined
Nov 22, 2019
Messages
35
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!

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:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Appears you are checking for missing data and displaying a message box. Are you also wanting additional coloring of the data input object(s)?

Try <control>.BackColor = RGB(255, 0, 0)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,785
Messages
5,626,869
Members
416,208
Latest member
tan21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top