Excel VBA用户窗体长连字符输入及后端处理技术问询

Great question! Let's tackle this for your Excel VBA user form—whether you want an easier input method for users or a backend fix to handle string matching, we've got you covered.

一、更简便的长连字符（En Dash, Chr(150)）输入方式 #

Alt+0150 works, but it’s not the most user-friendly if your users don’t remember the numeric code. Here are two simpler options:

1. Excel自动更正（推荐给普通用户） #

Set up an AutoCorrect rule so users can type a familiar shortcut and get the long dash automatically:

• Open Excel → Go to File > Options > Proofing > AutoCorrect Options
• In the "Replace" field, type -- (two short hyphens)
• In the "With" field, insert the en dash by pressing Alt+0150 once
• Click "Add" then "OK"

Now, whenever users type -- in your user form’s text box, it’ll instantly replace with the long en dash. No memorizing codes required!

2. Windows输入法快捷方式（针对Windows用户） #

If your users use Microsoft’s default input methods (like Microsoft Pinyin), they can:

• Type en dash and select the character from the suggestion list
• Or type -- then press Space (most modern input methods support this quick conversion)

二、VBA后端处理方案（如果输入方式不可行） #

If you’d rather handle the conversion programmatically (so users don’t have to change their input habits), use VBA to normalize hyphens either as users type or before string matching.

Example 1: Auto-convert short hyphens to long en dash in the text box #

Add this code to your user form’s text box event to convert - to Chr(150) when the user finishes typing:

Private Sub TextBox1_LostFocus()
    ' Replace all short hyphens with en dash (Chr(150))
    TextBox1.Text = Replace(TextBox1.Text, "-", Chr(150))
End Sub

Example 2: Normalize hyphens for consistent string matching #

If you need to compare user input against a target string (regardless of hyphen type), create a helper function to standardize all hyphens to one type:

Function NormalizeHyphens(inputStr As String) As String
    ' Convert en dash (150) and em dash (151) to short hyphen, or vice versa
    NormalizeHyphens = Replace(Replace(inputStr, Chr(150), "-"), Chr(151), "-")
    
    ' To convert ALL hyphens to en dash instead, use this line:
    ' NormalizeHyphens = Replace(Replace(inputStr, "-", Chr(150)), Chr(151), Chr(150))
End Sub

' Use it in your matching logic:
Private Sub CommandButton1_Click()
    Dim userInput As String
    Dim targetString As String
    
    userInput = NormalizeHyphens(TextBox1.Text)
    targetString = NormalizeHyphens("Sample Text–With En Dash") ' Your target value
    
    If userInput = targetString Then
        MsgBox "Match found!"
    Else
        MsgBox "No match."
    End If
End Sub

This way, even if users type short hyphens, long en dashes, or even em dashes, the comparison will work consistently.

内容的提问来源于stack exchange，提问作者Student of the Digital World