+ Reply to Thread
Results 1 to 12 of 12

Print Macro / module error & Update / Clear Macro error

  1. #1
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Print Macro / module error & Update / Clear Macro error

    Hi Guys

    I have written the macro module 1 for the print preview............ however I only want it to show columns and rows A1 to K48, but somehow I seem to have gone with this

    Please Login or Register  to view this content.
    My next issue is the Post to summary and clear invoice............. i have the code written into the invoice sheet with VBA but nothing is happening when I click on the button...........
    Any pointers welcome
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    I'm not sure what you're doing, but:
    Please Login or Register  to view this content.
    Why are you searching for active?
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Re: Print Macro / module error & Update / Clear Macro error

    Many thanks for your reply
    and your solution sorted the first problem but still cant get the second issue resolved

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    1. Line 4 of your code, vis. Exit Sub, causes the macro to immediately terminate. Nothing below that line will ever run.
    2. Your last line is: Sheets("Summary").Select, however, you have no such sheet. You have "Invoice Summary".
    3. lst tries to find the last used line in the invoice summary sheet, but you have created a table7 for the range A1:I15 so that the next row will always be #16, not #2. You will have to delete that table definition (i.e., convert to range).

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Re: Print Macro / module error & Update / Clear Macro error

    I have done what has been suggested however I now encounter that when I hit the post to summary and clear invoice I loose some of the formula's from my invoice sheet............ why is this happening and how can it be resolved to prevent this...

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    ... I loose some of the formula's from my invoice sheet...
    That's not specific enough. The line: Range("g10,a16:h37").ClearContents clears cell G10 and all the cells in the block A16:H37. If you have any formulas in that range, then you will have to modify the addresses, something like: Range("G10,A16:B37,D16:D36, ..."), listing the blocks of cells you want cleared separated by commas.

  7. #7
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Re: Print Macro / module error & Update / Clear Macro error

    Sorry for not reply sooner been away .........

    I have tried your suggestion and I'm still having the same problem...........

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    What is the current macro code?

  9. #9
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Re: Print Macro / module error & Update / Clear Macro error

    Please Login or Register  to view this content.
    cells j4, j5 &i10 clear all formulas?

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    cell I10 has a lookup that depends on G10, G10=blank then I10=blank. But the formula is not deleted.
    J4 & 5 did not go blank when I ran the code.

  11. #11
    Registered User
    Join Date
    07-24-2011
    Location
    belfast
    MS-Off Ver
    office 365
    Posts
    61

    Re: Print Macro / module error & Update / Clear Macro error

    When i hit the update button on mine it clears J4,J5 and I10 of the formulas, once I select a new customer in G10 it doesnt bring across the formulas and when i look for them they have disappeared................

    I have attached the current workbook for you to have a look at if this is any help?
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,984

    Re: Print Macro / module error & Update / Clear Macro error

    I think part of the problem is your worksheet change code. For instance,
    Please Login or Register  to view this content.
    on the sample worksheet B2:F14 are blank. Also,
    Please Login or Register  to view this content.
    Etc.
    You can correct part of the problem by turning off Event Handling in Button1 Click code:
    Please Login or Register  to view this content.
    You will have to carefully check your Change Event code, it should not be operating on cells like J4 that contain formulas. Also, you must turn off the Event Handling in the Sheet change code, if not every change made by the code will trigger the code to run from the top again and again.
    EDIT:
    Notice that, the way you have written your change event code, each and every if..end if statement block is checked for true/false every time the code is triggered. If you use SELECT CASE, the code will exit after the true case code is executed. Nothing below that one will be tested:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 09-28-2012 at 07:07 PM. Reason: added suggestion

+ 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