Tough One - Macro to create macro

[elayem]

I don't even know if this is possible, but bare with me and please let me know if it is:

I am working on a form that I may need to insert rows to duplicate the same form (I have a lot of other sheets, so duplicating sheets is not an option). For example, I would have a form with information form A1 to C10. I would then have a macro that can insert 10 rows with the same information from A1 to C10. That is not the problem.

Currently, within the form, I have a hide row and show row macro. E.g.,

Sub Hide()
    Rows("3:5").Select
    Selection.EntireRow.Hidden = True
End Sub
Sub Show()
    Rows("2:6").Select
    Selection.EntireRow.Hidden = False
End Sub

This would be linked to 2 buttons that will automatically hide/show the row when clicked upon.

Is there anyway that the form and buttons with the hide/show row can be duplicated upon execution of another macro? In essence, there needs to be new rows inserted, the form pasted, and the buttons reproduced but referencing the correct rows to show/hide. There's no such thing as a relative reference for this, correct?

The end result would be a macro that from A1 to C10, can insert 10 rows, and have a new A1 to C10, the original A1 to C10 form would be displaced to A11 to C20, and all the show/hide macros would work. Then if another form needs to be created, 10 more rows inserted from the macro, and so forth.

Sorry for the lengthy explanation. Any help would be much appreciated. Thanks!

 

[AlphaFrog]

You can have a macro create a new macro.
Extensibility in VBA

Another way would be to ...

• Insert new rows and buttons (a new form).
• The new buttons call the same two macros as the original buttons.
• The two Show\Hide macros will show\hide rows relative to the location of the button that called the macro.

Example:

Two Forms-type buttons: "Hide" is in cell A3, "Show" is in Cell A6. The buttons are linked to the macros Hide And Show

This copies rows 1:10 and inserts the copied rows above. This will copy the buttons as well.

Sub Insert_New_Form()
    Rows("1:10").Copy
    Rows("1:1").Insert Shift:=xlDown
    Rows("1:10").ClearContents
    Application.CutCopyMode = True
End Sub

Using the Application.Caller function to determine what button called the macro, the Hide and Show macros use the position of the button that called them to determine what rows to hide and show

Sub Hide()
    ActiveSheet.Buttons(Application.Caller).TopLeftCell.Resize(3).EntireRow.Hidden = True
End Sub

Sub Show()
    ActiveSheet.Buttons(Application.Caller).TopLeftCell.Resize(3).Offset(-3).EntireRow.Hidden = False
End Sub

You may have to play with the .Offset to select the rows you want to show\hide relative to your button positions.

 

[elayem]

Hi, thanks for the quick response.

I'm starting from scratch, and I'm getting this error:

Run-time error '1004':

Unable to get the Buttons property of the Worksheet class

What am I doing wrong?

 

[AlphaFrog]

There are two types of command buttons. The ActiveX buttons from the Control Toolbox toolbar and the CommandButton from the Form toolbar. The code is intended to work with CommandButtons from the Form toolbar.

Overview of forms, Form controls, and ActiveX controls on a worksheet

 

[elayem]

Thank you so much. Runs beautifully!