Have "Freeze Pane" button track current state

[Joe Sewell]

I've added the Freeze Panes button to a toolbar, but I'm disappointed
that it doesn't show the current state. While it does toggle between
Freeze and Unfreeze, I'd like it to show whether the current worksheet
has frozen panes or not.

In Word I can use application events to detect window & document
changes; it can be slow, but it works. Excel, though, doesn't propagate
worksheet events up to the application, or at least it doesn't seem to.
If there were an App_WorksheetActivate event, I could use it to update
the status of my toolbar button(s). There doesn't seem to be such a
beast, though, in Excel 2003. (That's what my place of work has
"standardized" on, so upgrading is not an option.)

Is there a better way -- or *any* way, for that matter -- to get a
Freeze Panes button to show whether panes are frozen or not? (Yes, I
know I can look for the split bar, but that isn't as obvious as looking
at a toolbar button.)

 

[Tom Ogilvy]

http://www.cpearson.com/excel/AppEvent.htm

 

[Joe Sewell]

Interesting stuff. The Class_Initialize event may be the part I've been
missing; that's assuming, of course, that I can catch an event that
tells me the user has changed worksheets at the application level.

I'll take a look at it. Thanks, Tom!

 

[Tom Ogilvy]

Same events found at the workbook level are found at the app level but
applies to all workbooks.

Private Sub object_SheetActivate(ByVal Sh As Object)

object would be a reference to the application.

sh.parent would tell you which workbook.

there are many events to choose from. Look in the object browser at the
application object and then scroll through its methods, properties and
events.

 

[Chip Pearson]

The App_SheetActivate event will tell you what sheet in any open workbook is
being activated.

Public WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
Debug.Print "Workbook: " & Sh.Parent.Name, "Sheet: " & Sh.Name
End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Joe Sewell]

I just noticed that it depends on Workbook_Open triggering. I don't
really want to have this in all my workbooks; I want this to work
regardless of the workbook that's open.

 

[Chip Pearson]

You could create an add-in that implements application-level events. It
would track all events in all open workbooks.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Joe Sewell]

Our previous messages passed each other on the network.

Thanks for the info, Chip. It sounds like I'll be able to do this after
all.

 

[Joe Sewell]

Thanks to you and Chip, Tom, I've got the situation more or less under
control. I've got the routines getting called as expected, but I
neglected to notice that the State property of "built-in"
CommandBarButtons is read-only. I'll have to roll my own macro-based
button to do the same thing the built-in button is doing.