How to get this marco going?

B

Brett

I'm using the following sub from here from here
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#RunSubFromActiveCell.

RunSubFromActiveCell (#RunSubFromActiveCell)
This one is kind of like have a command button or a menu item, but
using a cell instead to identify the macro to be run. Created this
one to relate to the macros near here. Most of my macros require a
preselected area first so expect this to be of limited use. Also see
Alt+F8 and MacroDialogBox in my Toolbars page.

Sub RunSubFromActiveCell()
Application.Run ActiveCell.Value
End Sub

Examples of content for Activecell:
mymacro
personal.xls!mymacro

I don't use another workbook (.xls file). Everything is in the same
workbook. I believe the above sub should work with one named
InsertRowsAndFillFormulas()
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#RunSubFromActiveCell.

I click the run button and try to run RunSubFromActiveCell() but get
the message Run-time error '1004':
The macro 'Cell' cannot be found

Both of the above subs are in Module1, a standard module as suggested.
I want a user to insert a row and retain the formulas. I'm not sure
how this should be invoked (button or some other method). Either way,
it isn't invoking. What am I doing wrong?

Thanks,
Brett
 
G

Gord Dibben

Brett

The string "InsertRowsAndFillFormulas"(no quotes) must be typed into a cell.

Click on that cell to make it Active then Tools>Macro>Macros. Select the
macro and Run command.

The "InsertRowsAndFillFormulas" will run at this point.

You could assign the "RunSubFromActiveCell" macro to a Toolbar Button or
whatever.

Gord Dibben Excel MVP
 
G

Gord Dibben

Brett

May be confusing.......

Click on that cell to make it Active then Tools>Macro>Macros. Select the
macro "RunSubFromActiveCell" and Run command.

Gord
 
B

Brett

If the string is typed into the cell, that looks odd. It seems as
though you are not supposed to type any data in that cell. I have a
form created. Certain cells are going to need that string, which
throws the whole form off. Cells that some one should be entering
figures instead have the InsertRowsAndFillFormulas string. Is that
common?

How do I add a button to the toolbar that will activate the
InsertRowsAndFillFormulas string? Will that string still need to be
typed into the cell?

BTW, it does work if I type in the InsertRowsAndFillFormulas string
and use Tools>Macros>Macros. A little cumbersome for users that isn't
familiar with macros.

Thanks,
Brett
 
G

Gord Dibben

Brett


The macro InsertRowAndFillFormulas cannot be run directly because it has an
argument (Optional vRows As Long).

If you don't want to enter the string in a cell, which the macro is designed
for, you can call it from a 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
 
Top