You are here:

Excel/Excel VBA, Color Mandatory cll if there is no entry and show message box

Advertisement


Question
QUESTION: Hi Tom,

I have a user form that has three different sheets.  Each sheet has cells that are mandatory to populate.   I would like to add to the “Beforesave” VBA code I have below to have these cells highlighted the color “Red” and a message appear stating “All mandatory  fields need to be populated before saving.  Please review all sheets in file and fill in the missing fields highlighted red”.  What code do I need to add to the last part of the coding below to check multiple cells in different sheets and highlight the cells that are blank, then show a message box to correct these missing data points before saving?

****Current Code****

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Sub Run_macros_click(#
UpdateDIP
a = MsgBox#"Remember to save file as MACRO ENABLED in the Save as type under the file name.  If you already have saved this Capex request file as a Macro Enabled, disregard this message.", vbOKno)
   If a = vbNo Then Cancel = True
  'If the six specified cells do not contain data, then display a message box with an error
  'and cancel the attempt to save.
  If WorksheetFunction.CountA(Worksheets("capex").Range("funnel4castid")) < 1 Then
         MsgBox "Form will not be saved unless" & vbCrLf & _
     "Funnel4Cast ID Number is filled in!"
        Cancel = True
  End If
End Sub



I have found coding at the below link that someone else posted.  It appears to be what I am looking for, but I cannot seem to get it to work in my file.  I am not sure what I need to change.
http://stackoverflow.com/questions/13529234/excel-vba-colour-mandatory-field-cel


Any help is appreciated. As always, Thank you for your time.

ANSWER: Will V R

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Sub Run_macros_click(#
Dim bProblems as Boolean
Dim r as Range
UpdateDIP
a = MsgBox#"Remember to save file as MACRO ENABLED in the Save as type under the file name.  If you already have saved this Capex request file as a Macro Enabled, disregard this message.", vbOKno)
If a = vbNo Then Cancel = True
 'If the six specified cells do not contain data, then display a message box with an error
 'and cancel the attempt to save.

v = Array("Sheet1!A2","Sheet1!B9","Sheet2!B9","Sheet2!F11","Sheet3!A1","Sheet3!B5")
for i = lbound(v) to Ubound(v)
 set r = Evaluate(v(i))
 if len(trim(r.value)) = 0 then
    bProblems = True
    r.Interior.ColorIndex = 3
 end if
Next   
if bProblems then
    Cancel = True
        MsgBox "Form will not be saved unless" & vbCrLf & _
    "ID Number is filled in on Sheet1, Sheet2 and Sheet3!"
End If
End Sub

change the sheets/ranges in the v = statement to reflect the cells you want checked.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom,

Thanks for the reply.  I have taken the code you provided and entered into my file and changed the ranges.  I did rearrange the events as shown in the new code below.  It appears all is working well accept for the line that states r.Interior.ColorIndex = 3.  It gets hung up on this and spits back a Run-Time error '1004' after checking the first cell/range listed.  When this cell is blank the code does highlight red, but it doesn't check the other cells/ranges listed prior to the error.  Any suggestions on how I can correct this?  In additional, is there a way to return the cell color to the original yellow (RGB 255, 255, 153) once the data is populated rather than keepping them red if the data isn't filled in? Perhaps this code does do that once I correct my issue mentioned above.

****New Code****
Private Sub Workbook_BeforeSave#ByVal SaveAsUI As Boolean, Cancel As Boolean#
'Sub Run_macros_click##
UpdateDIP
'If the four specified cells do not contain data, then display a message box with an error
'and cancel the attempt to save#
v = Array#"Capex!funnel4castid", "Capex!R12", "Coversheet!Project_Manager", "Valuation!Ec_life")
For i = LBound(v) To UBound(v)
Set r = Evaluate(v(i))
If Len(Trim(r.Value)) = 0 Then
   bProblems = True
   r.Interior.ColorIndex = 3
End If
Next
If bProblems Then
   Cancel = True
       MsgBox "Form will not be saved unless all mandatory fields are populated." & vbCrLf & _
"Please review all sheets in file and fill in the missing fields highlighted red"
a = MsgBox("Remember to save file as **Excel MACRO-ENABLED Workbook** in the Save as type under the file name in the next window.  If you have already saved this Capex request file as a Macro Enabled, disregard this message.", vbOKno)
If a = vbNo Then Cancel = True
End If
End Sub

Answer
Will VR,

v = Array#"Capex!funnel4castid", "Capex!R12", "Coversheet!Project_Manager", "Valuation!Ec_life")
For i = LBound(v) To UBound(v)
Set r = Evaluate(v(i))
If Len(Trim(r.Value)) = 0 Then
  bProblems = True
  r.Interior.ColorIndex = 3
End If
Next


r depends on the data you put in the array V.  If it evaluates to a range and the range is unprotected.

r.Interior.ColorIndex = 3
should have no problem

I can't test if  your entries in the array are evaluated to valid range references.

You need to extract that part of the code, i.e. duplicate it in a test procedure and test it and see what works and what doesn't.  (either that or put put in some debugging statements or use excel's built in debugger - I don't know what approach you use to debug your code).   

If there are only 4 cells you need to evaluate you could just write code to check them independently if you want.

As far as turning them yellow, you asked how to turn them red.  You could modify my code to turn them yellow


For i = LBound(v) To UBound(v)
Set r = Evaluate(v(i))
If Len(Trim(r.Value)) = 0 Then
  bProblems = True
  r.Interior.ColorIndex = 3  'red
else
  r.Interior.colorIndex = 6  'Yellow
End If
Next


--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.