Results 1 to 6 of 6

Thread: Check if the cell is blank before saveing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check if the cell is blank before saveing

    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

  2. #2
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if the cell is blank before saveing

    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

  3. #3
    New Member
    Join Date
    Dec 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if the cell is blank before saveing

    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

  4. #4
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,396
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Check if the cell is blank before saveing

    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?
    I visit this site mainly to remember how little I know

  5. #5
    New Member
    Join Date
    Dec 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if the cell is blank before saveing

    Yes I put the code in workbooks code

    Message i didn't change in the above code as it is in testing process.

  6. #6
    New Member
    Join Date
    Dec 2015
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if the cell is blank before saveing

    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

Some videos you may like

User Tag List

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
  •