Creating a Custom commandbar button
One way is to mark your controls as yours. Then you can just clean up all that
belong to you. (Nice when you add the second, third, ... controls).
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CleanUpControls
End Sub
Private Sub Workbook_Open()
Dim myControl As CommandBarControl
Call CleanUpControls
Set myControl = Application.CommandBars("Formatting").Controls _
.Add(Type:=msoControlButton, ID:=2950, Befo=19, temporary:=True)
With myControl
.DescriptionText = "Pastes the Data from Windows Clipboard"
.Caption = "Paste the Clipboard"
.OnAction = "PasteTxT"
.Style = msoButtonIconAndCaption
.Tag = "__Sifar__"
End With
End Sub
Sub CleanUpControls()
Dim ctrl As CommandBarControl
Do
Set ctrl = Nothing
On Error Resume Next
Set ctrl = Application.CommandBars("Formatting") _
.FindControl(Tag:="__Sifar__")
On Error GoTo 0
If ctrl Is Nothing Then
Exit Do
Else
ctrl.Delete
End If
Loop
End Sub
sifar wrote:
i have made a code to generate a Custom button when
the workbook opens (placed it in Workbook_Open() )
_________
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i
For Each i In Application.CommandBars("Formatting")
.Controls.Delete
Next i
'myControl.Delete
'Application.CommandBars("Formatting").Controls(my Control).Delete
End Sub
Private Sub Workbook_Open()
Dim myControl
Set myControl = Application.CommandBars("Formatting").Controls _
.Add(Type:=msoControlButton, ID:=2950, Befo=19)
With myControl
.DescriptionText = "Pastes the Data from Windows Clipboard"
.Caption = "Paste the Clipboard"
.OnAction = "PasteTxT"
.Style = msoButtonIconAndCaption
End With
End Sub
i want to delete the custom button on Workbook close. i dont know how
to do the same.
Please help ASAP.
Rgds,
Sifar
--
Dave Peterson
|