+ Reply to Thread
Results 1 to 7 of 7

Adding a button to a simple macro

  1. #1
    Registered User
    Join Date
    11-08-2019
    Location
    BC, Canada
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Adding a button to a simple macro

    Hello

    I'm completely new to macros. I made a macro (mostly with the recorder tool) to:

    1) Copy the current worksheet
    2) Move a selection of data down one row on the new worksheet
    3) Delete a row of data on the new worksheet

    It works when I run it. I'm trying to add a button which will just run the macro once when I click the button, but I'm getting a "run-time error '1004': Select method of Range class Failed"

    I assumed by wrapping the working macro with the code for the button, it would be enough, but I guess I'm missing something.

    This is the code with no button.

    Please Login or Register  to view this content.
    This is the same code, but with the button.

    Please Login or Register  to view this content.
    I'm sure its all very sloppy, I'll be starting tutorials after this so that I'm not running in circles, but would appreciate some help with this.

    Thanks

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Adding a button to a simple macro

    Hi newb and welcome to the forum,

    Leave your macro in a Module and "Call Macro7" from the Click event.
    The idea is that Macros behind sheets should only work on that sheet. By putting them in a Module they can work on all the sheets and on forms and ….

    Here is the idea... Scope also applies to variables but that isn't what I mean.
    https://www.excelcampus.com/vba/code...nt-procedures/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    11-08-2019
    Location
    BC, Canada
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Adding a button to a simple macro

    Thanks that worked!

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Adding a button to a simple macro

    Depending on your sheet layout,
    Would this
    Please Login or Register  to view this content.
    not do the same as this?
    Please Login or Register  to view this content.
    and this
    Please Login or Register  to view this content.
    not do the same as this?
    Please Login or Register  to view this content.
    and this
    Please Login or Register  to view this content.
    not do the same as this?
    Please Login or Register  to view this content.
    If it does, it would be less wear and tear on the fingers.

  5. #5
    Registered User
    Join Date
    11-08-2019
    Location
    BC, Canada
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Adding a button to a simple macro

    I'm not sure. Does "Insert Shift:=xlDown" move the cell contents down a row? Because I only want to move the cell values down 1.

    Cells I2:M2 contain formulas that spit out a value based on the cell below it. Eg. I2=I3+2.5

    So when I create a new sheet with the macro, the value in the old sheet I2 becomes the value in the new sheet's I3. It becomes 2.5 bigger than it was in the previous sheet.

    But as for the rest.. thanks that looks great! I just started some tutorials and am realizing how messy the record function is.

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Adding a button to a simple macro

    As the first line reads: "Depending on your sheet layout"
    First off, just try it and see what happens. If it is not right, close the Workbook without saving. No harm done.
    Or
    Try it on a copy of your original Workbook. Again, no harm done.
    BTW, you should ALWAYS try suggested code on a COPY of your original. There is no "Undo" possibility for code.
    If you want to continue with this code you'll have to explain exactly what you want to have happen but the best thing still is to try, check the result and if it is not what it needs to be ask here for a solution. Don't save the workbook as mentioned previously.

  7. #7
    Registered User
    Join Date
    11-08-2019
    Location
    BC, Canada
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Adding a button to a simple macro

    Yes, it doesn't work with my layout. But your suggestion would actually be a much better way of accomplishing what I'm trying to do, so I'll definitely be using it later after I finish some more tutorials. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simple Macro To Do Button
    By Gblack686 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2016, 09:59 PM
  2. Simple macro button-Please Help
    By wls485 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-10-2014, 01:02 PM
  3. Simple macro button to create data - Please help
    By brandonlbrewer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-14-2014, 03:11 PM
  4. adding a button for simple generation
    By andylang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2013, 07:13 AM
  5. [SOLVED] Enable an ActiveX macro button (Should be simple)
    By Gard5096 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-05-2012, 03:48 PM
  6. Simple Open Macro Button
    By richard05 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2009, 07:34 AM
  7. Help with simple macro button
    By IowaTracy in forum Excel General
    Replies: 3
    Last Post: 08-27-2006, 01:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1