Okay, I get it! I see why you're baffled. I ran your code and it added the
entries to the AutoCorrect list when I checked it in Excel, but the presence
of the entries in the list didn't result in those corrections being made when
I entered the letters in cells. But there was an inconsistency: one
AutoCorrect entry, which I had made using the Immediate window in the
VB
editor, DID work. I finally figured out the difference: the AutoCorrect
entries weren't working with the workbook I already had open when the entries
were added to the AutoCorrect list. If I opened a new workbook, all of them
worked in the new workbook. I don't know if this will be of any help, because
I really don't understand why the AutoCorrect entries would work in a new
workbook, but not one that was open when the code ran. By the way, we're
still on Excel 2000 here and you never know what slight differences there
will be between the way the versions work.
"Jack_Feeman" wrote:
Thanks for such an expeditious reply, Paul.
I fear I used an ambiguous term when I said form. The entire worokbook is
the form and where I would like the auto-correct to kick in is in cells where
the user has to evaluate someone's performance. So if VBA can be used in a
specific cell to Replace the capitol "S" with "Satisfactory" etc. (the cell
is the cell which the evaluator places the evaluation (such as
"Satisfactory", "Unsatisfactory", etc.). There are quite a few evaluation
cells in the workbook and I just wanted to make the user experience a bit
less tedious by allowing them to enter the initial letter of each of the five
ratings and the code would enter the whole word for them.
Hope this clarifies it. Thanks
Jack
" wrote:
Jack, I'm not certain I understand what you're trying to do. Your code uses
the AddReplacement method of the AutoCorrect object. I've never used the
AutoCorrect object in code so I'm not intimately familiar with the object and
its features (at least from a programming perspective), but what I believe
your code does is add to the list of strings that Excel is to replace when
the user types them into worksheet cells. If this code runs successfully
every time the user enters a U, S, M or E in a cell (NOT in your form) Excel
will change their entry to the word you've specified.
If you want these individual letters to automatically populate a control
like a TextBox, use the TextBox_Change event procedure, something like this:
Sub YourTextBoxName_Change
If YourTextBoxName = "S" Then
YourTextBoxName="Satisfactory"
ElseIf YourTextBoxName = "U" Then
YourTextBoxName="Unsatisfactory"
EndIf
End Sub
Just add as many "ElseIf"s as you need to accommodate all the letters you
want to provide automatic population for.
"Jack_Feeman" wrote:
I have created an Evaluation Form in Excel 2003. For expeditious handling and
ease of use, I created an auto-correct function to replace lets say "S" with
"Satisfactory", "U" with Unsatisfactory, etc. (see actual code below).
However, it does not go with the workbook so I assume VBA is the way to do
it. However, even my attempt in VBA does not produce the desired results
either (I must be doing something wrong). Please help me straighten out the
code. Thanks
Code extract:
Private Sub Workbook_Open()
Splash.Show
End Sub
Sub autocorrect()
'
' autocorrect Macro
' Macro recorded 9/5/2007 by Jack Feeman
'
'
Application.autocorrect.AddReplacement What:="U", Replacement:= _
"Unsatisfactory"
Application.autocorrect.AddReplacement What:="S", Replacement:= _
"Skill Needs Development"
Application.autocorrect.AddReplacement What:="M", Replacement:= _
"Meets Expectations"
Application.autocorrect.AddReplacement What:="E", Replacement:= _
"Exceeds Expectations"
With Application.autocorrect
.TwoInitialCapitals = True
.CorrectSentenceCap = True
.CapitalizeNamesOfDays = True
.CorrectCapsLock = True
.ReplaceText = True
.DisplayAutoCorrectOptions = True
End With
End Sub
" End code extract.
I see the splash displays all right when an user opens it but the
auto-correct sub doesn't work.
Thanks again
Jack