MACRO to check validation dropdowns left blank

**Daimez** · 10-27-2008, 06:25 AM

Need your help guys,

I'm new at using macros - just learned a month ago.

Any suggestions on this one:

Got a table where I've placed Data validation - List on them.

Placed a macro on a button so that the data on the table where the validation list is placed is transfered to another sheet (same workbook).

What I need is a to modify the macro assigned to the button so that when the button is clicked to activate the macro:
a.) Can identify if there are any blanks on specified cells with validation list
b.) Stops the macro from copying the fields to the other sheet
c.) Alert the user of the blanks (an alert stating there is a blank would suffice)
d.) Alert should preferably be similar to a Stop alert from Data Validation.

Can anyone help? Been reading through several threads but don't seem to find anything that could help me - tried reading a Visual basic thread and now i've got a headache....

Whew...

Thanks

**XiaoWei** · 10-27-2008, 06:54 AM

Based on my limited VBA knowledge as well, the following procedure should work.

Sub IdentifyBlanksinDataValidation()
    On Error Resume NoBlank
    Cells.SpecialCells(xlCellTypeAllValidation).SpecialCells(xlCellTypeBlanks).Select
    MsgBox "There are blank cells...", vbCritical
    Exit Sub
NoBlank:
'   Your copying fields to other sheet statements go here...
End Sub

I think waiting for an expert's answer is better though.

**Daimez** · 10-27-2008, 07:05 AM

Thanks

Will try this tomorrow. After a few advils....

**Daimez** · 10-28-2008, 05:24 AM

Tried to use this but code won't work - Came back with error. Syntax Error.
The first line got higlighted in yellow and the 2nd line got font color red.

Any one please?

**royUK** · 10-28-2008, 06:58 AM

Try replacing

On Error Resume NoBlank

with

 On Error GoTo NoBlank

**XiaoWei** · 10-28-2008, 08:56 AM

Hi,

Thanks roy for pointing it out, I just realised that we can't use Resume label on same statement as On Error. But if we use GoTo label instead, the error condition will remain. Will it affect the subsequent statements if they ever get an error?

By the way, this statement will select the entire spreadsheet, then filter to those with data validation of any type, then filter to blanks.

Cells.SpecialCells(xlCellTypeAllValidation).SpecialCells(xlCellTypeBlanks).Select

The better statement is to refer to a single cell, then select the range with same data validation and then blanks. For e.g.

range("A1").SpecialCells(xlCellTypeSameValidation).SpecialCells(xlCellTypeBlanks).Select

**Daimez** · 10-28-2008, 09:07 AM

Thanks a lot guys.

I'll give a try tomorrow

**Daimez** · 10-29-2008, 09:36 AM

Hi again,

Tried the new code - this time no syntax error. Good.
However when I run the code, even if the range or cell with validation is no longer blank the Msgbox indicating there is a blank still pops out and the copying does not proceed.

I'd like to place the code here plus the copying code I recorded but don't know how to wrap the code.

Thanks

**royUK** · 10-29-2008, 10:02 AM

Just checked your code, try this instead

Option Explicit

Sub IdentifyBlanksinDataValidation()
    Dim msg    As String
    On Error GoTo NoBlank
    If Application.WorksheetFunction.CountBlank(Range("d6:e6")) > 0 Then
        msg = "CA NAME cannot be blank"
        GoTo isblank
    End If
    If IsEmpty(Range("d17")) Then
        msg = "Process Type cannot be blank"
        GoTo isblank
    End If
    If IsEmpty(Range("d18")) Then
        msg = "Sub Type cannot be blank"
        GoTo isblank
    End If
    If IsEmpty(Range("d20")) Then
        msg = "Product/Specific Type cannot be blank"
        GoTo isblank
    End If
    Range("S2:AF2").Copy
    Sheets("Raw_Data").ange("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                        :=False, Transpose:=False
    Application.CutCopyMode = False
    Rows("5:5").Insert Shift:=xlDown
    Sheets("Tracker").Range("D10:D11,D13:D15,D17:D20,B23:F32").ClearContents
       
isblank:
    MsgBox msg, vbCritical, "Input missing"
    Exit Sub
End Sub

**Daimez** · 10-29-2008, 09:40 AM

This is the code

Sub IdentifyBlanksinDataValidation()
    On Error GoTo NoBlank
    Range("d6:e6").SpecialCells(xlCellTypeSameValidation).SpecialCells(xlCellTypeBlanks).Select
    MsgBox "CA NAME cannot be blank", vbCritical
    Range("d17").SpecialCells(xlCellTypeSameValidation).SpecialCells(xlCellTypeBlanks).Select
    MsgBox "Process Type cannot be blank", vbCritical
    Range("d18").SpecialCells(xlCellTypeSameValidation).SpecialCells(xlCellTypeBlanks).Select
    MsgBox "Sub Type cannot be blank", vbCritical
    Range("d20").SpecialCells(xlCellTypeSameValidation).SpecialCells(xlCellTypeBlanks).Select
    MsgBox "Product/Specific Type cannot be blank", vbCritical
    Exit Sub
NoBlank:
'   Range("S2:AF2").Select
    Selection.Copy
    Sheets("Raw_Data").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("5:5").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Sheets("Tracker").Select
    Range("D10:D11").Select
    Selection.ClearContents
    Selection.ClearContents
    Range("D13:D15").Select
    Selection.ClearContents
    Range("D17:D20").Select
    Selection.ClearContents
    Range("B23:F32").Select
    Selection.ClearContents
End Sub

RoyUK - Thanks for the link for wrapping codes

**royUK** · 10-29-2008, 09:46 AM

Using code tags is simple. Selecyt the code that you post, then just click the button marked # above(this does not show in quick reply)

The code works fine, no message if there are no blank cells

**XiaoWei** · 10-29-2008, 10:27 AM

Hello again,

Your code has some mistakes. Having seen Roy's code, On Error statement should not be there because the error will not occur under normal circumstance. Here's the modified code of Roy's.

Sub IdentifyBlanksinDataValidation()
    Dim msg As String
    If Application.WorksheetFunction.CountBlank(Range("d6:e6")) > 0 Then
        msg = "CA NAME cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d17")) Then
        msg = "Process Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d18")) Then
        msg = "Sub Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d20")) Then
        msg = "Product/Specific Type cannot be blank": GoTo Blank
    End If

    Range("S2:AF2").Copy
    Sheets("Raw_Data").ange("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                        :=False, Transpose:=False
    Application.CutCopyMode = False
    Rows("5:5").Insert Shift:=xlDown
    Sheets("Tracker").Range("D10:D11,D13:D15,D17:D20,B23:F32").ClearContents

    Exit Sub
Blank:
    MsgBox msg, vbCritical, "Input missing"
End Sub

However, this will check only one at a time. So if there are blanks in D6:E6 and D20 as well, the message box will prompt with first message (i.e. CA Name cannot be blank) only.

**royUK** · 10-29-2008, 10:30 AM

I forgot to delete that on error from the original

**Daimez** · 10-29-2008, 11:13 PM

When there is a blank the code works exactly the way Xiao outlined it above and I quote " However, this will check only one at a time. So if there are blanks in D6:E6 and D20 as well, the message box will prompt with first message (i.e. CA Name cannot be blank) only."

However when all the specified ranges are not blank an error apppears

400

I see that the ranges set to be copied has been copied (highlighted). Can we assume the action or code stopped there then has an error.

Note: I tried using both codes: first by Roy's then Xiao's but has the same result.

Modified the code a bit to match the ranges. And changed the copying part of the code to simplify.

Sub IdentifyBlanksinDataValidation()
    Dim msg As String
    If Application.WorksheetFunction.CountBlank(Range("d6")) > 0 Then
        msg = "CA NAME cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d17")) Then
        msg = "Process Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d18")) Then
        msg = "Sub Type cannot be blank": GoTo Blank
    ElseIf IsEmpty(Range("d20")) Then
        msg = "Product/Specific Type cannot be blank": GoTo Blank
    End If

    Range("R2:AE2").Copy
    Sheets("Raw_Data").Select
    Range("A5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Rows("5:5").Insert Shift:=xlDown
    Sheets("Tracker").Range("D10:D11,D13:D15,D17:D20,B23:F32").ClearContents

    Exit Sub
Blank:
    MsgBox msg, vbCritical, "Input missing"
End Sub

**royUK** · 10-30-2008, 04:19 AM

Can you attach the workbook?

**Daimez** · 10-30-2008, 09:54 AM

Attached is the workbook

Thanks

**royUK** · 10-30-2008, 10:17 AM

The codes work if you put them in the right place. See the attached, you have placed this in the worksheet module.

Add the code to a Standard module

Also, remove all those empty modules.