Check if the cell is blank before saveing

bhinderwala

New Member
Joined
Dec 12, 2015
Messages
18
Hi

I have a macro to save a file and make a register, next serial number, print and save a file as pdf which works fine.
i want a macro which can check the particular cell or cell if they are filled or not. if they are not filled user must have to filled that cells and then other macros run.

Can any body help me in this.

Muhammad
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Place the following code into workbook's code model (usually ThisWorkbook) and adjust cell's address if needed:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If IsEmpty(Range("A1").Value) Then
        MsgBox "Fill value in A1"
        Cancel = True
    Else
        '... Some other code
    End If
End Sub
 
Upvote 0
Thanks for your quick reply

I tried this code before but system bypass the macro and saving the file without any values in the cell.

I write the below code as per your input.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Range("I5:M5").Value) Then
MsgBox "Fill value in A1"
Cancel = True
Else
SaveAsPDF
End If
End Sub




Module code is as under

Sub VariationNumber()
'To Gereate the Next Serial number of any Documents

Range("D2").Value = Range("D2").Value + 1
' To Clear the Contents of the Specified Cells
Range("A8:B19").ClearContents
Range("F8:M19").ClearContents
Range("C21:E21").ClearContents
Range("I21:M21").ClearContents
Range("I5:M5").ClearContents


End Sub

Sub SaveWithNewName()
Dim NewFn As Variant
PostToRegister
ActiveWorkbook.Save
ActiveSheet.Copy
NewFn = "C:\Users\19973\Desktop\Variation Reports\Variation" & Range("D2").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFn, xlOpenXMLWorkbook
ActiveWorkbook.Close True
VariationNumber
Save
End Sub

Sub PostToRegister()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("VariationReport")
Set WS2 = Worksheets("VariationRegister")

'Figure Out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important Value to Register

WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("Date"), WS1.Range("Vno"), WS1.Range("ShipperID"), WS1.Range("StoreKeeperName"))



End Sub

Sub PrintVar()
'To Print the Active Sheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Sub Save()
'To Save an active Sheet
ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name
End Sub

Sub SaveAsPDF()
'To Save a File as PDF in Other Folder


PostToRegister
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\19973\Desktop\Variation Reports\Variation" & Range("D2").Value & ".pdf"
VariationNumber
Save

End Sub
 
Upvote 0
did you type private sub or place it in the workbook's code as Sektor stated?

Also you have changed the requirement to I5:M5 but the msg asks people to complete A1..is that right?
 
Upvote 0
I am facing again one issue

System is poping up the message that the value is not entered once i click ok the serial number is changing automatically
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top