Required Field Check > Based On Color Index

ryancdavis

New Member
Joined
Jun 29, 2017
Messages
8
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

Code:
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
 
Last edited:
Upvote 0
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.

Code:
' 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)

Code:
' 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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top