Macro or ActiveX for buttons
 

Mickey,

I have discovered that (a) all the buttons can only be coloured grey
True. You have a lot more options with the ones from the Control Toolbox

that a user can select and delete the button
Crush their sticky little fingers

I have to leave the worksheets unprotected so that functions like auto filter
buttons work
You can protect the sheet with code and still allow AutoFilter to work
Sample code:

Sub ProtectFromStickyFingers()
With Worksheets("Sheet1")
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub

The above would have to be in the Workbook.Open Event as the
..EnableAutoFilter will not be there upon closing and reopening
the workbook.

Advice on the best way forward would be appreciated
I fought using the Control Toolbox for a while (the forms are easier)
but once you get used to using them, they are a lot more versatile.

John



Microsoft wrote:

Hi,
I am pretty much a novice at Macros but have managed to use several buttons
in a large workbook. I have discovered that (a) all the buttons can only be
coloured grey, and (b) that a user can select and delete the button ( I
have to leave the worksheets unprotected so that functions like auto filter
buttons work)

I have started to explore the control box and found that those buttons can
be coloured and seem to make things difficult for a user to mess and delete.

Q. Should I try and develop some buttons from the control box or continue
with the basic buttons provided by the toolbar (macros) Advice on the best
way forward would be appreciated,

Thanks,
Mickey

Macro or ActiveX for buttons
 

Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One query,
when you write that the macro has to be in the Workbook.Open Event, what is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey


"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.

Macro or ActiveX for buttons
 

Apologies to all, I have just noticed that my newsreader has put my name as
'Microsoft' That was an unintentional error,

Apologies,
Mickey

Macro or ActiveX for buttons
 

Hi Mickey,
Open the VBA editor. In the top left window you will see a list of objects
(add-ins, workbooks, sheets).
If its not already expanded, expand the list under VBA Project
(yourworkbookname.xls) and you will see a listing for "thisworkbook".
Double click it and a module will open. In the left dropdown of the code
window, select "workbook".
Excel should place this in the code window.

Private Sub Workbook_Open()

End Sub

Add your event code inside the sub and it will execute every time the
workbook is opened.
You can disable the open event by holding down the shift key while opening
the workbook.


--

John

johnf202 at hotmail dot com


"Microsoft" wrote in message
...
Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One
query,
when you write that the macro has to be in the Workbook.Open Event, what
is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey


"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.

Macro or ActiveX for buttons
 

Mickey,

It can be in the Workbook.Open Event or a regular sub named
Auto_Open

Easiest way would be to use the Auto_Open
In place of
Sub ProtectFromStickyFingers()
use
Sub Auto_Open()

Place this in a regular module.
e.g.
Alt + F11
Right click on "ThisWorkbook" in the "Projects" window
Select "Insert/Module" (the module panel will appear to the right)

Now copy and paste the folowing into that module:

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End With
End Sub

The above will run each time the workbook is opened and
protect "Sheet1" with UserInterFaceOnly = True (meaning
that your code can modify cells on the sheet but the user can't)
and EnableAutoFilter = True (meaning that the AutoFilter should
work).

John

Microsoft wrote:

Hi John,
Wo\w! Thx for such a great reply, that is a great help for me. One query,
when you write that the macro has to be in the Workbook.Open Event, what is
meant by that?

Thx, I am very happy to get such a useful reply,
Mickey

"John Wilson" wrote in message
The above would have to be in the Workbook.Open Event as the
.EnableAutoFilter will not be there upon closing and reopening
the workbook.