Can't see macro

  • Thread starter Thread starter lunker55
  • Start date Start date
L

lunker55

I am experimenting with a macro from David McRitchie's page:
I create a custom button, and when I try to assign the macro, it is not in
my options.
It is in the workbook.
What am I doing wrong?
All the other macros are there.

Joe



Sub Insert_Rows_And_Fill_Formulas(Optional vRows As Long)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]>
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub
 
Hi Joe
where did you put the macro?. It has to go into a stantdard module (not
into 'ThisWorkbook')
 
Yes Frank, it is in a module. The other macro in the module I can see. Just
not this one.
Do I have to get at it a different way?

Joe
 
Hi
probably you have declared your function as private. e.g.
Pivate sub foo()

change this to
Public sub foo()
 
Joe

The macro InsertRowAndFillFormulas cannot be seen in Tools>Macro>Macros or run
directly because it has an argument (Optional vRows As Long).

You can call it from another Sub directly.

Sub RunInsertRowsSub()
Call InsertRowsAndFillFormulas
End Sub

Go to View>Toolbars. Open the Forms Toolbar. Select Button Icon and create a
button on your worksheet by dragging the cross to fit. Right-click and
"Assign Macro".

Select "RunInsertRowsSub" as the macro to assign.

This macro will run the InsertRowsAndFillFormulas macro.

If you want a button on a Toolbar, go to Tools>Customize>Commands. Scroll
down to "Macros" and drag the Smiley Face button to your Toolbar. Right-click
on that and "Assign Macro".

Note the other options for editing and changing the Icon image.

Gord
 
Back
Top