Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 27 of 27

Thread: Custom menu (Office 2002)

  1. #21
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Dennis, I would maybe use Application level event trapping & check for a specific sheet & value in a cell, or just rely on each Workbook Activate event to activate the addin. See what OP requires 1st (damn, too many experts here, LOL)
    Last edited by Insomniac; 05-25-2004 at 01:05 PM.

  2. #22
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    Hi Insomnic :hi

    LOL, there are too many Experts! Until a few minutes ago, I thought that I had some pretty good code, Doh!

    Ok, I'll address these points one by one, but it does sound like that to handle this for XL'97 that Application Trapping would be required, as you noted.

    (1) "Dim WithEvents m_cbButton As Office.CommandBarButton" will fail miserably for users of older versions: ie 97.

    I was blissfully unaware of this! I don't know how you XL'97 get around in life... LOL. Hmm... this seems to necessitate Application Event Trapping. Interesting that XL'97 supports Application Events but not not CommandBar Events, eh?

    (2) If the user resets the toolbars after Workbook is opened you will get errors with m_cbButton.Visible = True, etc...

    This happens if you edit your code within the Project, forcing your project to "Lose State". Once your code is finalized, I would expect that this would not occur. But since trapping Application Events would completely separate the "Event Trapping" from the Code within the Workbook in question, it would again seem to be a better solution. Two points for the sleepless one...


    (3) Mike with your code only the creating Workbook can run the code. If ThisWorkbook Is ActiveWorkbook Then...

    This part was 100% intentional. It seemed from the question that the OP intends for his code to fire for the ActiveWorkbook only. With possibly multiple Workbooks all hooking to this Event, we need to see which Workbook is Active and only fire for this one. But yes, yet again, this would be easier for Application Events, where we can simply Trap the Application_WorkbookActivate() Event. Three points... Hmm...

    5. Personally I would make an Addin for the control and check for it's existence in the Workbook_Activate() / Workbook_Deactivate() events,
    I would place all the code in the addin to do enable, delete etc.


    I stayed away from this for one reason and one reason only, that this involves an installation issue to some degree. With the current solution, you could email this Workbook to another User and they could open it like any other Workbook and it will run.

    Using an XLA requires that the XLA be placed into the XLStart directory, or perhaps placed within the same folder as your Workbook and the Workbook_Open() event would then have to search out for the XLA, find it, and open it. Not a massive hassle, but, well, I decided to go for a more "self contained" solution.

    That said, I had no idea that the code I provided would not work at all in XL'97. Soooo.... hmmmm... I guess we should make a version using Application Event Trapping, eh?

    I don't have the time today, but if you want to take a crack at it, be my guest Otherwise, I'll take a shot at it tomorrow if no one else has provided a solution by then. Gotta run...

    Have a great night all ,
    Mike

  3. #23
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Mike, thats a pretty good summary of the points I made & as you know I always allow for versions down to xl97. Maybe we overcomplicating the theme of original post but hopefully will stimulate others to consider all the variations on the theme. Me too need to review tommorrow, hopefully more input from many other experts available here.

  4. #24
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi guys,

    Don?t forget that the OP is only running Excel 2002

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #25
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Yes Dennis thats why I said "Maybe we overcomplicating the theme of original post" wait for response I think.

  6. #26
    VBAX Regular Sphinx No. 4's Avatar
    Joined
    May 2004
    Location
    Stockholm, Sweden
    Posts
    9
    Location
    I just got home from work, at 11:30 PM , and wednesday is conference day, but I will have a go as soon as possible.

    Thanks for all the replies, I am truly overwhelmed! :hi
    Last edited by Sphinx No. 4; 05-26-2004 at 10:24 AM.

  7. #27
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    This may be way off in left field... but...

    Not knowing the context of the OP and him using XL2002, I wonder if scenarios might be easier to implement on one sheet, or even custom views, and then have a macro assigned to buttons that activate the view/scenario?

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

Posting Permissions

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