View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Jim Thomlinson[_3_] Jim Thomlinson[_3_] is offline
external usenet poster
 
Posts: 983
Default Where to place Code question

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.