Macro to identify if required fields are filled in

[tonywatsonhelp]

Hi Everyone
I have an input sheet layed out like a form,

The range is D24:V74

No its all laid out the same with headers and input cell below the headers

If possible, what I like is to Identify the required field with a "*" after the header so "Name*" for example.
then have a macro chedck if every required cell has been filled in

if any are missing bring up a msgbox box saying "The following fields must be completed before proceeding" and list the Header after each other,

so if 3 headers where missing message box would say

"The following fields must be completed before proceeding"
"Name*"
"Email*"
"Age*"
for example.

please help if you can.

thanks


Tony

 

[BSALV]

"The following fields must be completed before proceeding"
"Name*"
"Email*"
"Age*"

where are those (and others cells with "*") in your range D24:V74 and the cell, that contains the data, is that the cell at the right of that cell ?

 

[dmt32]

Hi,
Have a play with following & see if goes in right direction

Sub Tony()

    Dim Cell    As Range
    Dim msg     As String
   
    Const PromptText As String = "The following field(s) must be completed before proceeding"
   
    For Each Cell In Range("D24:V74")
        With Cell
            If Right(.Value, 1) = "*" And Len(.Offset(1).Value) = 0 Then
                msg = msg & Mid(.Value, 1, Len(.Value) - 1) & Chr(10)
            End If
        End With
    Next Cell
   
    If Len(msg) > 0 Then
        MsgBox PromptText & Chr(10) & Chr(10) & msg, 48, "Entry Required"
        Exit Sub

    Else
   
   
   
    'rest of code
   
    End If
End Sub

Dave

 

[BSALV]

make a defined name "Necessary_Cells" with all the cells that need to be filled and a macro
the reference of that defined name is for example (with ; in my dutch version or , in other versions as separator

=Blad1!$D$7;Blad1!$K$17;Blad1!$H$25;Blad1!$E$32;Blad1!$F$37;Blad1!$J$40;Blad1!$M$41;Blad1!$N$33;Blad1!$N$24

Sub Check_Cells()
     With Range("Necessary_Cells")
          Delta = .Count - WorksheetFunction.CountA(.Offset(0))
          If Delta <> 0 Then
               Set c = .Offset(0).SpecialCells(xlCellTypeBlanks)
               MsgBox "Number of empty cells : " & Delta & vbLf & "address : " & c.Address
          End If
     End With
End Sub