-
Apr 7th, 2018, 04:08 PM
#1
Thread Starter
Member
[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
-
Apr 7th, 2018, 10:32 PM
#2
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
-
Apr 8th, 2018, 02:23 PM
#3
Thread Starter
Member
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.
-
Apr 8th, 2018, 04:22 PM
#4
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
-
Apr 8th, 2018, 05:08 PM
#5
Thread Starter
Member
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.
-
Apr 9th, 2018, 03:46 AM
#6
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
-
Apr 11th, 2018, 09:04 PM
#7
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|