I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar) on
those new sheets; again the code to do is behind the Master. As I understand
it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

Hi Stuart,

I wish to avoid a module if possible.

Why?

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Create an additional button called "Export Worksheet" that copies the
worksheet into a new workbook, deletes all the buttons and saves it to the
name and location of the user's choice. If you have all related code in
modules, none of it will follow the worksheet when it's copied out of the
workbook..

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Stuart" wrote in message
...
I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar)
on those new sheets; again the code to do is behind the Master. As I
understand it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

Where code is put depends mostly on the scope of the code. If a sub or
function is genereic and could be used by any sheet or form then you probably
want to put it in a module. If you need to declare anything as public you
pretty much have to put that in a module. It would be counter productive to
put the same function in 10 different sheets, because if that function needs
to be changed then you have to change it in 10 places. If on the other hand
the code is very specific as with your master sheet and there is no reason to
share that code then the code should be in the sheet. As for deleting the
sheets with the code... you are giving up control when you do that. Once the
sheet is gone you the programmer can no longer intervene. I personally don't
like to give up control. I would be more inclined to make the sheet very
hidden and change the buttons visible property such that if I want to I can
still get everything back full control to react to any problems. Do I sound
controlling??? or just pragmatic???

HTH

"Stuart" wrote:

I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar) on
those new sheets; again the code to do is behind the Master. As I understand
it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

You can make declare the procedure in the master sheet module and declare it
as public. Then assign it using the sheet code name qualifier


worksheets("NewSheetName").Buttons("Button 1").OnAction = _
Thisworkbook.Name & "Sheet1!General_Button_Click"


So in the sheet module of the Master sheet (has a codename of Sheet1) this
worked for me:

Public Sub General_Button_click()
Dim btn As Button
sName = Application.Caller
Set btn = ActiveSheet.Buttons(sName)
MsgBox btn.Caption & " - " & btn.Name
End Sub

Private Sub CommandButton1_Click()
Worksheets.Add After:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = "NewSheetName"
Set btn = sh.Buttons.Add(308.25, 12, 68.25, 18.75)
btn.OnAction = ThisWorkbook.Name & _
"!Sheet1.General_Button_click"
End Sub

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar)
on
those new sheets; again the code to do is behind the Master. As I
understand
it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

Create an additional button called "Export Worksheet" that copies the
worksheet into a new workbook, deletes all the buttons and saves it to the
name and location of the user's choice. If you have all related code in
modules, none of it will follow the worksheet when it's copied out of the
workbook..

Just to clarify, in case of any confusion (is usually more common with me),
if there is code in the worksheet module and the entire worksheet (object)
is copied, the code will follow; you must copy the cell *contents*, like a
traditional copy/paste type of effort.


--
Regards,
Zack Barresse, aka firefytr

"zackb" wrote in message
...
Create an additional button called "Export Worksheet" that copies the
worksheet into a new workbook, deletes all the buttons and saves it to
the
name and location of the user's choice. If you have all related code in
modules, none of it will follow the worksheet when it's copied out of the
workbook..

Just to clarify, in case of any confusion (is usually more common with
me),
if there is code in the worksheet module and the entire worksheet (object)
is copied, the code will follow; you must copy the cell *contents*, like a
traditional copy/paste type of effort.

Hi Zack,

That's why I suggested you keep your code in modules so you could copy
the worksheets out cleanly.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Many thanks for all the suggestions and comments. I'm trying to help someone
who has even less knowledge of excel than me (......yeah, there must be
someone?) with a simple 'template' for them to try out. They'll report back,
I'll adapt it, and when they're happy I'll pass it to them as a template.
They will then use the template to produce their finished workbook, and when
happy, save it, such that all code is removed. They can then distribute the
book to equally 'newbie' colleagues, without any fear of macro warnings
when the book opens, worrying them.
There is no need for any code from that point onwards ....
the purpose of the template is just to speed up the production of his
finished document.

Later, when he is more comfortable, I'll give him an addin, such that the
'stripping of code' issue will not exist.

Many thanks again.

Regards.

"Rob Bovey" wrote in message
...
Hi Stuart,

I wish to avoid a module if possible.

Why?

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Create an additional button called "Export Worksheet" that copies the
worksheet into a new workbook, deletes all the buttons and saves it to the
name and location of the user's choice. If you have all related code in
modules, none of it will follow the worksheet when it's copied out of the
workbook..

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Stuart" wrote in message
...
I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar)
on those new sheets; again the code to do is behind the Master. As I
understand it, the buttons should be assigned to subs in a general
module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

Understood that. Thanks.

Regards.

"zackb" wrote in message
...
Create an additional button called "Export Worksheet" that copies the
worksheet into a new workbook, deletes all the buttons and saves it to
the
name and location of the user's choice. If you have all related code in
modules, none of it will follow the worksheet when it's copied out of the
workbook..

Just to clarify, in case of any confusion (is usually more common with
me),
if there is code in the worksheet module and the entire worksheet (object)
is copied, the code will follow; you must copy the cell *contents*, like a
traditional copy/paste type of effort.


--
Regards,
Zack Barresse, aka firefytr

Many thanks for the the explanation/pros/cons.

Regards.

"Jim Thomlinson" wrote in message
...
Where code is put depends mostly on the scope of the code. If a sub or
function is genereic and could be used by any sheet or form then you
probably
want to put it in a module. If you need to declare anything as public you
pretty much have to put that in a module. It would be counter productive
to
put the same function in 10 different sheets, because if that function
needs
to be changed then you have to change it in 10 places. If on the other
hand
the code is very specific as with your master sheet and there is no reason
to
share that code then the code should be in the sheet. As for deleting the
sheets with the code... you are giving up control when you do that. Once
the
sheet is gone you the programmer can no longer intervene. I personally
don't
like to give up control. I would be more inclined to make the sheet very
hidden and change the buttons visible property such that if I want to I
can
still get everything back full control to react to any problems. Do I
sound
controlling??? or just pragmatic???

HTH

"Stuart" wrote:

I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar)
on
those new sheets; again the code to do is behind the Master. As I
understand
it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.

Many thanks for the follow-up to the earlier post, in showing how to avoid a
general module, and how to
assign the button to a sub in a specific sheet module.

Regards.

"Tom Ogilvy" wrote in message
...
You can make declare the procedure in the master sheet module and declare
it
as public. Then assign it using the sheet code name qualifier


worksheets("NewSheetName").Buttons("Button 1").OnAction = _
Thisworkbook.Name & "Sheet1!General_Button_Click"


So in the sheet module of the Master sheet (has a codename of Sheet1)
this
worked for me:

Public Sub General_Button_click()
Dim btn As Button
sName = Application.Caller
Set btn = ActiveSheet.Buttons(sName)
MsgBox btn.Caption & " - " & btn.Name
End Sub

Private Sub CommandButton1_Click()
Worksheets.Add After:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = "NewSheetName"
Set btn = sh.Buttons.Add(308.25, 12, 68.25, 18.75)
btn.OnAction = ThisWorkbook.Name & _
"!Sheet1.General_Button_click"
End Sub

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
I have a workbook with a Master sheet. The Master sheet is visible, but
protected. On the Master are buttons
(from the Control Toolbox) that add new sheets and then
format them according to 'pages' stored on the Master. So far all code is
behind the Master.

There is also one form.

When new sheets are added, there are new buttons (from the Forms toolbar)
on
those new sheets; again the code to do is behind the Master. As I
understand
it, the buttons should be assigned to subs in a general module.

I wish to avoid a module if possible.

Once the user has completed their workbook, I would like them to be able
to:
delete buttons in all sheets
delete the form
delete the Master sheet and save the workbook
without any residual code.

Can this be done, or must I set a reference to the VBA
Extensibilty library (for every user) and use (eg) an external button to
remove the code?

Regards.