asked on

Enter leading hyphen or plus sign as text in Excel without using apostrophe

I have a spreadsheet that is created and populated from Word using VBA. There are five data values in the spreadsheet taken from the Word file that created the spreadsheet and these will often contain strings such as "+2d" or "-5wk" meaning plus two days or minus five weeks.

When the values are of this form, the VBA formats the cells as Text and the initial data are placed in them as expected.

However, I want the user to be able to edit the cell values in Excel. It's possible to make entries with no leading sign, such as "3wk" but if there is a leading plus or minus sign, as soon as I enter a letter, Excel pops up a menu of special formats to do with dates, data bases, degrees and other things.

Can I use VBA to fix it so that entries are made as strings without the user having to precede them with an apostrophe?

tdlewis

I don't think there is any way to intercept the user's input in this way. You can add code to run when a cell changes or when calculations occur, but there is no event for responding to user keystrokes.

If you set the format of the cells where users will enter things like "-3wk" to "Text" then Excel validation will accept that input without the preceding '.

Note that after Excel validation has accepted the user's input, you can use a Worksheet_Change routine to alter the user's input. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Right(Target.Value, 3) = "wks" Then
        Target.Value = Replace(Target.Value, "wks", "wk")
    End If
End Sub

Open in new window

sjgrey

ASKER

I had already set the format to Text, using VBA, but I just tried it manually, set the format using menu commands, and the screen capture attached shows what happens. Perhaps this is a 2010 version thing. Excel is trying to be too clever.

Text-entry-problem.docx

ASKER CERTIFIED SOLUTION

tdlewis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

Perfect thanks, just what I needed