+ Reply to Thread
Results 1 to 16 of 16

Combine functions of 3 Buttons into 1

  1. #1
    Registered User
    Join Date
    11-20-2007
    Posts
    33

    Combine functions of 3 Buttons into 1

    Hi All

    In the attached xls I have a button on each tab, ideally I would like the button on the first tab to do everything, but I cannot seem to get it to work hence the current layout.

    First button on first tab creates new xls and copies content of 2nd tab onto it, then copies various bits of info from certain cells in tab 2 into the 3rd tab.

    2nd button on second tab simply clears the contents of the form, which is no longer needed as a seperate copy has just been created.

    3rd button on 3rd tab just tidys up the cell borders

    It would be much easier if I could get one button click to do everything (in that order preferably).

    Any Ideas?

    Thanks
    Attached Files Attached Files
    DrEvilAces

    If life is like a box of chocolates, then my boss is the coffee flavoured one!!

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    As I said in your last post, you need to tidy your existing code by removing all the unnecessary selecting. You should also remove the empty modules.
    Please Login or Register  to view this content.
    To run the three codes one after the other you need to specify which sheet they work on, see the above code.

    Please Login or Register  to view this content.
    The tidydiary macro needs to cleaning up.

    However, your tables world be much better and easier to maintain if you combined them into one with a column to show staus - ongoing, etc. You could then filter by this column. Your tidycode could then be simplified
    Last edited by royUK; 12-17-2008 at 05:42 AM.
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    As I said in your last post, you need to tidy your existing code by removing all the unnecessary selecting. You should also remove the empty modules.
    Please Login or Register  to view this content.
    To run the three codes one after the other you need to specify which sheet they work on.

    Please Login or Register  to view this content.
    The tidydiary macro needs to cleaning up.

    However, your tables world be much better and easier to maintain if you combined them into one with a column to show staus - ongoing, etc. You could then filter by this column. Your tidycode could then be simplified
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    Hi guys thanks for the help on this but I'm struggling a bit here!

    First thing I did was remove the empty modules as suggested, next up I replaced the code in the second tabs button from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

    And replaced module 6's code with
    Please Login or Register  to view this content.
    as you suggested. But unfortunately when I run that code (click the button) I get a 'Run Time Error 450 - Wrong number of arguments or invalid property assigned ' and it highlights all the new code in Module 6.

    I placed the entire new section of code you posted into the button code on the first tab (as well as updating module 6). When it is clicked the new xls is created but both module 5 & 6 try to run on the newly created xls rather than the original, it also stops with the same runtime error as above.

    I think this might be because the tab on the new xls is also called Tender Debrief. Is it possible to rename the new xls tab to Tender Debrief COPY within the macro?

    As for combining the three tables into one, it was something I had considered, however, only tenders won or lost will have been through a debrief, those ongoing or out of strategy haven't. Eventually those ongoing will end, a debrief completed and then added to the table above, and removed from ongoing. As for out of strategy tenders those are simply a reminder as to when they were tendered so we know when they will next be released. Hopefully by then we can apply for them because they will be in strategy!

    I Hope that made sense.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Can you attach the updated workbook? Again, I think you need to specify the workbook

  6. #6
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    Of course, sorry I forgot to attach it.
    Attached Files Attached Files

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    My mistake, the code should be

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    Thats great, it's fixed the problem on clearing the correct form, but it still trys to format the new sheet and not the original.

    I tried adding 'sheet3.' before the 'range' commands but that didn't work and just caused more errors.

    Also tried using 'With ThisWorkbook.Sheets("Tender Diary")' & 'End With' around the code but that also didn't work (not that i truely understand why it should yet anyway!).

    I've added v4 with the change to module6
    Attached Files Attached Files

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try using ActiveWorkBook, not THisWorkBook. ThisWorkBook refers to the workbook containing the code, ActiveWorkBook should refer to the newly added workbook

  10. #10
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    But it is the workbook with the code in that I want it run in, at the moment it is running in the newly create workbook!

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Then Thisworkbook should do it. I'll take another look

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Which code is it that you are having problems with, is it this
    Please Login or Register  to view this content.
    Last edited by royUK; 12-17-2008 at 03:37 PM.

  13. #13
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    Hi Roy

    No that bit of the code works perfectly, it's the code in module5 thats running in the wrong place (it works just on the wrong sheet)

    Please Login or Register  to view this content.
    For some reason when the code in the button calls module 5 it runs in the newly created copy of the form and not in Tab 3 (Tender diary) in the original document.

    I've tried adding
    Please Login or Register  to view this content.
    infront of any Range statements but that casues an error, like wise when I try to add
    Please Login or Register  to view this content.

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try this

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    sorry that didn't work, I'd already tried that, and also tried putting the End With before the start of the next with selection but it didn't work either!!

  16. #16
    Registered User
    Join Date
    11-20-2007
    Posts
    33
    Ok i have it working with the exception of two cells on the last tab (Tender Diary)!

    Please Login or Register  to view this content.
    I added this to the code in tab1 hoping it would copy the functions in cells G6 & J6 and paste them into G5 & J5.

    But it doesn't work. Any ideas anyone?

    Thanks a lot
    Attached Files Attached Files

+ 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