Excel macros

[Joanne]

I am running these two macros on a spreadsheet designed for label
merging with Word - using Office 2003, Win XP Pro

The macros run just fine and do what is expected, but what happens is
that when I run the macro, it immediately shows the file in proper
case, then the pointer/hourglass vibrates for a few seconds as if some
processing is being accomplished, then the second macro shows itself
as completed, again almost instantaneously.

My question is what is going on during the processing, is the program
writing all the changes after it shows them, or what? I'm simply
curious because the more I know what is happening the better I
understand the coding in vba.

Here is the coding for the macros:

Public Sub ProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
Call FixStates
End Sub

Public Sub FixStates()
Cells.Replace What:=" Il ", Replacement:=" IL ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:=" Mn ", Replacement:=" MN ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

One more question Please. I can't figure out how to put buttons on my
command bar for these two macros - not as straight forward as it is in
Word. Could you please direct me to an article explaining the
procedure.

Thanks a million
Joanne

 

[Bernie Deitrick]

Joanne,

Add this to the top of you code:

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
End With

and this to the bottom

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

As for adding the button, put your code into your Personal.xls, then visit:

http://www.mvps.org/dmcritchie/excel/toolbars.htm

and look for

"Add or delete a toolbar button"

HTH,
Bernie
MS Excel MVP