Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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.) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
http://www.cpearson.com/excel/AppEvent.htm
-- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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 wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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 wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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" wrote in message ups.com... 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 wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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. Tom Ogilvy wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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" wrote in message ups.com... 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. Tom Ogilvy wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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. Chip Pearson wrote: 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" wrote in message ups.com... 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. Tom Ogilvy wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have "Freeze Pane" button track current state
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. Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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 wrote: http://www.cpearson.com/excel/AppEvent.htm -- Regards, Tom Ogilvy "Joe Sewell" wrote: 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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Sorting "State" Column onto separate worksheets in same workbook. | Excel Worksheet Functions | |||
"Track Changes" - Prevent turn off track changes to meet SOX regs | Excel Discussion (Misc queries) | |||
freeze window creates multiple "views" suffixed with ":n" | Excel Discussion (Misc queries) | |||
can i get the "message pane" underneath the "threads pane" instea | Excel Worksheet Functions |