When Saving or Switching Worksheet, Check if One Column is Empty if Adjacent Cell Has Data

[DaveWW00]

Been playing around in VBA trying to get this to work and just am not having much luck with my basic skills. Basically what i am trying to do is when the user leaves this worksheet to go to another sheet, save, or close there will be a check to make sure if they entered something in column G but not column H for that row, then it will stop them from leaving and popup message telling them to enter it. For example, it needs to check IF G4 isnt Blank but H4 is, the error would popup. However it needs to do this for all rows between G4 and G20000.

This is to make sure a due date is entered when a new sample is entered in this sheet. I've read around and copied and played with some code to try and do this but havent had much luck. Any help would be greatly appreciated. Thanks.

 

[JoeMo]

Been playing around in VBA trying to get this to work and just am not having much luck with my basic skills. Basically what i am trying to do is when the user leaves this worksheet to go to another sheet, save, or close there will be a check to make sure if they entered something in column G but not column H for that row, then it will stop them from leaving and popup message telling them to enter it. For example, it needs to check IF G4 isnt Blank but H4 is, the error would popup. However it needs to do this for all rows between G4 and G20000.

This is to make sure a due date is entered when a new sample is entered in this sheet. I've read around and copied and played with some code to try and do this but havent had much luck. Any help would be greatly appreciated. Thanks.

Here's an example of sheet event code that will prompt the user who has not filled all col H cells that have companion col G cells filled when the user attempts to leave the sheet. Install it as sheet code not as a standard module. If it's satisfactory you can adapt it to Thisworkbook event modules for before save and/or workbook close events.

Private Sub Worksheet_Deactivate()
If Application.CountA(Range("G4:G20000")) > Application.CountA(Range("H4:H20000")) Then
    MsgBox "You must fill all column H cells that have filled column G companion cells on sheet: " & Me.Name
    Me.Activate
End If
End Sub

 

[DaveWW00]

Thanks so much! Works perfectly.

 

[JoeMo]

Thanks so much! Works perfectly.

You are welcome - thanks for the reply.

 

[DaveWW00]

Sooo, it was working. Few issues now:
The vba code above worked, then i saved the file, renamed it, and just reopened it. I can set data so this vba should stop from switching off that page and it doesnt do anything. I also noticed none of the other code i had in other worksheets was running now and would give runtime 9 error. I resaved the file with original filename and rest of the code elsewhere started working again but not this one. Any suggestions?

Also how can i change filename and not have it stop all the vba and macros from running properly?

 

[DaveWW00]

nevermind. i think i got it. i copied your code back in and it started working again. i then changed file name by doing save as, instead of just changing filename from desktop and everything still seems to be working. one more question please. Is there a way to make this same code also run when they save the file? Right now you can have missing data and save the file and it lets you. thanks.

 

[JoeMo]

nevermind. i think i got it. i copied your code back in and it started working again. i then changed file name by doing save as, instead of just changing filename from desktop and everything still seems to be working. one more question please. Is there a way to make this same code also run when they save the file? Right now you can have missing data and save the file and it lets you. thanks.

File must be saved as a macro-enabled file (.xlsm file extension), and individuals must go into their Excel options and enable macros and/or change the Trust Center settings to allow macros to run. There is nothing in the code that would make it intermittent. If macros and events are enabled it will run reliably. Makes no difference what the file name is so long as the file is macro-enabled (.xlsm NOT .xlsx).