Is this when it's appropriate to put the macro in the worksheet's code area?

[nj]

Hi,

I'd like to run this past this group for any better ideas and some
general information.

I have inherited a workbook. In it are five worksheets that end up as
independent workbooks, associated with five separate links on a
webpage. Each of these five sheets contains some buttons wired to
macros. Hiding/unhiding unused columns or rows, showing/hiding
decimals, etc. These macros are all together in one module.

This becomes a nuisance when we make the separate copies for the web.

I *think* the most elegant solution is to put the macros in the code
area (sorry, don't know the proper term for this) for each of the
worksheets. I haven't done this before -- all of my code to date lives
in the workbook modules, except for the last_mod macros (which I
created by very precisely following the instructions I found in one of
the group posts).

My goal is to simply be able to make a copy of the sheet (after Paste
Special to Values) and have the macros go with automatically.

So, my questions are:
1. Is this the best solution?
2. What is different from when I create macros in the workbook
modules? What do I need to watch out for?

As always, thanks so much. I learn tons from your posts and refer to
them frequently. Kind of makes one wonder what a hit counter would
look like on some of them!

NJ

 

[Guest]

Does nj mean you live in New Jersey or those are your initials? I think the
best way to handle your problem is to put the macros into a personal.xls
file. The personal.xls file is a seperate file that loads when you start
excel and is not in the workbooks cotaining your data.

Put Personal.xls in the following directory with you macros

C:\Program Files\Microsoft Office\Office10\XLStart

 

[Guest]

Is nj your initilas or do you live in New Jersey like me.

I think the solution to your problems is to create a personal.xls file which
contains the macros. Personal.xls runs everytime you start excel. This way
you don't have to have your macros in the actual excel files that contain
data, but you still can run the macros.

Put Personal.xls in the following directory on your PC

C:\Program Files\Microsoft Office\Office10\XLStart

 

[Guest]

Hi nj, It is difficult to give a specific answer to your questions without
knowing exactly where the code resides, what it does and how the data is
obtained and dispositioned. However, I can tell you that generally speaking,
code for controls will reside in the initialize and click procedures for the
controls, code for worksheet events, changes and functions linked to the
worksheet will normally be in the worksheet code module which is accessed by
clicking the sheet tab at the bottom of the screen, then click "view code".
Code which moves, copies, searches, manipulates, combines, separates, pastes,
performs functions, etc. is normally entered in the standard module. The
standard module is the one that appears in the project window as Module1,
Module2, etc. The average user will probably never have more than Module1.
You will also see in the project window a ThisWorkbook. That is where you
enter code for Workbook related events such as open or change. Well, that is
the nickel tour.

I am still learning this stuff myself, but I can tell you that when I first
began, I did not care if it looked good to someone else. If it worked, I was
real happy. The frills and efficiency sort of fall in place as we go along.

 

[nj]

Hi, JLGWhiz,

You nailed and, frankly, I forgot all about being able to put the code
in the controls! I haven't had occasion to do that either.

Thanks for the reminder!

NJ