Disable button until save

[Nerdalert314]

Good morning,

I am using a button to move end of day balances to beginning of day and clear out data. If the button is clicked twice it will move 0's to the beginning balances. I am trying to prevent this from happening. I want the button to only be clicked once until the file has been saved. Since we use the same file everyday I can't just disable after 1 click. Does anyone have any suggestions?

Sub Clear()
'
' Clear Macro
Sheets("Blotter").Select
       Sheets("Blotter").Range("C9").Copy
       Sheets("Blotter"). Range("K6").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       Sheets("Blotter").Range("K44").Copy
      Sheets("Blotter").  Range("K4").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Sheets("Blotter").  Range("K44,K10:K14, K17:K18, K25:K31").FormulaR1C1 = "0"
       Sheets("Blotter").Range("E10:E15,E17:E35, D49:D51").ClearContents
    Sheets("Difference Ticket Totals").Select
   Sheets("Difference Ticket Totals").Range("B4").Copy
   Sheets("Difference Ticket Totals").Range("B1").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Shadow Posting").Select
   Sheets("Shadow Posting").Range("B6:D6").Copy
   Sheets("Shadow Posting").Range("B2").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Shadow Posting").Range("B6:D6").ClearContents
   Sheets("Shadow Posting").Range("B12:C12, B17:C17, B26:C26, B34:C34, E12:E15, E17:E24, E26:E32, E34:E38, G24,G32, G34:G38").ClearContents
    Sheets("Outstanding Checks").Select
   Sheets("Outstanding Checks").Range("B3:B9").Copy
   Sheets("Outstanding Checks").Range("C3").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Outstanding Checks").Range("B6:B8").FormulaR1C1 = "0"
    Sheets("BLP Balances").Select
   Sheets("BLP Balances").Range("B6").Copy
   Sheets("BLP Balances").Range("B2").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("BLP Balances").Range("B6, E2:E3").FormulaR1C1 = "0"
        Sheets("Staff Proofs-BCS").Select
   Sheets("Staff Proofs-BCS").Range("D97:D98, E97:E98, G97:G98").FormulaR1C1 = "0"
   Sheets("Staff Proofs-BCS").Range("A67:A86").FormulaR1C1 = "NDT"
   Sheets("Staff Proofs-BCS").Range("A67:A86").Select
    With Selection.Interior
        .Pattern = xlNone
    End With
   Sheets("Staff Proofs-BCS").Range("B4:Q17, B19:Q54, B59:Q86").FormulaR1C1 = "0"
   Sheets("Staff Proofs-BCS").Range("A35:A54").FormulaR1C1 = "CDT#"
   Sheets("Staff Proofs-BCS").Range("A35:A54").Select
   
    With Selection.Interior
        .Pattern = xlNone
    End With
 
    Sheets("Blotter").Select
    Range("B1:M1").Select
End Sub

 

[dmt32]

Hi,
untested but try this update to your code & see if does what you want

Sub Clear()
'
' Clear Macro
    With Sheets("Blotter")
        If .Range("A1").ID = CStr(Date) Then
            MsgBox "End Of Day Balances Already Submitted", 16, "Already Submitted"
            Exit Sub
        Else
            .Range("K6").Value = .Range("C9").Value
            .Range("K4").Value = .Range("K44").Value
            .Range("K44,K10:K14, K17:K18, K25:K31").FormulaR1C1 = "0"
            .Range("E10:E15,E17:E35, D49:D51").ClearContents
            .Range("A1").ID = Date
        End If
    End With
    
    
    With Sheets("Difference Ticket Totals")
        .Range("B1").Value = .Range("B4").Value
    End With
    
    With Sheets("Shadow Posting")
        .Range("B2").Resize(, 3).Value = .Range("B4").Value
        .Range("B6:D6").ClearContents
        .Range("B12:C12, B17:C17, B26:C26, B34:C34, E12:E15, E17:E24, E26:E32, E34:E38, G24,G32, G34:G38").ClearContents
    End With
    
    With Sheets("Outstanding Checks")
        .Range("B3:B9").Copy
        .Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .Range("B6:B8").FormulaR1C1 = "0"
    End With
    
    With Sheets("BLP Balances")
        .Range("B2").Value = .Range("B6").Value
        .Range("B6, E2:E3").FormulaR1C1 = "0"
    End With
    
    With Sheets("Staff Proofs-BCS")
        .Range("D97:D98, E97:E98, G97:G98").FormulaR1C1 = "0"
        .Range("A67:A86").FormulaR1C1 = "NDT"
        .Range("A67:A86").Interior.Pattern = xlNone
    End With
    
    With Sheets("Staff Proofs-BCS")
        .Range("B4:Q17, B19:Q54, B59:Q86").FormulaR1C1 = "0"
        .Range("A35:A54").FormulaR1C1 = "CDT#"
        .Range("A35:A54").Pattern = xlNone
    End With
    
End Sub

when button pressed, current Date is is placed in Range.ID property of cell. If there is no match, code continues otherwise, msgbox appears informing user data already submitted.

Note. It is assumed that you only open your workbook once a day as Range ID property may not hold it's value once workbook is closed. If you open & close workbook on more than one occasion each day, use a free cell in your workbook & replace ID with the Range.Value property to store the date value.

Dave

 

[Nerdalert314]

Thank you!

 

[dmt32]

Thank you!

most welcome - glad suggestion helped

Dave