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

 

[par60056]

There are a couple problems with that.

first, if you take a Date and format it as text, it will show number like 41919 (today)

second you want beforeSave rather than beforeClose (although modifying it before close may force it to ask if you want to save)

You can try this, If there are formulas in G they will be lost because the values are being replaced with hard text strings.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim currRow As Long
Dim lastRow As Long


lastRow = Range("G65535").End(xlUp).Row
For currRow = 1 To lastRow
    With Range(Cells(currRow, 7), Cells(currRow, 7))
        If UCase(.NumberFormat) Like "*D*" Then
            .Value = "'" & Format(.Value, "mm/dd/yyyy")
            .NumberFormat = "@"
        End If
    End With
Next
End Sub

 

[titoexcel]

Hi thank you. So if someone goes and changes the format for G44 to Date, this macro will convert it to text upon saving? I also added data validation where they can only enter the correct format (ex. 12/12/2014 or 12/01/2014)

 

[par60056]

By default id somebody enters a value and Excel thinks it looks like a date, it will convert it to a datevalue and set the formatting. The macro looks at the formatting for the cells and looks for a D. if it finds 1 it assumes Excel has made this a date format converts the value to a string in the format mm/dd/yyyy and sets the format to text so it won't try to convert it the next time there is a save.

It looks at every cell in G when doing the save. It will probably not be noticeable in terms of the delay unless there are thousands of rows.

Another option would be to use a worksheetChange event and do the conversion as soon as they change the cell. Check the target to see if it is in column G and then look to see if it is a date and replace it immediately. This will be fast and nobody should notice.

 

[titoexcel]

By default id somebody enters a value and Excel thinks it looks like a date, it will convert it to a datevalue and set the formatting. The macro looks at the formatting for the cells and looks for a D. if it finds 1 it assumes Excel has made this a date format converts the value to a string in the format mm/dd/yyyy and sets the format to text so it won't try to convert it the next time there is a save.

It looks at every cell in G when doing the save. It will probably not be noticeable in terms of the delay unless there are thousands of rows.

Another option would be to use a worksheetChange event and do the conversion as soon as they change the cell. Check the target to see if it is in column G and then look to see if it is a date and replace it immediately. This will be fast and nobody should notice.

How would we go about worksheetchange event? I like the idea as the rows would never exceed 1000 for now.

 

[par60056]

This looks like it works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If (Not Intersect(Target, Columns("G:G")) Is Nothing) Then
    For Each thecell In Intersect(Target, Columns("G:G"))
        With thecell
            If UCase(.NumberFormat) Like "*D*" Then
                .Value = "'" & Format(.Value, "mm/dd/yyyy")
                .NumberFormat = "@"
            End If
        End With
    Next
End If
End Sub

 

[par60056]

Using worksheet change is if there are a lot of rows. It will only process the changed ones not all of them.

 

[titoexcel]

Using worksheet change is if there are a lot of rows. It will only process the changed ones not all of them.

So i just put a date in G10 12/12/2014 and changed the formatting to scientific. So the macro should run and change it to text?

I tried but it didn't change it.

 

[titoexcel]

I just open editor and place the code you provided in it? Anything else i should do to ensure its not the basic errors?

 

[par60056]

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.