Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Excel VBA to prevent Excel file before closing & saving

    Hi,

    I am trying to prevent the excel file to have specific cells filled prior closing & saving it. Since I am new to VBA can someone suggest me or write a VBA for me in this case ?

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,287
    Thanks
    46
    Thanked 255 Times in 235 Posts
    Mohan,

    Place this code in the ThisWorkbook Module. Change the cells to which ever you want cleared. You can have as many as you want. You could also apply a named range instead (ex, Scores is cells A1, B1, C1). When you attempt to save, the cells will be cleared prior.

    HTH,
    Maud

    Individual cells:
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        [a1] = ""
        [b1] = ""
        [c1] = ""
    End Sub
    Named Range:
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        [Scores].ClearContents
    End Sub
    From Excel, press Alt-F11 then paste the code as the image below describes.

    saveclose.png
    Last edited by Maudibe; 2014-11-23 at 08:59.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    124
    Thanks
    1
    Thanked 11 Times in 11 Posts
    Put these two macros in the THISWORKBOOK module

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Len(Application.Trim(Sheets("sheet1").Range("a1")) ) < 1 Then
    MsgBox "fill cells"
    Cancel = True
    End If
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Len(Application.Trim(Sheets("sheet1").Range("a1")) ) < 1 Then
    MsgBox "fill cells"
    Cancel = True
    End If
    End Sub

  4. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,287
    Thanks
    46
    Thanked 255 Times in 235 Posts
    I believe the BeforeClose event is not necessary as the Before save will always clear the cells before closing unless of course you do not want to save (which they will then be cleared on closing). I also think the OP wants to prevent the cells from being filled
    Last edited by Maudibe; 2014-11-23 at 09:06.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,404
    Thanks
    208
    Thanked 833 Times in 766 Posts
    Hey Y'all,

    Depending on the workbook requirements another option would be to just unlock all the cells, lock the ones you don't want filled, then protect the workbook. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    Lounger
    Join Date
    May 2013
    Posts
    46
    Thanks
    29
    Thanked 4 Times in 4 Posts
    Would something like this be OK?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
    Target.Value = ""
    End If
    Application.EnableEvents = True
    End Sub

    Good Luck
    Alexandra

Posting Permissions

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