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 |
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 |
All times are GMT +1. The time now is 11:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com