Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: If cells are empty do not allow save

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If cells are empty do not allow save

    Dear Team,

    I have an excel sheet which is filled by line managers. Very often i get back incomplete sheets.

    1) I have drop down values enabled in Column "C". If drop down value is selected as "YES", then columns "E" to "H" are mandatory. User should not be able to save the sheet without filling in all the columns. A warning message saying "Please input values in required columns"

    2) Colum "D" is also a drop down. If "D" is selected as Yes, there should be a warning message "Please enter training details in Column "G".
    The message can be displayed on closing. User can save the excel without data input

    3) Maximum allowed "YES" in Column "C" is 10. if the count exceeds 10, a warning message should be displayed as "Exceeds quota"

    It would be of great help if you can solve this.

  2. #2
    Board Regular
    Join Date
    Oct 2015
    Location
    Mc Calla, AL
    Posts
    432
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If cells are empty do not allow save

    Give this code a try. Put the following code under ThisWorkbook in the VBAProject window
    Thanks to MrKowz for help on this issue

    Code:
    
    
    Code:
    Option Explicit
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    
    Dim i As Long
    Dim lr As Long
    Dim blankcells As Integer
    
    
    '   Check to see if there are more than 10 rows in Column C with "Yes"
    blankcells = Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes")
    If blankcells > 10 Then
        MsgBox "Exceeds Quota"
    End If
    
    
    '   Check if C = Yes and there are blank cells in Columns E:H
    blankcells = Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes", Range("E:E"), "", Range("F:F"), "", Range("G:G"), "", Range("H:H"), "")
    If blankcells > 0 Then
        MsgBox "You have entered 'Yes' in Column C" _
        & vbCrLf & "but have left blank cell(s) in " & blankcells & " Rows in  Columns E through H"
    End If
    
    
    '   Check if D = Yes and there are blank cells in Columns E:H
    blankcells = Application.WorksheetFunction.CountIfs(Range("D:D"), "Yes", Range("G:G"), "")
    If blankcells > 0 Then
        MsgBox "You have entered 'Yes' in Column D" _
        & vbCrLf & "but have left 'Training Details' blank in  Columns G"
    End If
    
    
    End Sub



  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cells are empty do not allow save

    Thanks a lot MrKowz.... Its working fine.
    But want the macro to start when the user attempts to save the excel sheet

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,530
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If cells are empty do not allow save

    But want the macro to start when the user attempts to save the excel sheet
    It will, if you put it in the right place, it will run automatically whenever anyone tries to save the file.
    Notice what Frank said:
    Put the following code under ThisWorkbook in the VBAProject window
    You MUST put it in the "ThisWorkbook" module in the VB Editor. If you put it in any other module, it will not work autamatically.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cells are empty do not allow save

    I am Sorry MrKowz.. i figured out to trigger the macro on save.
    What i require is without the conditions in the messages are satisfied the sheet should not be saved.

    Is there a way to do it

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,530
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If cells are empty do not allow save

    There is no "MrKowz" who has posted to this thread! Frank was just telling you that he borrowed the code from a post from MrKowz.

    If you want to cancel the save, you simply add the line:
    Code:
    Cancel = True
    where appropriate.

    See here for explanations and examples: https://msdn.microsoft.com/en-us/vba...ve-event-excel
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cells are empty do not allow save

    Thank you.... You are a lifesaver

  8. #8
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cells are empty do not allow save

    One more clarification please... when i use the below macro, if i enter data in any one cell from E to H, the message box is not coming up for that particular row.


    ' Check if C = Yes and there are blank cells in Columns E:H
    blankcells = Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes", Range("E:E"), "", Range("F:F"), "", Range("G:G"), "", Range("H:H"), "")
    If blankcells > 0 Then
    MsgBox "You have entered 'Yes' in Column C" _
    & vbCrLf & "but have left blank cell(s) in " & blankcells & " Rows in Columns E through H"
    End If

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,530
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: If cells are empty do not allow save

    The way you have written the code, it is only checking for the situation where column C has "Yes", and ALL columns E:H are blank.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Nov 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cells are empty do not allow save

    Can you help me set the macro as where column C has "Yes", and any column in E:H are blank.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •