Creating a Custom commandbar button

[sifar]

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(myControl).Delete
End Sub


Private Sub Workbook_Open()
Dim myControl
Set myControl = Application.CommandBars("Formatting").Controls _
..Add(Type:=msoControlButton, ID:=2950, Before:=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

 

[Nick Hebb]

Move the declaration for myControl outside the functions at the module
level. Then call myControl.Delete in _BeforeClose.

 

[sifar]

The above codes are in the ThisWorkbook. Do you mean i have to create a
new module & place declaration there?

 

[Dave Peterson]

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, Before:=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]

Hi, I get Error message "Cannot find Project Library" &
msoCommandButton gets highlighted.

What do i do?

have loaded the Addins viz., Analysis toolpack, Analysis Toolpack-VBA,
selected references viz, MS Forms 2.0 in References.

Does it mean that Excel not fully installed on PC?

How do i load all these files on Workbook_open() using VBA????


Rgds,

Sifar

 

[Dave Peterson]

That code worked ok for me.

My bet is that you got extra characters when you copied from Google. Google
seems to add extra spaces and dashes in bad spots.

You may be able to connect directly to the newsgroup and copy from there.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

 

[sifar]

Hi Dave,

Its nothing to with your code. getting the same message at a Cybercafe
PC. works fine on my Home PC.

Made some changes to your code.....

Sub CleanUpControls()
Dim cbar As CommandBar
Dim ctrl As CommandBarControl

Do
Set cbar = Nothing
Set ctrl = Nothing
On Error Resume Next
Set cbar = Application.CommandBars("PastyFellow")
Set ctrl = cbar.FindControl(Tag:="Sifar")
On Error GoTo 0

If ctrl Is Nothing Then
Exit Do
Else
ctrl.Delete
cbar.Delete
End If
Loop

End Sub

This worked fine for me by deleting the CustomCommandBar along with the
Control.

Please advice.

 

[Dave Peterson]

First, I think I'd move that cbar stuff out of the loop.

Option Explicit
Sub CleanUpControls()
Dim cbar As CommandBar
Dim ctrl As CommandBarControl
Set cbar = Nothing
Set ctrl = Nothing

Set cbar = Application.CommandBars("PastyFellow")

Do
Set ctrl = Nothing
On Error Resume Next
Set ctrl = cbar.FindControl(Tag:="Sifar")
On Error GoTo 0

If ctrl Is Nothing Then
Exit Do
Else
ctrl.Delete
End If
Loop

cbar.Delete

End Sub

Second, on the troublesome pc, look under Tools|References.

If you see a reference marked MISSING, then maybe unchecking that reference
would fix the problem. I've never seen this error with a constant, though--but
it'll be easy to check.