Excel VBA Newbies
 

Hi:

I am very new to Excel VBA programming, I have few questions for VBA
Expert.

I have an Excel produced by an order entry application that has
multiple worksheet, each sheet has the same layout except the data is
different (i.e. customer 1 order info on worksheet1, Customer 2 order
info on worksheet2, etc ).

This application is installed on the network and each user has access
to generating the Excel Report.

I need to create an Excel Macro that is independent of the Excel
Report, where this macro can be located on the network drive and when
user have the Excel report loaded, he just need to locate the macro on
the network drive and run it (Is this possible?)

Here's what the macro has to do:
Go through each worksheet,
1. Hide a number of unnecessary columns and rows. (same for each
sheet)
2. Remove some default column heading (same for each sheet)
3. Perform some additional misc stuff. (same for each sheet)
4. On the daily basis, customer order info changes and this macro
should be dynamic and not static, so when order info changes, it
should still work.


Here's my psuto code approach, I would like some expert opinion to see
if my approach is in the right way.

Steps:
1. Create a macro/VBA project independent of the excel workbook (I
don't know how to do this)
2. Within the macro, I first determine how many sheet are there. (I
know how to do this )
3. I scan through each sheet and performing the following I do the
hiding of columns and rows and removing coloring. (I know how to do
this part)
4. Deploy this macro on network and enable other user to run this
macro. (I don't know how to do this)

For step 1 and 4, can any expert give me some hint on how to achive
this?

Thank you

Peter

Excel VBA Newbies
 

Hi Peter;

Yes it is possible. Create a seperate workbook with them
macro in it. Make sure all the calls refer to the
activeworkbook. Put an Auto_Open macro in the seperat
workbook and place all the routine in the Auto_Open macro.

The save the seperate workbook out an XLA add-in. Create
a menu in Excel to open the XLA and then when you call the
XLA it will not take the focus because it is an add-in but
the auto open routine will opperate on the active workbook.

Thanks,

Greg
-----Original Message-----
Hi:

I am very new to Excel VBA programming, I have few
questions for VBA
Expert.

I have an Excel produced by an order entry application
that has
multiple worksheet, each sheet has the same layout except
the data is
different (i.e. customer 1 order info on worksheet1,
Customer 2 order
info on worksheet2, etc ).

This application is installed on the network and each
user has access
to generating the Excel Report.

I need to create an Excel Macro that is independent of
the Excel
Report, where this macro can be located on the network
drive and when
user have the Excel report loaded, he just need to locate
the macro on
the network drive and run it (Is this possible?)

Here's what the macro has to do:
Go through each worksheet,
1. Hide a number of unnecessary columns and rows. (same
for each
sheet)
2. Remove some default column heading (same for each
sheet)
3. Perform some additional misc stuff. (same for each
sheet)
4. On the daily basis, customer order info changes and
this macro
should be dynamic and not static, so when order info
changes, it
should still work.


Here's my psuto code approach, I would like some expert
opinion to see
if my approach is in the right way.

Steps:
1. Create a macro/VBA project independent of the excel
workbook (I
don't know how to do this)
2. Within the macro, I first determine how many sheet
are there. (I
know how to do this )
3. I scan through each sheet and performing the
following I do the
hiding of columns and rows and removing coloring. (I know
how to do
this part)
4. Deploy this macro on network and enable other user
to run this
macro. (I don't know how to do this)

For step 1 and 4, can any expert give me some hint on how
to achive
this?

Thank you

Peter
.

Excel VBA Newbies
 

Peter,

I can think of two methods you can use to achieve the results you are
looking for.

The method I prefer is to create another workbook which contains the macros,
store it on the network, and put controls on a sheet to allow the users to
activate those macros easily.

You could also provide each user with a "personals.xls" workbook. This is a
hidden workbook that contains code locally. I don't as much like this
method, even though it streamlines well, becasue the macros must be updated
on each workstation manually. However, I have met people who love it that
way.

-Mike

Excel VBA Newbies
 

Hi Greg:

Thank you for your prompt feedback, I still have few questions. I hope
you can help me.


<Yes it is possible. Create a seperate workbook with them
<macro in it. Make sure all the calls refer to the
<activeworkbook. Put an Auto_Open macro in the seperat
<workbook and place all the routine in the Auto_Open macro.

I am not sure how to do this, do you have a sample code on this part?

<The save the seperate workbook out an XLA add-in.
How do I do the XLA Add-in

<Create a menu in Excel to open the XLA and then when you call the XLA
it will not take the focus because it is an add-in but the auto open
routine will opperate on the active workbook.

Does the menu go away when user closes their Excel Report?

Thank you
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel VBA Newbies
 

Hi Mike:

Do you have a sample code of the first method you mentioned (below)

<The method I prefer is to create another workbook which <contains the
macros store it on the network, and put <controls on a sheet to <allow
the users to activate those <macros easily.

Thank you

Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!