Okay, here we go.
Make a copy of your workbook. I would hate for this not to work right
and we mess up your original.
Now, open the copy.
Hit Alt+F11 to get to the Visual Basic Editor (VBE).
On the left-hand side of the window, double-click ThisWorkbook.
On the right-hand side, paste the following code:
Start code----------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This closes the toolbar when the workbook is closed.. This is
what we call an *on-event* procedure (macro) because it is
'run when the workbook is closed.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My&Menu").Delete
End Sub
Private Sub Workbook_Open()
'The Dim statements make the rest of the code easier to create.
Dim Mybar As CommandBarControl
Dim myButton As CommandBarControl
Dim i As Integer
Dim A(2) As Variant
On Error Resume Next
'This checks if the menu already exists. If it does, it does not
create a new one.
'The ampersand (&) in the name of the menu underlines the letter
that follows it to give
'it a keyboard command (Alt-m) as many menus have.
Application.CommandBars("Worksheet Menu Bar").Controls("My&Menu").Delete
With Application.CommandBars("Worksheet Menu Bar").Controls
Set Mybar = .Add(Type:=msoControlPopup, Before:=11)
Mybar.Caption = "My&Menu"
End With
'Note that the parts of the array are ( " Title of menu
option " , " Macro to Run " , FaceID for toolbar button)
A(1) = Array("Autofit", "AutofitAll", 80)
A(2) = Array("Sort B", "SortB", 81)
For i = 1 To UBound(A)
With Mybar.Controls
Set myButton = .Add(Type:=msoControlButton)
With myButton
.Caption = A(i)(0)
.OnAction = A(i)(1)
.FaceId = A(i)(2)
End With
End With
Next i
End Sub
End code-------------------------
In the code above, change My&Menu to any menu name you like.
You can get a very cool little tool from John Walkenbach's site, which
provides the Face IDs for command buttons (or just use those I've
provided):
http://j-walk.com/ss/excel/tips/tip67.htm
Now, hit Insert-Module from the menu, and paste the following code:
Start code-----------------
Sub AutoFitAll()
'autofits all columns in a worksheet
ActiveSheet.Unprotect passwordhere
Application.ScreenUpdating = False
Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select
Application.ScreenUpdating = True
ActiveSheet.Protect passwordhere
End Sub
Sub SortB()
'sorts column B
ActiveSheet.Unprotect passwordhere
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect passwordhere
End Sub
End Code---------------------------------
Change B1 in the code above to the cell that would be active when
hitting the sort button. If you have multiple columns by which you
want to allow sorting, then you'll have to provide multiple macros, or
we'd have to set up a userform.
Change "passwordhere" to your password, if any. If no password, simply
delete the whole word "passwordhere" in both lines of code above.
To do your find and replace, I really need more information. Will we
be searching in entire cells? In other words, does the entire cell
have to match the FIND criteria? Or will we be looking for words
within cells too? Please give as much information as you can about
your need for find and replace. |
