Required Field Check > Based On Color Index

[ryancdavis]

Hello -

Thank you for any help in advance. I'm still trying to learn VBA and have been trying to figure this out for a while - and need some help.

I am trying to make certain fields required before the user can access the print button I created using the form tools on a specific worksheet. To reduce headaches moving forward, I was thinking about creating a macro that would check a specific worksheet based on a predefined "RGB ColorIndex" for required fields. I'll color all required fields this predefined color, which will require input prior to the user being able to access the print/submit button(s) I created within this specific sheet.

My thoughts are that this will help me later if changes are made on the worksheet (e.g. new fields added, reorganizing fields, or not requiring certain fields any longer) - so all I will have to do is ensure a required cell is colored correctly which would make it required automatically per the VBA code. This would save a lot of time, rather than defining an array of specific cells that will have to be manually updated every time a change is made to the worksheet.

Does anyone have any ideas on how to achieve this?

In summary, the logic in my mind is below. I just don't know how to put this into a working VBA script.

> Check range on worksheet named "X" for cells with specific color index.
> All cells that are equal to the color index predefined for this specific worksheet are required, so check these fields for input (not blank or empty).
> If cells with predefined color index are empty, then hide/disable the print button I created on the worksheet and notify user in message box which cells require input.
> Else, if some required fields have input, but not all required fields, then notify user in a message box stating the fields that require input.
> Else, if all required fields have input, then show/enable the print button.

I'm guessing it would be okay to put this under the Worksheet_Change sub, but feel that could cause the error message to popup every time a user begins filling out the blank worksheet with information - becoming annoying and inefficient. Is there a better worksheet specific sub to put this under?

Hopefully, this makes sense on what I am trying to achieve. Thank you community for all the help and assistance!

- RD

 

[shknbk2]

Here's something I came up with. It uses the Change event as you had mentioned, but instead of showing a messagebox with the missing values, it uses 2 colors to show good or bad entries. This way, the popup doesn't happen every time, but the user would have to know that the one certain color (e.g., yellow) needs to have a value before the button can be enabled.

There is also a variable called lookupRange that you can taylor to include all of the cells that could be colored. It's meant to be a subset of all the cells on the spreadsheet so that searching for the colored cells doesn't take forever.

Obviously, you'll have to change what I've hard coded to match the colors, lookupRange you need. Also, change the name of the CommandButton to whatever you have.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim coloredCells As Range, iSect As Range, showButton As Boolean
    Dim colorIndexEmpty As Integer, colorIndexNotEmpty As Integer
    
    'Find colored cells
    '   lookupRange is a range to search for colored cells
    '        This should be big enough to include the colored cells but
    '        not too big to take forever to find
    '   colorIndexEmpty is the color of the cell if empty
    '   colorIndexNotEmpty is the color of the cell if there is a value
    '
    '  if you need to determine the colorIndex values, select a cell that has
    '  the color you're looking for in the spreadsheet, then in the VBE Immediate window,
    '  type the following and then hit ENTER:
    '     ?activecell.Interior.Colorindex
    
    colorIndexEmpty = 6 'Yellow
    colorIndexNotEmpty = 4 ' Green
    Set coloredCells = ReturnColoredCells(Range("A1:E20"), colorIndexEmpty, colorIndexNotEmpty)
    
    If coloredCells Is Nothing Then
        Exit Sub
    End If
    
    'Check to see if changed cell is one of the colored cells
    Set iSect = Intersect(Target, coloredCells)
    
    If Not iSect Is Nothing Then
        showButton = True
        For Each iSect In coloredCells
            If iSect.Value <> "" Then
                iSect.Interior.ColorIndex = colorIndexNotEmpty
            Else
                showButton = False
                iSect.Interior.ColorIndex = colorIndexEmpty
            End If
        Next
        If showButton Then
            Me.CommandButton1.Enabled = True
        Else
            Me.CommandButton1.Enabled = False
        End If
    End If
End Sub


Function ReturnColoredCells(lookupRange As Range, colorIndexEmpty As Integer, colorIndexNotEmpty As Integer) As Range
    Dim c As Range, returnRange As Range
    
    For Each c In lookupRange
        If c.Interior.ColorIndex = colorIndexEmpty Or c.Interior.ColorIndex = colorIndexNotEmpty Then
            If returnRange Is Nothing Then
                Set returnRange = c
            Else
                Set returnRange = Union(returnRange, c)
            End If
        End If
    Next c
    Set ReturnColoredCells = returnRange
End Function

 

[mikerickson]

You could put this in the sheet's code module.
Adjust the range in RequriedFieldsFilled to include any possible cells that might be required.
Adjust the name of the button in the Change event.

' in sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Shapes("Button 1").Visible = RequiredFieldsFilled
End Sub

Public Function RequiredFieldsFilled() As Boolean
    Dim oneCell As Range
    RequiredFieldsFilled = True
    Application.Volatile
    
    For Each oneCell In Me.Range("A1:A100"): Rem adjust
    
        With oneCell
            If .Interior.Color = vbYellow Then
                RequiredFieldsFilled = RequiredFieldsFilled And (CStr(.Value) <> vbNullString)
            End If
        End With
    Next oneCell
End Function

Then put this code in the ThisWorkbook module, to prevent the user printing with methods other than your button.
(Adjust the name of the sheet as needed)

' in ThisWorkbook code module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Not Sheet1.RequiredFieldsFilled Then
        MsgBox "missing some data print canceled"
        Cancel = True
    End If
End Sub

 

[ryancdavis]

Hi shknbk2 and https://www.mrexcel.com/forum/members/mikerickson.htmlmikerickson -

Thank you both for the reply, and for taking the time to generate the coding solution for this issue. You both are awesome and I sincerely appreciate it. Also, I apologize for the delayed response. I just got back in town and have been catching up on emails.

I'll give these a try and reply back letting you know how they worked, or if I have any questions - if any arise. Thank you both again and have a great week. Talk soon.