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

Posted on 2012-09-03
Last Modified: 2012-09-03
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?
Question by:sjgrey
    LVL 10

    Expert Comment

    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

    LVL 1

    Author Comment

    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.

    LVL 10

    Accepted Solution

    Yes, Excel is making suggestions as to the input (and those suggestions are not the least bit helpful for your use case).

    Try adding this code to the worksheet:
    Dim saveDisplayFormulaAutoComplete As Boolean
    Private Sub Worksheet_Activate()
        saveDisplayFormulaAutoComplete = Application.DisplayFormulaAutoComplete
        Application.DisplayFormulaAutoComplete = False
    End Sub
    Private Sub Worksheet_Deactivate()
        Application.DisplayFormulaAutoComplete = saveDisplayFormulaAutoComplete
    End Sub

    Open in new window

    LVL 1

    Author Closing Comment

    Perfect thanks, just what I needed

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now