Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 "Joanne" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Making excel macros run Word macros | Excel Programming |