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

Posted on 2012-09-03
Medium Priority
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
  • 2
  • 2
LVL 10

Expert Comment

ID: 38362247
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


Author Comment

ID: 38362254
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

tdlewis earned 2000 total points
ID: 38362276
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


Author Closing Comment

ID: 38362304
Perfect thanks, just what I needed

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

580 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