Macro & button?

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

How can I add a macro & button that can be clicked on a protected sheet?

this is the macro I would like assigned to a button, also how to keep the
button visible when > Options > View > Objects > Hide all, is selected?

Sub SortTotals()

ActiveSheet.Unprotect
ActiveWorkbook.DisplayDrawingObjects = xlPlaceholders
Columns("A:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = False
Selection.AutoFilter Field:=1, Criteria1:="Show"
Columns("A:B").Select
Range("B1").Activate
Selection.EntireColumn.Hidden = True
ActiveWorkbook.DisplayDrawingObjects = xlHide
ActiveWindow.SmallScroll Down:=-9
Range("C7:K74").Select
Selection.Sort Key1:=Range("C7"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

Many thanks,
Steve
 
Thanks for the help Anne,

Due to my lack of knowledge on this subject iv messed it up a bit how can I
remove the menu items iv added to the tools menu?

Thanks,
Steve
 
Tools|Customize (just to show this dialog)
Then find that menu item and drag it off.

If you're going to be screwing around with (pronounced screwing up!) your menus,
you may want to back up your current settings.

Close excel
windows start button|Find/Search
look for *.xlb (name and location vary with versions of excel and windows)

And back it up to a nice spot. If you find more than one file, use the one with
the most current date (you just changed it).

Make a note of where you got it. Then when things get really hairy (and they
will!), you can just close excel and copy your backup right back into its real
location.
 
Thany you Dave!

Dave Peterson said:
Tools|Customize (just to show this dialog)
Then find that menu item and drag it off.

If you're going to be screwing around with (pronounced screwing up!) your menus,
you may want to back up your current settings.

Close excel
windows start button|Find/Search
look for *.xlb (name and location vary with versions of excel and windows)

And back it up to a nice spot. If you find more than one file, use the one with
the most current date (you just changed it).

Make a note of where you got it. Then when things get really hairy (and they
will!), you can just close excel and copy your backup right back into its real
location.
Click to expand...
 
Just for the record, you can also just hold the ALT key to drag buttons off
the toolbar without ever having to open Tools-Customize...

Steven said:
Thany you Dave!

Dave Peterson said:
Tools|Customize (just to show this dialog)
Then find that menu item and drag it off.

If you're going to be screwing around with (pronounced screwing up!)
Click to expand...
your
menus,
you may want to back up your current settings.

Close excel
windows start button|Find/Search
look for *.xlb (name and location vary with versions of excel and windows)

And back it up to a nice spot. If you find more than one file, use the one with
the most current date (you just changed it).

Make a note of where you got it. Then when things get really hairy (and they
will!), you can just close excel and copy your backup right back into
Click to expand...
its
Click to expand...
 
I've always had trouble with getting rid of subitems (Like Print... under File)
this way.



Anne said:
Just for the record, you can also just hold the ALT key to drag buttons off
the toolbar without ever having to open Tools-Customize...
Click to expand...
 
<<How can I add a macro & button that can be clicked on a protecte
sheet?>>

Locate the Forms toolbar. Click on the button icon and draw in th
worksheet. Right click to 'Assign Macro' or set properties.
This works whether sheet is protected or not.

<<keep the button visible when > Options > View > Objects > Hide all
is selected>>
No can do. Could use a toolbar button ,or use a macro to selectivel
hide the other controls etc
 
Back
Top