+ Reply to Thread
Results 1 to 6 of 6

Insert Row Macro - runtime error

  1. #1
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Insert Row Macro - runtime error

    Hi there,

    I'm trying to call a subroutine to insert a row in a table then add a new row to two other sheets in the same workbook. The new rows in the workbooks keep giving me a runtime error, however if I insert them into a separate module then they run ok. Any ideas what I am doing wrong?

    Please Login or Register  to view this content.
    Thanks in advance

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Insert Row Macro - runtime error

    What error and on which line(s)?
    Also, is this code in a normal module or a worksheet code module? (I suspect the latter)
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Insert Row Macro - runtime error

    Hi there,

    You got me thinking.

    The code was in a worksheet module. So I was trying to activate another worksheet from a worksheet module, which was causing the error. I just pasted it into a normal module and it worked fine.

    Thanks for the pointer!

    Rob

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Insert Row Macro - runtime error

    You can activate another sheet from a worksheet module (though there is rarely any need to) but any reference to Range that is not qualified with a worksheet will refer to a range on the sheet containing the code, not the active sheet. You can simplify to:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Insert Row Macro - runtime error

    Excellent, it works perfectly.

    Thank you!

    As you can see I'm still learning VBA and I'm a bit clumsy at the moment.

  6. #6
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Insert Row Macro - runtime error

    And now I'm back

    The 'savings' sheet on this report is hidden upon closing and autosaved. If the user wants to see the sheet, the worksheet_activate event triggers a userform with a password prompt. The problem with this is that if you go to another sheet and then return to the 'savings' sheet, the password userform fires again. I want the code to look whether the sheet has been unhidden and if so end the sub before calling the userform. I must be doing something stupid. Sorry, I haven't used if statements before.

    This is the original code.

    Please Login or Register  to view this content.
    And this is my attempt at fixing it.
    Please Login or Register  to view this content.
    Please can anyone advise my schoolboy error?

    I promise to learn from it!

    EDIT

    I think my problem is using the worksheet_activate event as this will make the sheet visible. I need a before activate event lol.....
    Last edited by radddogg; 10-25-2011 at 08:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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