enforcing macros

[Dave Peterson]

Since you liked Harlan's suggestion better than mine (I did too), I don't want
to look at mine anymore <bg>.

But the easiest question first: Which fires first--_beforeclose or _beforesave?

Put these in a brand new test workbook:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "hi from beforeclose"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "hi from beforesave"
End Sub

And make a change and close the workbook. You'll see the order right away.

In fact, Chip Pearson has nice instructions for working with events at:
http://www.cpearson.com/excel/events.htm

He includes a workbook that you can download that has this kind of stuff in it.
So you can see the order of lots of events firing.

hi dave/john/harlan,

i tried all the codes, spent nearly 36 hours performing regressive
test.

dave - the code that i got from you, there is a small glitch.

harlan - the code works fine, but i could not understand certain
things. what happens when i close the file, say no to save changes and
open the file with macros disabled? how is it hiding the sheets?
beforesave event is not being triggered, there is no sheet-hide
procedure in beforeclose.

It's not hiding the sheets when you open it, it's hiding the sheets each time
you save it.

When you save it,
your code hides the sheets
saves the workbook
unhides the worksheets (which means the workbook is marked as needing a save
But then your code lies to excel telling it the workbook is saved, so if
you close without saving again, it has the sheets hidden.

most of the final code is built around harlan's algorithm. i am still
trying to find a glitch or two in his code .

That'll be difficult to do!

I made some minor changes to your code. I got rid of the Public declaration and
added two Dim's within a couple of procedures--you weren't really using it for
public purposes and it seems safer to keep in encased(?) in each procedure.

I removed some of your (obvious <bg>) comments. I find it difficult to read the
code when there's too many of that type comment.

"ws.visible = true" shouldn't need a comment (I bet you agree, too.)

And I added a return to range (that's the curSelection variable and
application.goto). I added the .screenupdating = false to help stop the
flicker--my pc had a slight case of the jitters, though--but hardly noticeable.
And I've stopped using "As Integer". Longs achieve the same purpose and it's
one less thing for me to remember <g>.

And I removed a few "on error resume next" lines. I didn't see any reason for
them to exist.

So you could try this version--remember the user can do a few things to still
break this--disable events and even though you stopped File|SaveAs within your
code, you can still copy it to a new location in windows explorer.

Anyway, here's what I did. I couldn't break you're code--but you'll want to
test the heck out of it:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim intResp As Long

If Me.Saved = True Then Exit Sub

intResp = MsgBox("Do you want to save the changes you made to - '" _
& ActiveWorkbook.Name & "'?", vbYesNoCancel + vbExclamation, _
"Microsoft Excel")

Select Case intResp
Case vbYes
Call Workbook_BeforeSave(False, False) 'hide, save, exit
Case vbNo
Me.Saved = True 'exit
Case vbCancel
Cancel = True 'stay
End Select

End Sub

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

Dim WS As Worksheet
Dim CurSelection As Range

Set CurSelection = Selection

Cancel = True

'prevent file from being saved elsewhere
If SaveAsUI = True Then
MsgBox "Restriction on FileSaveAs location!", vbCritical
Cancel = True
Exit Sub
End If

'hide the flickering
Application.ScreenUpdating = False

'hide every sheet before save,
'whether workbook is being closed or not
For Each WS In Me.Worksheets
If LCase(WS.CodeName) = "sheet2" Then
'do not hide the titlesheet
Else
WS.Visible = xlSheetVeryHidden
End If
Next WS

Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

'if workbook is not being closed,
'unhide every Sheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS

With Application
'go back to original location
.Goto CurSelection
'and reset screenupdating
.ScreenUpdating = True
End With

'else save message comes up twice,
'once for beforeclose,
'once for beforesave
Me.Saved = True

End Sub
Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS
End Sub

 

[icestationzbra]

hi dave,

thanks for the enhancement in that code.

i have just started coding in the month of december, so i go by th
book in terms of variables and i write a lot of comments. it bugs m
too, but i am still learning the trade. tricks of the trade will b
picked up soon.

there is a glitch here, albeit a small one.

we are restricting SaveAsUI. now, if i paste this code into a fres
workbook and try to save the file thru menu or toolbar, it does no
allow that. however, when i close the file, it saves the file (if
chose yes for save) as book1, book2 etc. second time around, its not
problem, that is, a file once already saved does not pose problems. it
not such a big deal, as the file that this code is going into, i
already made. but, just for academic purposes, it would be nice t
figure out a way to deal with this.

i have another problem.

i have a huge list of items in one column, their statuses (accepted
rejected, WIP) in another column, and defects (if rejected and WIP) i
yet another column.

i have used SUMPRODUCT function to get the desired result, however
would like to use a macro to be used instead. this would take care o
the resetlastcell issue, currently the formula is dragged down t
several rows and has exponentially increased the filesize. i hav
written a macro, but i could not get the algorithm right, hence th
results are wrong. please help me out in finetuning it. i have attache
a sample xl file.

thanks,

mac

Attachment filename: listfile.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40398

 

[Harlan Grove]

harlan - the code works fine, but i could not understand certain
things. what happens when i close the file, say no to save changes and
open the file with macros disabled? how is it hiding the sheets?
beforesave event is not being triggered, there is no sheet-hide
procedure in beforeclose.

I'm assuming you started out with the worksheets hidden. If so, then
whenever the file is opened with macros enabled it can't be resaved without
the BeforeSave hiding the worksheets. If it's subsequently opened with
macros disabled, then unless the workbook is protected (so that users can't
unhide worksheets - weak security, but usually enough to thwart most users)
users shouldn't be unhiding any worksheets.

most of the final code is built around harlan's algorithm. i am still
trying to find a glitch or two in his code .

which event occurs first, beforeclose or beforesave?

BeforeClose.

 

[Dave Peterson]

One way is to disable the save if the workbook has been never beend.

The beforeSave code could start this way:

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

Dim WS As Worksheet
Dim CurSelection As Range

Set CurSelection = Selection

Cancel = True

If Me.Path = "" Then
MsgBox "cannot save unsaved workbook"
Exit Sub
End If

'continue existing code....

=============

I'm sorry, but I don't open excel attachments. Too many bad things can happen.
(Lots of people have this attitude. But maybe someone will open it and make a
suggestion.

But if no one does, you'll want to start a new thread and post your question and
code as plain text. Not many fear that!

And without opening your workbook to see if this even makes sense, you may want
to try Data|Pivottable. It's a very neat way to get summaries. Do it a few
times manually to learn what you want. Then record a macro when you do it.

You'll have to tweak the code a bit, but probably not too much. Once you get
the hang of these things (after a bit of a learning curve), you'll be hooked and
wonder how you got along without them.

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx


==========
And if your code was for resetting the last used cell, Debra Dalgleish has some
code at:
http://www.contextures.com/xlfaqApp.html#Unused

 

[icestationzbra]

harlan, thanks for the explanation. i am still not able to break you
code thru testing .

dave, no issues at all. i will tell you what my sheet looks like.

***************************************************
Sheet1
Item Status Defects
A Rejected 0
A Accepted 5
B Accepted 0
C Accepted 0
D Rejected 5
D Accepted 0

*****
Sheet2
Item Accepted Count Rejected Count
A
B
C
D
***************************************************
CODE:

Option Explicit

Public intAcc As Integer
Public intRows1 As Integer
Public intRej As Integer
Public intRows2 As Integer

Dim m As Integer
Dim n As Integer

Sub calcStatus()

m = 2
n = 2
intRows2 = 0
intRows1 = 0
intAcc = 0
intRej = 0

Sheet1.Select
Range("A1").Activate
ActiveCell.CurrentRegion.Select
intRows1 = Selection.Rows.Count - 1

Sheet2.Select
Range("A1").Activate
ActiveCell.CurrentRegion.Select
intRows2 = Selection.Rows.Count - 1

For m = 2 To intRows1

For n = 2 To intRows2

If (Sheet1.Range("A" + Trim(Str(m))) = Sheet2.Range("A"
Trim(Str(n)))) Then

If (Sheet1.Range("B" + Trim(Str(m))) = "Accepted") Then

intAcc = intAcc + 1


Else

If (Sheet1.Range("B" + Trim(Str(m))) = "Rejected"
Then

intRej = intRej + 1

End If

End If

End If

Sheet2.Range("B" & n).Value = intAcc

Sheet2.Range("C" & n).Value = intRej

Next n

Next m

End Sub

***************************************************

would you be able to help me?

thanks,

mac

 

[Dave Peterson]

How about another alternative to your macro:

Use some worksheet formulas:

In sheet2, B2, put this formula:
=SUMPRODUCT(--($A2=Sheet1!$A$2:$A$7),--(Sheet1!$B$2:$B$7="accepted"),
Sheet1!$C$2:$C$7)

And in C2, put:
=SUMPRODUCT(--($A2=Sheet1!$A$2:$A$7),--(Sheet1!$B$2:$B$7="rejected"),
Sheet1!$C$2:$C$7)

(watch out for line wrap in the post. These are all one cell.)

Drag down through your columns.

These two formulas sum up the Defects (Column C) for each category. If you
really meant just a count of rows that are used, then drop that 3rd argument.

In sheet2, B2, put this formula:
=SUMPRODUCT(--($A2=Sheet1!$A$2:$A$7),--(Sheet1!$B$2:$B$7="accepted"))

And in C2, put:
=SUMPRODUCT(--($A2=Sheet1!$A$2:$A$7),--(Sheet1!$B$2:$B$7="rejected"))

Don't forget to adjust the ranges for your real data.

And even though I bet you didn't try the Data|pivottable stuff, don't forget to
look into it. You'll be pleasantly surprised.

 

[icestationzbra]

hi dave,

thanks for the suggestion.

i have used pivottables extensively in the workbook. there are certai
places and situations in there which are not conducive to pivottabl
being used.

one reason, i do not want to use pivottable is that - i would like t
be able to create a sheet, dump data onto it, provide that formula, an
delete it at the end of the operation - all using a macro. i have th
rest of the code ready, i am just not getting the results right throug
my algorithm.

i have another grouse. if i use formulas on the sheets, i have to dra
the formula down to several rows in order to accommodate for furthe
addition in data along the rows. this however, increases the filesize
hence i came up with the idea of creating a temporary sheet, dump data
manipulating it with a formula and deleting it at the end of th
operation.

mac

 

[Dave Peterson]

Not trying to argue too much, but you can do all the pivottable stuff in code.
Convert it to values, delete the sheet, all that stuff.

If you want a formula approach, have your macro populate the cells with the
=sumproduct() formula and then later, just convert to values.

I don't think I'd spend time rewriting =sumproduct() when I could use it.

 

[icestationzbra]

dave,

i am a little obstinate. however, your statement about not reinventin
the wheel, drove home the point.

i shall reuse the code with sumproduct.

thanks,

mac