Running a macro on Save and setting format

[badrion]

I want to run a macro on save. The macro needs to check all cells that are 'numeric' and either change the format to 'general' or put a 'tick in front of the numeric expression.

The reasoning behind this is I use this file in a VB application, it opens the file, queries out the data I need and creates a text file, but when the user changes something in the excel file it resets my format. If anyone has a better idea of how to do this, I'm open for opinions.

Thanx,
Brandi

 

[JVFriederick]

One option might be to pick the entire column to reformat

Columns("C:C&quot.Select
Selection.NumberFormat = "General"

Columns("L:T&quot.Select
Selection.NumberFormat = "General"

Your worksheet layout may not support this, and you will still need to need to ensure the user runs the macro before saving the file. However, you could do a switcheroo and place the macro in Sub Autpen() so that it always runs when the file is OPENED.

 

[badrion]

Actually I got this far, but the on save isn't working. I thought it was
IF ActiveWorkbook.Save = True then
Columns("C:C&quot.Select
Selection.NumberFormat = "General"
End If
MsgBox "Workbook format has been saved" 'just a check msg to see if it works

I don't get the msg it's not working.

 

[JVFriederick]

I would suggest placing the macro in the Autpen() and then creating a button to run Autpen() if desired while the file is open prior to saving.

OR

Don't bother with checking..... create a Save&Close button so the user is encouraged to press it (they like to click things!). Within this macro reformat what is needed, save the file and close.

 

[dsi]

You may want to consider using the Auto_Close macro. Then, when the user closes the file, you can make your configuration changes and resave the file. The downside to this is that you may force a save when the user wants to abort the changes.

 

[badrion]

I got it...... By setting the column to left Justify it fixed the problem. WIERD! No need for a macro afterall...... Sorry for taking up your time.
Thanx.....Brandi