MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Recorded Macro gets Run Time Error


Posted by chafee on December 20, 2000 8:11 AM

Excel 2000
Automating the show or hide details of an outline.

I used the Data...Group and Outline...Group function in Excel to develop an outline. I then recorded the Data...Group and Outline..."Show Detail" and "Hide Detail" functions using Record Macro. Steping through the macro a Run time Error 1004 "Entered to many arguments for this function" appears. The recorded macro includes only:
Sub Macro1()
Range("A21").Select
ExecuteExcel4Macro "SHOW.DETAIL(1,20,FALSE,,31)"
ExecuteExcel4Macro "SHOW.DETAIL(1,20,TRUE,,31)"
End Sub

Any ideas how to make this work.

tks
Steve


Posted by cpod on December 20, 2000 1:10 PM


Try:

Range("A21").Select
ExecuteExcel4Macro "SHOW.DETAIL(1,20)"
End Sub

You should need only one macro. If you leave out the true and false the macro toggles between expand and collapse.

Posted by chafee on December 21, 2000 6:33 AM

Posted by chafee on December 21, 2000 6:56 AM

Tried the above. But this macro did not cause anything to happen on the spreadsheet, nor did it cause an error. I tried several different variations, but nothing happened. Also looking through Excel help, the following works, but is very limited:
Worksheets("sheet 1").Activate
Set myRange = ActiveCell.CurrentRegion
lastRow = myRange.Rows.Count
myRange.Rows(lastRow).ShowDetail = True

Would like to make the code you suggestd work since it is more general and should toggle as required.
any suggestions?
tks
Steve