Can't see macro

[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

 

[Frank Kabel]

Hi Joe
where did you put the macro?. It has to go into a stantdard module (not
into 'ThisWorkbook')

 

[lunker55]

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

 

[Ron de Bruin]

Hi

I think you have private before the macro name

 

[Frank Kabel]

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

change this to
Public sub foo()

 

[Gord Dibben]

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