Share via

Macros for dummies

Anonymous
2022-10-21T21:26:29+00:00

So I'm new to the world of macros. I'm competent in excel with formulas and such but when it comes to macros I seem to not be getting it.

I recently posted a plea for help with creating a macro to insert copied cells below an active cell was shown to use the below macro:

Sub Add_Row()

    Dim i As Long

    i = Application.InputBox("How many copies?", Type:=1)

    ActiveCell.Offset(1).Resize(i).EntireRow.Insert

    ActiveCell.EntireRow.Resize(i + 1).FillDown

End Sub

It work exactly as I wanted. The problem now is I am making a base template worksheet that co-workers will use. Upon opening the worksheet, they will 'Save As' to a specific folder for that project. But when I try to use the macro, it is opening the base template workbook in the background after giving me an error stating "Automatic update of links has been disabled...etc." basically telling me beware of nefarious content from shady people on the internet, which I'm pretty sure I am not.

In an effort to eliminate user error, I started over from scratch on a blank worksheet and before anything, tried using the macro (after re-recording/editing it), clicked the button to run and IT OPENED THE VERY FIRST WORKSHEET AGAIN.

Please help...and feel free to explain it in a way that a 8 year old would understand.

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-10-24T12:37:53+00:00

    If you created the custom ribbon tab in File > Options > Customize Ribbon, the custom button will be bound to the macro in the original workbook.

    But if you create a command button on the worksheet (Developer tab of the ribbon > Insert > Button (Form Control) and assign your macro, the button will execute the macro in the current workbook if you create a copy or a new workbook based on the original one.

    Alternatively, you'd have to use Ribbon XML, which is a lot more complicated.

    0 comments
  2. Anonymous
    2022-10-24T11:47:58+00:00

    I did assign the Macro to a custom button on a custom ribbon tab. Would that be my problem? And if so, How would I work around that? because that is the whole reason for the macro: essentially my sheet is for ordering material, on the base sheet I have 1 of every component listed and when multiple of the same component are needed (in different lengths or gauges), id like the button to add a specified number of lines.

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-10-21T21:29:39+00:00

    Did you assign the macro to a command button on the worksheet? Or to a custom button on the Quick Access Toolbar?

    0 comments