PDA

View Full Version : Solved: Macro runs regardless of Msg Box press NO



BENSON
11-29-2007, 10:32 PM
Could you please check the following code, at the moment the macro runs even when I press no on the message box that appears:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
a = MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", _
vbYesNo)
If a = vbYes Then Cancel = True


Dim WsTgt As Excel.Worksheet
Dim rngCopy As Excel.Range

Application.ScreenUpdating = False
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
ActiveSheet.Range("C284").Copy
.Offset(, 1).PasteSpecial xlPasteValues
ActiveSheet.Range("C286").Copy
.Offset(, 2).PasteSpecial xlPasteValues
ActiveSheet.Range("C288").Copy
.Offset(, 3).PasteSpecial xlPasteValues

Set rngCopy = ActiveSheet.Range("G260:AZ260")
rngCopy.Copy
.Offset(, 4).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End With
End Sub


Function NextEmptyRow(Wks As Worksheet) As Long
Dim Rng As Range
Set Rng = Wks.Range("A" & Wks.Rows.Count).End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
NextEmptyRow = Rng.Row
End Function


Thanks in advance for any help

mikerickson
11-29-2007, 10:57 PM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
a = MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", _
vbYesNo)
If a = vbYes Then
Cancel = True
Application.ScreenUpdating = False
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
ActiveSheet.Range("C284").Copy
.Offset(, 1).PasteSpecial xlPasteValues
ActiveSheet.Range("C286").Copy
.Offset(, 2).PasteSpecial xlPasteValues
ActiveSheet.Range("C288").Copy
.Offset(, 3).PasteSpecial xlPasteValues

Set rngCopy = ActiveSheet.Range("G260:AZ260")
rngCopy.Copy
.Offset(, 4).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End With
Else
Rem Cancel = True:Rem If you don't want No=Close
End If
End Sub

BENSON
11-29-2007, 11:26 PM
perfect thank you so much

mikerickson
11-29-2007, 11:36 PM
You're welcome.

Zack Barresse
11-30-2007, 02:11 AM
Why are you needing to copy when you are just transferring values?...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim A As VbMsgBoxResult
A = MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", _
vbYesNo)
If A = vbYes Then
Cancel = True
Application.ScreenUpdating = False
With Workbooks("Gardens History.xls").Sheets(1)
With .Cells(.Rows.Count, "A").End(xlUp)
.Offset(0, 1).Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
.Offset(, 2).Value = ActiveSheet.Range("C284").Value
.Offset(, 3).Value = ActiveSheet.Range("C286").Value
.Offset(, 4).Value = ActiveSheet.Range("C288").Value
.Offset(, 5).Resize(1, 46).Value = ActiveSheet.Range("G260:AZ260").Value
End With
End With
Else
Rem Cancel = True:Rem If you don't want No=Close
End If
Application.ScreenUpdating = True
End Sub

HTH