Autocorrect in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day. I am trying to have inputs from a text box, when placed in a cell, trigger the autocorrect features. For example, textbox entry (tm) should apply to the cell as halfcase TM. Am having a devil of a time figuring this one out. Anyone trying to move from Santa's bad list to good list would get extra points!

Thank you.
 
Jeff,

I've managed to get something working using SendKeys. The following code
puts the text in TextBox1 into cell A1 on Sheet1:

Private Sub CommandButton1_Click()
UserForm1.Hide
Worksheets("Sheet1").Range("A1").Value = ""
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Activate
SendKeys "{F2}" & fQuoteString(TextBox1.Value) & "~"
End Sub

Private Function fQuoteString(sStringIn As String) As String
Dim sQuoteChars As String
Dim nChar As Long
Dim sCurrChar As String

sQuoteChars = "[]{}+^%~()"
fQuoteString = ""
For nChar = 1 To Len(sStringIn)
sCurrChar = Mid(sStringIn, nChar, 1)
If InStr(sQuoteChars, sCurrChar) > 0 Then
fQuoteString = fQuoteString & "{" & sCurrChar & "}"
Else
fQuoteString = fQuoteString & sCurrChar
End If
Next nChar
End Function

(the fQuoteString function quotes special characters before passing them to
SendKeys).

Using SendKeys is a bit error-prone, to put it mildly. In particular, this
will only work if the code is run from Excel directly, instead of through
the VBA editor. I don't know if there's a better way...

Hope this helps,
Paul

Jeff Fuller said:
Good day. I am trying to have inputs from a text box, when placed in a
Click to expand...
cell, trigger the autocorrect features. For example, textbox entry (tm)
should apply to the cell as halfcase TM. Am having a devil of a time
figuring this one out. Anyone trying to move from Santa's bad list to good
list would get extra points!
 
Not sure what functions of the autocorrect you are trying to implement

If you are just trying to change the case from lower to upper, you can use the before update features of the text box events, or if you have found a way to activate the autocorrect, then have the autocorrect run on the after update event. Be sure though to have a catch, that if the text in the text box is equal to the autocorrected text, not to update the text box. If the two texts are different, then go ahead and update the text box. If you watch your call stack, you'll see that the after update will be run once, then it will come back into the after update, once the code has assigned the autocorrected text
 
Also if you use the Microsoft Help/"Paperclip" You would find the following when searching "AutoCorrect Object

Contains Microsoft Excel AutoCorrect attributes (capitalization of names of days, correction of two initial capital letters, automatic correction list, and so on)

Using the AutoCorrect Objec

Use the AutoCorrect property to return the AutoCorrect object. The following example sets Microsoft Excel to correct words that begin with two initial capital letters

With Application.AutoCorrec
.TwoInitialCapitals = Tru
.ReplaceText = Tru
End Wit

You should be able to find more if you use the object browser or the AutoCorrect Object help page referenced above, and look at the properties and methods of the AutoCorrect object.
 
Back
Top