button self deletion

[Guest_imported]

I am trying to fix a bug in an excel macro. What is supposed to happen is that when a user clicks on one of the buttons in a custom tool bar, the button will change from one icon to another. The way the code does it (not my code, I inherited this) is that it attempts to delete the button that has been pressed, then add another button with a different icon in the same place. The only problem is that vb refuses the delete button. I guess this makes sense, since the deletion occurs within the sub that is called by the button. Is there any way around this, or is there a way to simply change the icon of the button?

It gets a little hairier as well. This was written several years ago in the office 97 format, when all of the toolbar stuff was very different, because of this I’m having a really tough time finding documentation on this. The routine in question is posted below. Thanks in advance.

Sub Freeze_Panes()
'--Toggle on/off Freeze panes
On Error GoTo Err_Freeze_Panes

Worksheets(1).Activate
If ActiveWindow.FreezePanes = False Then
Cells(1, 1).Activate
Cells(FREEZE_CELL, 1).Activate
ActiveWindow.FreezePanes = True
Else
ActiveWindow.FreezePanes = False
End If
Frozen = Not Frozen

'--Change button face
With Toolbars(MAIN_TOOLBAR)
.Visible = True
Application.Toolbars(MAIN_TOOLBAR).ToolbarButtons(1).Delete
If Frozen Then
.ToolbarButtons.Add Button:=212, before:=1
.ToolbarButtons(1).OnAction = "Freeze_Panes"
Else
.ToolbarButtons.Add Button:=211, before:=1
.ToolbarButtons(1).OnAction = "Freeze_Panes"
End If
End With

Exit_Freeze_Panes:
Exit Sub
Err_Freeze_Panes:
MsgBox Error$(), 48, "Freeze_Panes"
Resume Exit_Freeze_Panes
End Sub

 

[MichaelRed]

I won't really go there, but It looks like the only action invloved is to toggle the freeze panes and 'change' the icon. The asctivity is the toggle of "Freeze_Panes". The iconchange is just a user 'cue' to the button's next action.

I would take this as a "design requirement" type of document and re-code the thing in the current environment an then just replace the original.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over