Macro To Run Automatically For Any Worksheet Upon Opening Of THe F

[BB]

Hello! Have recorded a macro in person.xls that basically Auto fits the
cells to show the full name of each column.

Have called it Auto_Open and it should open automatically for any Excel file.

The need is as follows. The user team team will double click on a csv file
and they have associated csv file types with Excel, would like this macro to
run for them and the questions follow.

1) First, prior to distributing the macro to other users, would like to
make sure it works but having an issue getting it to Auto Open when any csv
file is double clicked, the macro is not running, what might be missing here.

Have to go to Tools - Macro - Macros, select Auto_Open and then run it, the
macro is not running automatically when the csv file is opened, had assumed
that calling it Auto_Open would do this.

Currently working with Excel 2003 version.

2) How is the macro, Auto_Open, which was created locally, distributed to
other users - possibly share it from a network location or send it to each
member of the user team

3) How does a user install the macro so it works for them for any Excel file

Have looked at some of the other reponses to similar topics in this forum
but not totally understanding it yet.

Would appreciate any help.

Thanks!

 

[Gord Dibben]

Auto_Open in the Personal.xls only runs when Personal.xls is opened, not
when other files are opened.

Change the name from Sub Auto_Open() to Sub Autofit()

Run the code from a button after you have opened the CSV file


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I would do it exactly the way Gord suggests.

But if you want, you could use an application event that looks at each workbook
and checks the names. If it finds an extension (.csv and maybe .txt or
..prn???), then it can do something special.

If you want to try:

(Saved from a previous post.)

Create a new workbook:

Add this under the ThisWorkbook module.

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Select Case LCase(Right(Wb.Name, 4))
Case Is = ".txt", ".prn", ".csv"
Wb.Worksheets(1).UsedRange.Columns.AutoFit
End Select
End Sub

Save this workbook to a nice location.
as an addin named: AutoFitTextColumns.xla
Close excel and reopen it
Tools|addins
browse to that nice location and select this addin.

Then each time you open excel, this addin will open.

If you decide you don't want it, just tools|addins|and uncheck it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about those application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx