Share via

Make filling a cell mandatory before saving

Anonymous
2022-02-10T22:29:31+00:00

Hi!

I have an Excel form with many cells (11) needing to be filled in before saving; cells cannot be left blank.

So, do I need only one macro that lists all the cells that need to be filled, and that uses a common message pop-up (i.e. 'This cell must be completed'), or do I need a separate macro for each cell that needs to be filled in, with a cell specific Message (i.e. 'Name of Patient must be filled')?

I would prefer a single macro if possible, and, if possible, the script needed. Do you know how to do this?

Thanks

Bruce

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2022-02-10T22:47:22+00:00

    You could place a command button (Form Control) on the worksheet, and assign the following macro to the command button:

    Sub CheckCells()
    Dim c
    Dim m
    Dim i As Long
    c = Array("A2", "A4", "A6", "C2", "C4", "C6", "E2", "E4", "E6", "G2", "G4")
    m = Array("Message for A2", "Message for A4", "Message for A6", _
              "Message for B2", "Message for B4", "Message for B6", _
              "Message for C2", "Message for C4", "Message for C6", _
              "Message for G2", "Message for G4")
    For i = LBound(c) To UBound(c)
        If Range(c(i)).Value = "" Then
            Range(c(i)).Select
            MsgBox m(i), vbInformation
            Exit Sub
        End If
    Next i
    MsgBox "All checks have been passed!", vbInformation
End Sub

    Modify the two arrays as needed.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points
    2022-02-11T15:40:32+00:00

    Let's get clear what you mean by "form" first - is it a range on a worksheet, or did you mean a userform created in the Visual Basic Editor?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-02-11T15:11:43+00:00

    OK .. now I'm lost.

    Why would I want to click the button?

    Do I click the button after filling in the form to check that all cells have been filled-in? Is the script written insisting the button be clicked (check the form) before the form can be saved or printed? And, if the button finds an empty cell, does it give that cell focus; and the next one after that if more than one cell is blank?

    Does the script you wrote allow for the form to be printed or saved without clicking the button? Or is clicking the button mandatory before any other action (i.e. printing, saving, et al) can be completed?

    Also, the button could then be placed prominently in the form proper and named 'Check the Form', and its properties set so that it does not print the button. Correct?

    Sorry for my being so dense ... but in the area I am at least that.

    Bruce

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2022-02-11T14:28:08+00:00

    You can place it anywhere on the same sheet. If you would prefer to place it on another sheet, the code would have to be adjusted slightly.

    But if you cover it up, you won't be able to click it...

    Was this answer helpful?

    0 comments
  5. Anonymous
    2022-02-11T14:14:01+00:00

    Wow ... and 'Thanks!', Hans V

    You write, "... place a command button (Form Control) on the worksheet." Does this command button need to be located within the actual form where it can be seen (but not needed!), or can I place it anywhere on the sheet even outside of the print area where it cannot be seen or printed, even covered up with a blank object?

    Bruce

    Was this answer helpful?

    0 comments