Before Save Macro

guarrac1no

New Member
Joined
Mar 26, 2009
Messages
7
In My sheet I would like to check to make sure col 15 is true before going through the process of adding the contents of each row from col 6 - 10 to column 5. (Col 5 could already contain values)

This is my code:

Code:
Private Sub Workbook_ThisWorkbook(ByVal SaveAsUI As Boolean, cancel As Boolean)
    BeginRow = 8
    EndRow = 87
    ChkCol = 15
    BeginCol = 6
    EndCol = 10

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = True Then
         For RowCnt = BeginRow To EndRow
            Cells(RowCnt, 5).Value = Cells(RowCnt, 5).Value + Cells(RowCnt, ColCnt).Value
            Next ColCnt
        End If
    Next RowCnt
End Sub


Any suggestions?
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Isn't that what this portion of your code is doing?
Rich (BB code):
If Cells(RowCnt, ChkCol).Value = True Then
         For RowCnt = BeginRow To EndRow
            Cells(RowCnt, 5).Value = Cells(RowCnt, 5).Value + Cells(RowCnt, ColCnt).Value
            Next ColCnt
        End If

Just move that If statement outside of the For RowCnt = BeginRow To EndRow loop
 
Upvote 0
Basically it has to go through the loop to check each row to ensure the value in col 15 is true. It doesn't seem to work and I was wondering if you saw any errors in my code
 
Upvote 0
Code:
Private Sub sumitup()
BeginRow = 7
EndRow = 86
ChkCol = 10
ChangeCol = 5
NewCol = 6
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = True Then
           Cells(RowCnt, ChangeCol).Value = Cells(RowCnt, ChangeCol).Value + Cells(RowCnt, NewCol).Value
           Cells(RowCnt, NewCol).Value = 0
        End If
    Next RowCnt
End Sub

This is my new code which works

Now to call it I have this in the workbook:


Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    Worksheets("Sheet5").sumitup
    a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
End Sub

I keep getting a runtime error 9 : Subscript out of range

Ideas?

Thanks
 
Last edited by a moderator:
Upvote 0
Worksheets("Sheet5").sumitup

Greetings to all,

I am guessing a bit here, but I suspect that you have the Private Sub 'sunitup' housed in Sheet5's module. If I am wrong, disregard the rest. If correct:

You cannot call a private sub from another module.

Now if you won't be running 'sumitup' from anywhere but the BeforeSave event, you could move the sub to ThisWorkbook module, but you would still need to qualify 'Cells' - so that Excel knows what sheet it is talking about...

Now if you have a button of shortcut key that you use to run 'sumitup' at times other than BeforeSave, then you would want to move it to a standard module and make it public. Again, Cells would need qualified.

In the below, we take the second choice, as this works either way.

In ThisWorkbook Module:
Code:
Option Explicit
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Call sumitup
    
    If Not MsgBox("Do you really want to save the workbook?", vbYesNo) = vbYes _
        Then Cancel = True
End Sub

In a Standard Module:
Code:
Sub sumitup()
Dim BeginRow As Long, EndRow As Long, ChkCol As Lon
ChangeCol As Long, NewCol As Long, RowCnt As Long
Dim wksSheet5 As Worksheet
 
    Set wksSheet5 = ThisWorkbook.Worksheets("Sheet5")
 
    BeginRow = 7
    EndRow = 86
    ChkCol = 10
    ChangeCol = 5
    NewCol = 6
 
    With wksSheet5
        For RowCnt = BeginRow To EndRow
            If .Cells(RowCnt, ChkCol).Value = True Then
                .Cells(RowCnt, ChangeCol).Value = _
                    .Cells(RowCnt, ChangeCol).Value _
                    + .Cells(RowCnt, NewCol).Value
                .Cells(RowCnt, NewCol).Value = 0
            End If
        Next RowCnt
    End With
End Sub

Hope this helps,

Mark
 
Upvote 0
Assuming you are not needing the "sumitup" code for anything else, you could just add it into the Workbook_BeforeSave macro and not call it at all.
 
Upvote 0
Guys, Thanks for the help!

It doesnt seem to work so I am going to keep playing but basically this is what i have in ThisWorkbook

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

sumitup

If Not MsgBox("Do you really want to save the workbook?", vbYesNo) = vbYes _
Then Cancel = True
End Sub
Public Sub sumitup()
Dim BeginRow As Long
EndRow As Long
ChkCol As Long
ChangeCol As Long
NewCol As Long
RowCnt As Long
Dim wksSheet5 As Worksheet
Set wksSheet5 = ThisWorkbook.Worksheets("Sheet5")

BeginRow = 7
EndRow = 86
ChkCol = 10
ChangeCol = 5
NewCol = 6

With wksSheet5
For RowCnt = BeginRow To EndRow
If .Cells(RowCnt, ChkCol).Value = True Then
.Cells(RowCnt, ChangeCol).Value = _
.Cells(RowCnt, ChangeCol).Value _
+ .Cells(RowCnt, NewCol).Value
.Cells(RowCnt, NewCol).Value = 0
End If
Next RowCnt
End With
End Sub
 
Upvote 0
Code:
Dim BeginRow As Long
EndRow As Long
ChkCol As Long
ChangeCol As Long
NewCol As Long
RowCnt As Long

Greetings again,

You did not copy the code straight. There were commas and line continuations, which are required. Sorry if my style threw you a bit; just tack a Dim in front of each one missing it.

Kristy's point is very valid. If not called from anywhere else, its not much code just to combine straight into the BeforeClose.

Hope this helps,

Mark

Edit: ACK! My apologies, I just scrolled bakc up to the code I had posted. My bad, as it was me who didn't copy correctly.

Try:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
    Call sumitup
 
    If Not MsgBox("Do you really want to save the workbook?", vbYesNo) = vbYes _
        Then Cancel = True
End Sub
 
Private Sub sumitup()
Dim BeginRow As Long
Dim EndRow As Long
Dim ChkCol As Long
Dim ChangeCol As Long
Dim NewCol As Long
Dim RowCnt As Long
Dim wksSheet5 As Worksheet
 
    Set wksSheet5 = ThisWorkbook.Worksheets("Sheet5")
 
    BeginRow = 7
    EndRow = 86
    ChkCol = 10
    ChangeCol = 5
    NewCol = 6
 
    With wksSheet5
        For RowCnt = BeginRow To EndRow
            If .Cells(RowCnt, ChkCol).Value = True Then
                .Cells(RowCnt, ChangeCol).Value = _
                    .Cells(RowCnt, ChangeCol).Value _
                    + .Cells(RowCnt, NewCol).Value
                .Cells(RowCnt, NewCol).Value = 0
            End If
        Next RowCnt
    End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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