custom toolbar button

[Pat]

The following macros freezes a sheet in different locations when either one
of two custom buttons on the toolbar is clicked.

Sub Freeze1()
ActiveWindow.FreezePanes = False

Application.GoTo Range("A29"), True

Range("D31").Select
ActiveWindow.FreezePanes = True
End Sub

and macro

Sub Freeze2()
ActiveWindow.FreezePanes = False
Columns("D").Select
ActiveWindow.FreezePanes = True
Range("IV1").Select
End Sub

To allow other sheets within the workbook to use the custom button I have
included code within each sheet to allow this to happen. The code I am
using is not compiled correctly and an error occurs when the sheet is
activated. Here is the code being used:

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("Freeze1")
.Visible = True
End With
With .Controls("Freeze2")
.Visible = True
End With
End With
End Sub


Private Sub Worksheet_Deactivate()
With Application.CommandBars("Formatting")
With .Controls("Freeze1")
.Visible = False
End With
With .Controls("Freeze2")
.Visible = False
End With
End With
End Sub

Anyone know where the code is going wrong?
Appreciate any help

Pat

 

[Jim Cone]

Pat,

I added a Freeze1 and Freeze2 button to the Formatting toolbar in Excel 97.
Your Worksheet_Activate and _Deactivate code worked without a hitch.
What happens when you step thru the code?

Regards,
Jim Cone
San Francisco, CA

The following macros freezes a sheet in different locations when either one
of two custom buttons on the toolbar is clicked.

Sub Freeze1()
ActiveWindow.FreezePanes = False
Application.GoTo Range("A29"), True
Range("D31").Select
ActiveWindow.FreezePanes = True
End Sub
and macro
Sub Freeze2()
ActiveWindow.FreezePanes = False
Columns("D").Select
ActiveWindow.FreezePanes = True
Range("IV1").Select
End Sub
To allow other sheets within the workbook to use the custom button I have
included code within each sheet to allow this to happen. The code I am
using is not compiled correctly and an error occurs when the sheet is
activated. Here is the code being used:

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("Freeze1")
.Visible = True
End With
With .Controls("Freeze2")
.Visible = True
End With
End With
End Sub

 

[Pat]

Both buttons execute just fine, but when moving from sheet to sheet (the
sheets which have the Worksheet_Activate() and Worksheet_Deactivate() code)
the following error occurs:

' Invalid procedure call or argument'

What also happens any other sheet (without Worksheet_Activate() and
Worksheet_Deactivate() code) will respond to the custom buttons. This is not
desirable as it freezes the sheet.

Hope I am making sense here!

Pat

 

[Jim Cone]

Pat,

Yes it makes sense, however you did not answer my question.

More Questions...
1. Are the buttons directly on the formatting toolbar or are they items underneath another button?
2. Are the button names spelled Exactly the same as the names in your code?

Go into the code and put your cursor in the first line and press F9.
That will insert a break point (a stop) at that line.
Run your code by activating or deactivating a sheet.
When the code stops, press the F8 key and the code will execute one line at a time.
Continue until the error occurs.
3. Now, which line generated the error?

Regards,
Jim Cone
San Francisco, CA

 

[Pat]

Hi Jim,

1. Are the buttons directly on the formatting toolbar or are they items

underneath another button?
They are "directly on the formatting toolbar"

2. Are the button names spelled Exactly the same as the names in your code?
Yes
3. Now, which line generated the error?

With .Controls("Freeze1")

Pat

 

[Jim Cone]

Pat,

I just tried the activate and deactivate code again, this time in XL2002 and it worked perfectly.

The only possibilities left that I can think of are:
1. that the buttons are actually installed on the "Standard" toolbar.
The formatting toolbar is the one with the font drop down box on it.
2. the buttons are corrupt.
Remove the buttons and install new ones.

Hope some of this helps.

Regards,
Jim Cone
San Francisco, Ca

 

[Pat]

Jim,

You have pinpointed the problem

1. that the buttons are actually installed on the "Standard" toolbar.

I have changed them to the formatting toolbar and it now works fine.

Many thanks for helping me out.
Pat