Results 1 to 7 of 7

Thread: [RESOLVED] Check the Cells Blank or Not when saving file

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Resolved [RESOLVED] Check the Cells Blank or Not when saving file

    Hello Everyone,
    I'm trying to create a Dashboard, which is shared workbook. some peoples missed to update some data's on that workbook,
    So i need to create VBA to check the cells while saving the workbook whether its updated or not,

    If A2 column contain some value ( 7 to 10 digit numbers ) then B2 to AG2 cells should be filled with some values (Should not leave it as blank).
    If A3 column contain some value ( 7 to 10 digit numbers ) then B3 to AG3 cells should be filled with some values (Should not leave it as blank).
    If A2 column is empty then entire column Should be blank.
    If any blank cell is there on that range (Ex. B2 to AG2) then file should not save and we need to display the Message.

    Please help me anyone to do this.

    Thanks in Advance

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Check the Cells Blank or Not when saving file

    you can test this to see if it works for what you want
    Code:
    For rw = 2 To 3
        If Not IsEmpty(Cells(rw, 2)) Then
            If WorksheetFunction.CountA(Cells(rw, 2).Resize(, 32)) < 32 Then  '   if any cells empty
                MsgBox "some cell is empty row 2"
                Exit Sub
            End If
            Else
            If WorksheetFunction.CountBlank(Cells(rw, 2).Resize(, 32)) < 32 Then  ' if any cells have value
                MsgBox "some value in row " & rw & ", should be empty"
                Exit Sub
            End If
        End If
        Next
    it will put msgbox at first issue, but will not find additional issues until previous are fixed
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Lightbulb Re: Check the Cells Blank or Not when saving file

    Hello Westconn
    Thanks for your Reply.
    I have tested your code, but it's not working. I tested on sample file, in that file i filled only A column, when i try to saving the workbook, it was saved without any message.

    Please look the attached file.Test (2).zip

    Thanks again.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Check the Cells Blank or Not when saving file

    if you want the code to work automatically whenever you try to save you need the code to be in the workbook_beforesave event, in the thisworkbook code module

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    End Sub
    put cancel = true before exit sub in 2 places

    also as per your original specifications the code should test a2 and a3, i made an error, instead testing b2 and b3
    change to
    Code:
    If Not IsEmpty(Cells(rw, 1)) Then
    also change to
    Code:
    MsgBox "some cell is empty row " & rw
    if you want to test all rows, not just 2 and 3
    change to
    Code:
    For rw = 2 To Cells(Rows.Count, 1).End(xlUp).row    ' from row 2 to last row of data
    Last edited by westconn1; Apr 8th, 2018 at 04:28 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Re: Check the Cells Blank or Not when saving file

    I have changed the code as you mentioned above, but it's not working.
    workbook was saved even the cells are empty(except A column).

    Please look the Code

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For rw = 2 To Cells(Rows.Count, 1).End(xlUp).Row    ' from row 2 to last row of data
        If Not IsEmpty(Cells(rw, 1)) Then
            If WorksheetFunction.CountA(Cells(rw, 2).Resize(, 32)) < 32 Then  '   if any cells empty
                MsgBox "some cell is empty row " & rw
                Cancel = True
                Exit Sub
            End If
            Else
            If WorksheetFunction.CountBlank(Cells(rw, 2).Resize(, 32)) < 32 Then  ' if any cells have value
                MsgBox "some value in row " & rw & ", should be empty"
                Cancel = True
                Exit Sub
            End If
        End If
        Next
    End Sub
    Thanks.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Check the Cells Blank or Not when saving file

    i did test on your sample workbook posted above this morning, so the code was working

    does the code run at all?
    add a breakpoint or a msgbox as the first line of the procedure, to see it it does run

    the procedure must be in the thisworkbook code module for it fire as an event, check if it shows in the dropdown list for workbook in the left dropdown and the events in the right
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Re: Check the Cells Blank or Not when saving file

    Yes..i made wrong.
    Actually i have placed the code on module instead of this workbook..

    Now its working good.
    Thank you so much Westconn for your help.

Posting Permissions

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



Click Here to Expand Forum to Full Width