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

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

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

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

Interesting, Paul.
I am certified in Office 2000 but using 2003 though certainly not certified
in VBA. I wonder if I write some simple code to look in a specific cell and
if the contents = "S" then replace it with "Satisfactory". I will take what
you found and see if I can figure it out.

"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

Yes, I considered that approach but made the assumption from the way you
wrote about your form that the cells were scattered around the form and not
in one convenient place, like a single column. Writing code to check entries
in individual cells, while certainly possible, is cumbersome.

Are you familiar with the Validation tools built into Excel? If you're just
looking for the best way to do this and aren't married to the idea of coding
it in VBA, you might like Validation better. Check help for information. In
a nutshell, what you do is make a list of the acceptable entries anywhere in
your spreadsheet; usually this would be a hidden column far to the right of
the active data area your users would see. Select a cell (or range of cells)
that you want to apply a validation rule to, and go to menu Data --
Validation... In the "Allow" box select "List" and then use the "Source" box
to identify the range of cells your list of allowable entries is in. Once
you've done this you'll provide your users with 2 benefits:
(1) Only the text you've entered in your list of acceptable entries can be
entered in validated cells.
(2) Excel will auto-complete entries because it knows whether the user has
entered a character that matches one of the acceptable entries.


"Jack_Feeman" wrote:

Interesting, Paul.
I am certified in Office 2000 but using 2003 though certainly not certified
in VBA. I wonder if I write some simple code to look in a specific cell and
if the contents = "S" then replace it with "Satisfactory". I will take what
you found and see if I can figure it out.

"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

Actually, Paul one of my engineers proposed that same solution, but I put it
on the back burner as I felt auto-correct would be faster and more user
friendly that 15 dropdowns, but you present some good points.
I never thought about the inability for a user to write anything he/she
wanted in the cell. So thanks for pointing that out to me. I will use that
approach.
Thanks
Jack

" wrote:

Yes, I considered that approach but made the assumption from the way you
wrote about your form that the cells were scattered around the form and not
in one convenient place, like a single column. Writing code to check entries
in individual cells, while certainly possible, is cumbersome.

Are you familiar with the Validation tools built into Excel? If you're just
looking for the best way to do this and aren't married to the idea of coding
it in VBA, you might like Validation better. Check help for information. In
a nutshell, what you do is make a list of the acceptable entries anywhere in
your spreadsheet; usually this would be a hidden column far to the right of
the active data area your users would see. Select a cell (or range of cells)
that you want to apply a validation rule to, and go to menu Data --
Validation... In the "Allow" box select "List" and then use the "Source" box
to identify the range of cells your list of allowable entries is in. Once
you've done this you'll provide your users with 2 benefits:
(1) Only the text you've entered in your list of acceptable entries can be
entered in validated cells.
(2) Excel will auto-complete entries because it knows whether the user has
entered a character that matches one of the acceptable entries.


"Jack_Feeman" wrote:

Interesting, Paul.
I am certified in Office 2000 but using 2003 though certainly not certified
in VBA. I wonder if I write some simple code to look in a specific cell and
if the contents = "S" then replace it with "Satisfactory". I will take what
you found and see if I can figure it out.

"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