Find Empty Cells in Excel and Prompt User with MsgBox with Macro

  • Hi, I have a Excel Form on which I need to run a Macro to check some key cells and if the cells are empty, I want to inform the user which cell is empty and then have the macro go to the empty cell.


    There may be multiple empty cells in the form


    I have setup the following macro, but it doesn't seem to work. It only finds the first empty cell and then ignores any other empty cells.


    Also not sure how to get the macro to return the user to each empty cell for required actions
    I have added the macro I have put together below. Any assistance with this would be great.



    Regards
    Lance

  • Re: Find Empty Cells in Excel and Prompt User with MsgBox with Macro


    Hello Lance,


    Welcome to Ozgrid.
    While we're glad to have you on-board, please take the time to read the Forum Rules.


    All VBA code posted in the forum must be wrapped in code tags which you omitted. I've added the tags for you this time only. Be sure to use them in future posts.


    How to use code tags


    [noparse]

    Code
    [/noparse]
    [COLOR=navy]your code goes between these tags[/COLOR]
    [noparse]

    [/noparse]
    ------------------------------------------------------------


    Remove this line of code from all if the IF . . . ElseIf bits


    Code
    Exit Sub
    Quote

    Also not sure how to get the macro to return the user to each empty cell for required actions

    You could use:

    Code
    Range("J58").Select

    My preference:

    Code
    Application.GoTo reference:=Sheet1.Range("J58"), scroll:=True


    -------------------------------------------------------------


    I think that many message boxes will get quite annoying, you might consider using this approach, which groups sections:


    Also, consider shading all cells where entries are requires and use Conditional Formatting to un-shade them when the entry is made. This will provide a nice visual cue to the user and make it easy to spot the missing data.

  • Re: Find Empty Cells in Excel and Prompt User with MsgBox with Macro


    Thanks for the feedback and all good now. Also have taken note of the rules when using code in threads.


    Regards
    Lance

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!