Macro Doesn't Function on Multiple Sheets in Group Mode (126313)

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To work around this behavior, you must loop through each sheet in the selection individually in your procedure or script to perform the action as in the following examples. (Note that the following examples use the Spelling command to demonstrate the workaround. These examples can be modified to work with other commands.)

Sample Visual Basic Procedure

   Sub SpellCheck()

      ' Dimension variable x as Object type
      Dim x as Object
      ' Group select Sheet1, Sheet2, and Sheet3 worksheets.
      ' Note that there are other methods for selecting multiple sheets.
      Sheets(Array("sheet1", "sheet2", "sheet3")).Select

      ' Perform spell check on each selected sheet.
      ' Note that the Change All button will only change
      ' words on one worksheet, but the spell checker dialog
      ' will appear once for each sheet selected.
      For Each x In ActiveWindow.SelectedSheets
         x.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
            IgnoreUpperCase:=False, AlwaysSuggest:=True
      Next x

   End Sub
				

NOTE: The following example selects the worksheets, one at a time, that you want to spell check (Sheet1, Sheet2, and Sheet3 in this example) in the active workbook in Microsoft Excel, and spell checks each sheet individually.

 Sub XL_SpellChk()
      ' Dimension variables
      Dim xl As Object, n As Integer, wksheet()
      ' Dimension wksheet variable as array with 3 elements
      ReDim wksheet(3)
      ' Assign worksheet you want to spell check
      ' as elements of wksheet array
      wksheet(1) = "sheet1"
      wksheet(2) = "sheet2"
      wksheet(3) = "sheet3"

      ' Activate Microsoft Excel and assign to variable xl
      Set xl = GetObject(, "Excel.Application.5")

      For n = 1 To 3
         ' Display Spell Checker dialog, once for each sheetname in array
         xl.Sheets(wksheet(n)).CheckSpelling "CUSTOM.DIC", False, True
      Next n

      Set xl = Nothing
   End Sub