Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
Move the declaration for myControl outside the functions at the module
level. Then call myControl.Delete in _BeforeClose. ---- Nick Hebb BreezeTree Software http://www.breezetree.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
The above codes are in the ThisWorkbook. Do you mean i have to create a
new module & place declaration there? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
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/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....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_tut...e_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_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm sifar wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Custom commandbar button
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. sifar wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to exchange Custom Commandbar buttons between PC's? | Excel Discussion (Misc queries) | |||
Custom Commandbar | Excel Programming | |||
static commandbar Button | Excel Programming | |||
Creating a CommandBar - what's wrong? | Excel Programming | |||
Value of ComboBox on Custom CommandBar | Excel Programming |