How can I kill custom buttons??

ntruong · Oct 30, 2003

Hello Experts,
I have a big headache..
I created some custom buttons (and they run macros) and was able to display them on different computers. Now we don't need them any more but no matter how many times I killed the custom toolbar (and the custom buttons were put on the custom toolbar) everytime I open though a new workbook or the same workbook, those custom buttons show up everytime. Please note that the workbook has network level that we share in the department and off-site office like one in Dallas for example.

Please help me KILL those CUSTOM BUTTONS once and for all.

Desperated,
Nee

Greg Truby · Nov 4, 2003

Nee,

As I said, I've also had situations at work where I have distributed spreadsheets with custom toolbars and then I would like to get rid of them. So (as workload permitted) I put together a tool for myself to use to solve this problem. The tool could be placed on a network drive or else set up the values and then e-mail to users for them to open and run. There's a custom button underneath the input cells that runs the main macro. Alas COLO's gem of a utility didn't capture it. But you could launch w/ Alt+F8 or, if confident inputs are well-defined, use the WB_Open() method to launch Sub KillCustomToolbars().

Input cells are C2:C4. In the code I reference via named ranges on the small chance that I'd ever change the layout of the input sheet.

Input Sheet:

Toolbar Killer.xls

A

B

C

D

1

2

NameofToolbartoKill

Custom1

3

NameofFiletoSeek(Dirty)(Canusewildcards)

*deletete?t*.xls

4

Name&PathofNetworkReplacement(Clean)

u:\buttondeletetest-clean.xls

5

6

7

8

Sheet1

The code itself is in a module and is as follows:OptionExplicit' Written by: Greg Truby' Last Revision: 04 Nov 2003' Revision History:'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯Sub KillCustomToolBars()'______________________________________________________________________________' Procedure to attempt to kill off obsolete custom toolbars that were' attached to spreadsheets distributed to others.

 Dim strToolbarToKillAsString
 Dim strDirtyFileAsString, strCleanFileAsString
 Dim strMsgOptionsAsString, strMsgInstructAsString
 Dim booShowInstructAsBoolean, intMsgResultAsInteger, intChoiceAsInteger
 Dim varFileListAsVariant
 Dim objFileSystemAsObject
 Dim wbDirtyAs Workbook, wbActiveAs Workbook, wbOtherAs Workbook
 Dim i%

 strToolbarToKill = [TargetToolbar] ' Named Range
 strDirtyFile = [FileNameDirty] ' Named Range
 strCleanFile = [FileNameClean] ' Named Range

 ' _____Ready to go?
 If strToolbarToKill = "" _
 Or strDirtyFile = "" _
 Or strCleanFile = ""Then
 MsgBox "One or more required fields are blank.", vbCritical, "Missing Required Field"
 ExitSub
 EndIf
 If Dir(strCleanFile) = ""Then
 MsgBox strCleanFile & " not found.", vbCritical, "File Not Found"
 ExitSub
 EndIf

 ' _____Variable setup
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 Set wbActive = ActiveWorkbook
 booShowInstruct =True
 strMsgInstruct = "In order to clean " & UCase(strToolbarToKill) & " from this file you must " & _
 "use the Customize Toolbar dialog box." & vbCr & _
 "(Customize Toolbar dialog will be displayed after " & _
 "this Instructions box is closed.)" & vbCr & vbCr & _
 "1. Click the ATTACH... button." & vbCr & _
 "2. Click " & UCase(strToolbarToKill) & " in the righthand list." & vbCr & _
 "3. Click the DELETE button in between the two lists." & vbCr & _
 "4. Click OK." & vbCr & _
 "5. Click CLOSE." & vbCr & vbCr & _
 "-- Close this Instruction box using OK to see these instructions " & _
 "if another file is found." & vbCr & _
 "-- Close thisInstruction box using CANCEL to avoid seeing these " & _
 "instructions again."


 ' _____Close all other open wb prior to clean up
 ForEach wbOther In Workbooks
 If wbOther.Name<> wbActive.Name _
 And UCase(wbOther.Name)<> "PERSONAL.XLS"Then
 wbOther.Close
 EndIf
 Next wbOther

 ' _____Delete "General" Instance of Toolbar
 If LookForToolbar(strToolbarToKill)Then
 Application.CommandBars(strToolbarToKill).Delete
 EndIf

 ' _____Look for files that match DirtyFile spec
 ' _____(load names and paths into array)
 varFileList = LookForFile(strDirtyFile)
 If TypeName(varFileList) = "String"Then ' will return "String()" if found any
 If varFileList = "Error"Then
 MsgBox "Execution Canceled", vbCritical, "Error"
 ElseIf varFileList = "False"Then
 MsgBox "No instances of " & strDirtyFile & " found.", vbInformation
 EndIf
 GoTo Exit_KillCustomToolbars
 EndIf

 ' _____do the cleanup
 For i = 1 ToUBound(varFileList)
 Set wbDirty = Workbooks.Open(Filename:=varFileList(i))
 If LookForToolbar(strToolbarToKill)Then
 strMsgOptions = UCase(strToolbarToKill) & " is attached to file:" & vbCr & _
 String(80, "-") & vbCr & _
 varFileList(i) & vbCr & _
 String(80, "-") & vbCr & vbCr & _
 "Would you like to?" & vbCr & vbCr & _
 "1. OVERWRITE this file with the clean copy from the network." & vbCr & _
 "2. CLEAN " & UCase(strToolbarToKill) & _
 " from this file (preserve any data in [" & wbDirty.Name & "])." & vbCr & _
 "3. DELETE [" & wbDirty.Name & "] from this directory."

 intChoice = 0
 Do
 intChoice = Val(InputBox(strMsgOptions, "Select Cleanup Option", "1"))
 LoopUntil intChoice > 0 And intChoice< 4
 SelectCase intChoice
 Case 1 ' Overwrite
 wbDirty.Close SaveChanges:=False
 objFileSystem.CopyFile Source:=strCleanFile, _
 Destination:=varFileList(i)

 Case 2 ' Clean
 If booShowInstructThen
 intMsgResult = MsgBox(strMsgInstruct, vbOKCancel, "Instructions")
 booShowInstruct = (intMsgResult = vbOK)
 EndIf
 Application.Dialogs(xlDialogCustomizeToolbar).Show
 wbDirty.Close SaveChanges:=True

 Case 3 ' Kill
 wbDirty.CloseFalse
 Kill varFileList(i)
 EndSelect
 Else
 wbDirty.CloseFalse
 EndIf
 Next i

 ' _____One last time...
 If LookForToolbar(strToolbarToKill)Then
 Application.CommandBars(strToolbarToKill).Delete
 EndIf


Exit_KillCustomToolbars:'"""""""""""""""""""""""
 Set objFileSystem =Nothing
 Set wbActive =Nothing
 Set wbDirty =Nothing
 Set wbOther =NothingEndSub'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯Function LookForFile(strFileName)AsVariant'______________________________________________________________________________
 ' Search through all files on drive C:\ looking for strFileName' Return an array of filenames (and paths) if found' Return "False" (string) if not found, "Error" if hit error.

 Dim strPathAsString, strLongNameAsString
 Dim strDirResultAsString
 Dim varShellRetValAsVariant
 Dim intHandleAsInteger
 Dim strRetVal()AsString
 ReDim strRetVal(0)

 OnErrorGoTo ErrorLookForFile

 ' _____Run a batch file that lists all directories on C:
 ' _____(if this batch file does not yet exist, create it).
 intHandle = FreeFile
 If Dir("C:\DirList.bat") = ""Then CreateBatchDirectoryLister
 If Dir("C:\Directories.txt")<> ""Then Kill "C:\Directories.txt"
 varShellRetVal = Shell("C:\DirList.bat", vbMaximizedFocus)
 If varShellRetVal = 0Then Err.Raise vbObjectError + 1, "LookForFile", "Bad DOS Dir Return"

 ' _____VB trying to open the file before DOS is closing
 ' _____its output file, so slow it down just a bit.

 Loiter 3

 ' _____Run through the text file created by the batch file
 ' _____looking for files that meet specs in strFileName
 Open "C:\Directories.txt"ForInputAs #intHandle
 DoWhileNot EOF(intHandle)
 LineInput #intHandle, strPath
 strLongName = strPath & "\" & strFileName
 strDirResult = Dir(strLongName)
 DoWhile strDirResult<> ""
 ReDimPreserve strRetVal(UBound(strRetVal) + 1)
 strRetVal(UBound(strRetVal)) = strPath & "\" & strDirResult
 strDirResult = Dir()
 Loop
 Loop
 Close #intHandle

 IfUBound(strRetVal) > 0Then
 LookForFile = strRetVal
 Else
 LookForFile = "False"
 EndIf
 ExitFunction

ErrorLookForFile:'""""""""""""""""
 MsgBox "Error: " & Err.Number & vbCrLf & _
 "Desc: " & Err.Description & vbCrLf & _
 "Source: " & Err.Source, _
 vbCritical, "Error"
 LookForFile = "Error"EndFunction'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯PrivateSub CreateBatchDirectoryLister()'______________________________________________________________________________' Create a DOS Batch file that will list all directories into a text file' Then use SHELL to execute the batch file and create C:\Directories.txt

 Dim intHandleAsInteger
 intHandle = FreeFile
 Open "C:\DirList.bat"ForOutputAs #intHandle
 ' _____Use PRINT command because WRITE command embeds quotation marks
 Print #intHandle, "cls"
 Print #intHandle, "C:"
 Print #intHandle, "echo Creating List of All Directories"
 Print #intHandle, "dir c:\ /a:d /b /s > c:\Directories.txt"
 Close #intHandleEndSub'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯Function LookForToolbar(strBarSought)AsBoolean'______________________________________________________________________________

 Dim i%

 LookForToolbar =False ' Default return value

 For i = 1 To Application.CommandBars.Count
 If Application.CommandBars(i).Name = strBarSoughtThen
 LookForToolbar =True
 ExitFor
 EndIf
 Next iEndFunction'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯Sub Loiter(intSecToWaitAsInteger)'______________________________________________________________________________

 Dim newHourAsInteger, newMinuteAsInteger, newSecondAsInteger, WaitTimeAsDate

 newHour = Hour(Now())
 newMinute = Minute(Now())
 newSecond = Second(Now()) + intSecToWait
 WaitTime = TimeSerial(newHour, newMinute, newSecond)
 Application.Wait WaitTimeEndSub

Hope this is of any help to you.

Regards,

ntruong · Nov 4, 2003

Hi Greg / Tusha,

Thanks for the findings and great explanation. I realized now that putting custom toolbar or buttons on excel is not a good idea .. they are undestroyable and it is a real pain to kill them pc by pc.
I learned.
Sorry about being late in getting back to you (busy coping with other "innovations")

Best regards,
Nee

tusharm · Nov 4, 2003

I suspect you find this frustrating, but we should clear up one item. I did not write nor did I imply that customizing toolbars / buttons is a bad idea or that the result is 'undestroyable.' What I indicated was that you should delete the toolbar / button using the same mechanisms you must already have in place to publish changes to your workbook.

ntruong said:
Hi Greg / Tusha,

Thanks for the findings and great explanation. I realized now that putting custom toolbar or buttons on excel is not a good idea .. they are undestroyable and it is a real pain to kill them pc by pc.
I learned.
Sorry about being late in getting back to you (busy coping with other "innovations")

Best regards,
Nee

Greg Truby · Nov 4, 2003

Nee,

I agree with Tushar, a custom toolbar can be a very handy thing to distribute with a spreadsheet. While putting code in that appends custom items to the menu bar is easier to "clean up" by having the workbook delete the menu items upon closing, it requires a more coding and the users have to know where to find your custom menu items. Custom toolbars are easier to create and normally easier for the users to interface with.

To expound upon Tushar's idea of using the same dissemination procedures to clean off obsolete toolbars. This will only work if you overwrite the old spreadsheet that had the attached toolbar with a "clean" copy that no longer has the toolbar attached. The custom toolbar would stay registered in Excel (I'm assuming in the xlb file we've discussed here) until you go in and delete it. But since it is no longer "attached" you could handle the deletion programmatically. In the "clean" file that you overwrite with, you could put code in the WB's open method to handle deleting the old toolbar. Providing the user has no other old files with that toolbar attached, it would then be gone. But again - if any user has any data in the old WB that he wishes to retain - this option loses a lot of its appeal. Then you're back to manual intervention or using something akin to what I wrote for myself to speed up the process.

Regards,

ntruong · Nov 4, 2003

Hello Greg / Tushar,

Thanks you both for putting in the time and the very detailed explanation to this function.
What both of you said was exactly what I thought -- trying to make things handy -- so I will learn your guides and will conquer what I still could not do before. I'll treat you guys beers if you were in the neighborhood.

Best regards,
Nee

tusharm · Nov 4, 2003

You are welcome, Nee. Glad to be of help.

How can I kill custom buttons??

ntruong

Active Member

Greg Truby

MrExcel MVP

Excel Facts

ntruong

Active Member

tusharm

MrExcel MVP

Greg Truby

MrExcel MVP

ntruong

Active Member

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

How can I kill custom buttons??

ntruong

Active Member

Greg Truby

MrExcel MVP

Excel Facts

ntruong

Active Member

tusharm

MrExcel MVP

Greg Truby

MrExcel MVP

ntruong

Active Member

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock