VBA code must produce error with message if Excel is not completely filled

[N3rd]

Hello all

I am new here on the forum and would appreciate some help.

I am currently working on an excel file.

The VBA code must meet the following conditions:

• It must be filled in completely by the user.
• If this does not apply, the code should not allow the file to be saved.
• The Excel contains rolldown menus and text fields of numbers and letters.
• The rolldown menu must only be selectable without triggering another macro.
• Very important: the code must alert the user with a popup window where the input is missing. No matter if rolldown or normal text or number field.

At the moment the code complains if one field is not filled out by many. The rolldown menus are not considered yet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Application.WorksheetFunction.CountA(Range("B7, B9, B13, B27, B31, B32, B33, B37, B38, B40, B44, B49, B51, B53")) < 14 Then MsgBox "Please fill in missing information."

End Sub

Does anyone have any ideas? Would be very glad for help.

Wish you all a good evening.

N3rd

[jdc2000]

I assume that you would like the message to contain info on which cells (fields) are missing data. It would help to know what each of the cells that you listed is supposed to contain (field name or description). Also, if you are using any sort of data validation on any of those cells, that information would also be useful.

[N3rd]

Dear jdc2000,

Thanks for your quick reply. Your reasoning ist correct. I would like to know which cells are missing data.

There are the following cells:

The user can fill in or choose the following options:

B6 (rolldown menu with 5 options)
B7 (cell with numbers and letters)
B9 (only numbers)
B11 (rolldown menu with 3 options)
B13 (cell with numbers which must be in data format (xx.yy.zzzz))
B15 (rolldown menu with 2 options)
B17 (rolldown menu with 3 options)
B29 (rolldown menu with 80 options)
B31 (cell with letters)
B32 (cell with numbers and letters)
B33 (cell with numbers and letters)
B37 (cell with numbers and letters)
B38 (cell with numbers and letters)
B39 (cell with numbers and letters)
B40 (cell with numbers and letters)
B42 (cell with 7 options)
B44 (cell with numbers and letters)
B49 (cell with 3 options)
B51 (cell with numbers and letters)
B53 (cell with numbers and letters)
B55 (rolldown menu with 5 options)
B57 (rolldown menu with 1 option)
B58 (rolldown menu with 3 options)
B64 (rolldown menu with 4 options)
B68 (rolldown menu with 2 options)
B70 (rolldown menu with 3 options)
B72 (rolldown menu with 1 option)
B74 (rolldown menu with 3 options)
B76 (rolldown menu with 4 options)
B78 (rolldown menu with 2 options)
B80 (rolldown menu with 3 options)
B82 (rolldown menu with 3 options)
B84 (rolldown menu with 3 options)
B86 (rolldown menu with 3 options)

Is this helpful and am I answering your question?

Thanks again for your help.

Best regards,

N3rd

[westconn1]

can you post a workbook here (zip first) with some sample data

[N3rd]

sure.

[westconn1]

the selected items in the dropdowns are not values in the cells they cover, so have to be tested separately

this code will check all dropdowns on the worksheet for option selected, then all cells in column b with green interior for being empty

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim s As Shape, cel As Range
For Each s In Sheets("test").Shapes
    If s.OLEFormat.Object.ListIndex = 0 Then
            Cancel = True
            MsgBox "fisrt empty dropdown is " & s.TopLeftCell.Address
            s.TopLeftCell.Select
            Exit Sub
    End If
Next
For Each cel In Range("b:b")
    If cel.Row = 87 Then Exit Sub   ' change to suit
    If cel.Interior.ColorIndex = 14 Then
        If IsEmpty(cel) Then
            Cancel = True
            MsgBox "fisrt empty cell is " & cel.Offset(, 1).Address
            cel.Offset(, 1).Select
            Exit Sub
        End If
    End If
Next
End Sub

this does not check any validation or the format of the text in the cells, this could be done within the code, but is probably better done by cell validation, a list of cells could be used instead of using interior colour as criteria
the message boxes could be removed as the first offending cell should be selected

note the dropdowns are not in order down the worksheet, but they could be changed to be in order, also some of the dropdows also appear to be located in the cell above where they appear, so could be moved slightly , or the cell above them may be selected, which may not matter at all

this is partially tested, but should have more thorough work out