none
VBA to force cells to be in TEXT format in Excel

    Question

  • Hi everyone,

    I hope you can help. I have a spreadsheet that is used as part of a file import process using SSIS and SQL Server. The spreadsheet has a number of date fields/columns formatted as Date (Type:*DD/MM/YYYY, Locale: English (U.K.). My question is can someone help me out with the VBA code that;

    a) OnOpen and OnClose of the spreadsheet, those specific cells will be changed to Text datatype? Something like the following; =TEXT(<cell value>, "DD/MM/YYYY"). Basically I want all the data except for the header row to be stripped of any formatting Excel has chosen and forced to be changed to TEXT. 

    b) When the spreadsheet is opened, the option to change the formatting of any cells is disabled. The file is basically a template, so all I want the users to be able to do is to enter data and to save the spreadsheet. I don't want them editing any panes, windows, adding/removing columns etc.

    Your help will be much appreciated. Thanks.

    Monday, May 16, 2011 4:28 PM

Answers

  • Hi Calvin and thanks for your response. Currently I have this routine which is called at Workbook_BeforeClose;

    Sub FormatCellsAsText()

    Dim MyFormula As String
        Sheets("Sheet1").Unprotect userinterfaceonly:=True
        For Each MyCell In Worksheets("Input - Corrections").Range("DateFields3").Cells
            If Not (MyCell.Value = vbNullString) Or MyCell.Value <> "" Then
                MyFormula = "=Text(""" & MyCell.Value & """,""DD/MM/YYYY"")"
                MyCell.Value = MyFormula
            End If
        Next MyCell
    End Sub

    ...seems to be doing the trick. But I like the idea that you and Cheng are suggesting, which is to modify the template so that the format of the cells in the range are TEXT, then protect the worksheet so that users are unable to change the format again. Again, my only problem with that is how do I write the code to prevent users from copying and pasting data from elsewhere into the workbook that could change the format. i.e. do you know how I can ensure that any data which is copied and pasted will always be PASTESPECIAL TEXT?

    Thanks in advance for your time and help.

    Wednesday, May 18, 2011 8:43 AM
  • ...seems to be doing the trick. But I like the idea that you and Cheng are suggesting, which is to modify the template so that the format of the cells in the range are TEXT, then protect the worksheet so that users are unable to change the format again. Again, my only problem with that is how do I write the code to prevent users from copying and pasting data from elsewhere into the workbook that could change the format. i.e. do you know how I can ensure that any data which is copied and pasted will always be PASTESPECIAL TEXT?

     

    Hi wattyjnr,

     

    Good work!

    The code also works fine on my side.

     

    When you paste data into a worksheet, you can choice which type to paste in Paste Special: Paste formulas or Paste value…

    When we paste date using right clicking, we can easily choice it from the list. However, by default, when we paste data using shortcut key (Ctrl + v), the value and the formatting will be paste. So we can create a macro to implement the paste value feature and make the macro’s shortcut key as Ctrl + V to “overload” the shortcut key.

     

    1. Record a macro;

    2. In the dialog, make the shortcut key as Ctrl + v

    3. Perform the copy and paste value actions

    4. Stop the macro

    5. Open the macro, and delete the code we don’t need. Please check the code follow:

     

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+v
    
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Sub
    

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 18, 2011 9:35 AM
    Moderator

All replies

  • Can anyone help with the above?!
    Tuesday, May 17, 2011 8:04 AM
  • It seems that you don't need VBA to achieve this. Format the range as text first and use "Protect Sheet" to stop users to provent users to change the formact.
    qp Cheng
    Tuesday, May 17, 2011 12:50 PM
  • Hi everyone,

    I hope you can help. I have a spreadsheet that is used as part of a file import process using SSIS and SQL Server. The spreadsheet has a number of date fields/columns formatted as Date (Type:*DD/MM/YYYY, Locale: English (U.K.). My question is can someone help me out with the VBA code that;

    a) OnOpen and OnClose of the spreadsheet, those specific cells will be changed to Text datatype? Something like the following; =TEXT(<cell value>, "DD/MM/YYYY"). Basically I want all the data except for the header row to be stripped of any formatting Excel has chosen and forced to be changed to TEXT.

    b) When the spreadsheet is opened, the option to change the formatting of any cells is disabled. The file is basically a template, so all I want the users to be able to do is to enter data and to save the spreadsheet. I don't want them editing any panes, windows, adding/removing columns etc.

    Your help will be much appreciated. Thanks.

    Hi,

     

    I think the simple way that converts date to text is creating an add-in. You might have known that Excel is unable directly convert datetime to string, which like .ToSring() method in .NET. Furthermore, if you try to use formula, the formula requests a cell to display the result, and cell is not recommend to the same cell of value cell. When you use the TEXT method display the result in a cell which is just a value cell, the 1900-01-00 will be shown.

     

    I’m doing some research on this question via VBA. However, creating an add-in really make the problem very simple.

     

     

    As the second request of protecting worksheet style, you can just use protect worksheet feature. Please be in mind that before you protecting the worksheet, you can set some special ranges to all users to do some specific actions. Please follow the steps below:

    1. Select the worksheet you want to protect;

    2. Review tab -> Allow Users to Edit Ranges -> New -> Select a range which you want to be specific protected -> Ok -> Protect Sheet

    3. Allow some actions in the list. You should check all the items except the Format cells

    4. Ok

     

    Done

     

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 17, 2011 1:26 PM
    Moderator
  • Hi and thank you for your response.

    Preventing the users from changing the format by right clicking on the worksheet surely won't stop users from copying & pasting data (date field for example) from another worksheet/workbook into my workbook, and subsequently changing the format from TEXT to DATE again.

    In light of the above, is there any way then to create VBA that if a user does copy data from else where that it would do a PASTESPECIAL as TEXT?

    Thanks in advance, grateful for your time.

    Wednesday, May 18, 2011 8:38 AM
  • Hi Calvin and thanks for your response. Currently I have this routine which is called at Workbook_BeforeClose;

    Sub FormatCellsAsText()

    Dim MyFormula As String
        Sheets("Sheet1").Unprotect userinterfaceonly:=True
        For Each MyCell In Worksheets("Input - Corrections").Range("DateFields3").Cells
            If Not (MyCell.Value = vbNullString) Or MyCell.Value <> "" Then
                MyFormula = "=Text(""" & MyCell.Value & """,""DD/MM/YYYY"")"
                MyCell.Value = MyFormula
            End If
        Next MyCell
    End Sub

    ...seems to be doing the trick. But I like the idea that you and Cheng are suggesting, which is to modify the template so that the format of the cells in the range are TEXT, then protect the worksheet so that users are unable to change the format again. Again, my only problem with that is how do I write the code to prevent users from copying and pasting data from elsewhere into the workbook that could change the format. i.e. do you know how I can ensure that any data which is copied and pasted will always be PASTESPECIAL TEXT?

    Thanks in advance for your time and help.

    Wednesday, May 18, 2011 8:43 AM
  • ...seems to be doing the trick. But I like the idea that you and Cheng are suggesting, which is to modify the template so that the format of the cells in the range are TEXT, then protect the worksheet so that users are unable to change the format again. Again, my only problem with that is how do I write the code to prevent users from copying and pasting data from elsewhere into the workbook that could change the format. i.e. do you know how I can ensure that any data which is copied and pasted will always be PASTESPECIAL TEXT?

     

    Hi wattyjnr,

     

    Good work!

    The code also works fine on my side.

     

    When you paste data into a worksheet, you can choice which type to paste in Paste Special: Paste formulas or Paste value…

    When we paste date using right clicking, we can easily choice it from the list. However, by default, when we paste data using shortcut key (Ctrl + v), the value and the formatting will be paste. So we can create a macro to implement the paste value feature and make the macro’s shortcut key as Ctrl + V to “overload” the shortcut key.

     

    1. Record a macro;

    2. In the dialog, make the shortcut key as Ctrl + v

    3. Perform the copy and paste value actions

    4. Stop the macro

    5. Open the macro, and delete the code we don’t need. Please check the code follow:

     

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+v
    
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Sub
    

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 18, 2011 9:35 AM
    Moderator
  • Hi,

     

    Have you solved the problem? Have you tested the above suggestion? If you come back and have any further concern, please feel free to let me know. I’ll follow up with you.

     

    Have a nice day,


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 23, 2011 8:47 AM
    Moderator