VBA Macro to auto run on open and close with formatting

[titoexcel]

Hi,

I am trying to keep column G as text format. I would not like anyone to change the format as another program needs to read it as text.

so the format has to be for ex. 12/22/2014 or 12/01/2014 but text and users should not be allowed to change it.

What i had so far is not working, can anyone take a look see what I am doing wrong. What I did is basically run a macro when it opens and convert column G to text and when it closes run the macro convert column G to text. Is there a better way? Please help

Private Sub workbook_open()
Columns("G:G").Select
Selection.NumberFormat = "@"
End Sub


Private Sub workbook_beforeclose()
Columns("G:G").Select
Selection.NumberFormat = "@"
End Sub

 

[titoexcel]

I think the code needs to be placed on a sheet module not a regular module. That associates it with the change event for the sheet.

Just type a date in G10 and leave the cell. if you move back to the cell it will have changed from 12/12/2014 to '12/12/2014 (note the quote indicating text) If you typed '12/12/2014 it will do nothing because it is not formatted as a date.

I am doing something wrong as it does not do that.

 

[par60056]

When you open the code editor, you need to dbl click the sheet that you want the event to effect. That will open a window with "Bookname - Sheetname (Code)" in the title. The event code needs to go in there.

I just did a little experiment and if the cell is type text before you type in it, then the event does not need to do anything.

 

[par60056]

can you describe what is happening? If you set a break point in the event procedure you should be able to see it fire off and see what it is doing.