Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Class Modules (2000)

    Hi all,

    In my target workbook, I have the worksheet menu bar disabled and a substitute bar in it's place. I am trying to prevent the user from opening another instance of Excel while the target workbook is open. The user cannot open another workbook from the file/.../...menu because it is not there, however, the user can minimize the window and double click on an existing workbook, or use the start menu to begin a new workbook, or have a workbook already open and then open the target workbook.

    In the attached test workbook, following Chip Pearson's instructions, I have created a class module that prevents opening another workbook from the file menu. This seems to work fine. Is there some way to trap the other methods of opening another workbook from the start menu or minimizing the app and double-clicking an existing workbook?

    I tried the Class_Initialize event, but since Excel is already initialized it doesn't fire (or will fire upon opening if Excel is not already running).

    Or, as an alternative, is it possible to somehow disable the minimize and maximize buttons at the upper right of the Excel window in conjunction with preventing the user from accessing the Start menu? (seems to me this would be almost impractical and highly unlikely as a solution)
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Modules (2000)

    Hi Michael,

    You used the wrong event. The NewWorkbook event fires when you open a new empty workbook (FIle, New), which of course is OK if you want nothing else than you own book open. Add this:


    Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "you opened a workbook!"
    Wb.Close False
    End Sub

    Also, your code is not OK, better is:

    Public Sub App_NewWorkbook(ByVal Wb As Excel.Workbook)
    MsgBox ("You cannot open another instance of Excel")
    Wb.Close savechanges = False
    End Sub

    Lastly, opening a workbook does NOT open a new instance of Excel, they are in the same instance.
    Your event code would not work on the second instance, which knows nothing about the event.
    Sometimes opening a file from explorer does open a new instance of XL. But then that would be OK for your aplication (which is still in its own "private" instance)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Modules (2000)

    Thanks Jan, will play around with that for a while. Am just beginning to understand the class module concept.

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Class Modules (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Mike

    To add to Jan Karel's comments:

    In the same instance of MS-Excel when you open a workbook you get to deactivate the currently active workbook and then activate the newly opened workbook. So the workbook deactivate event can help in this case.

    If the User manages to start a second instance of MS-Excel and open the same workbook well the user will get a read-only notification, but I am not sure if you can trap that. Maybe if you have some code in the Personal.XLS that would react to a read-only workbook, just a thought here.

    But Windows will allow you to test and see what applications are running. This is done by an API that would read the Window Name (check the MSKB for more info.) and then you will get two of the same and that is how you know what is running.

    The best thing to do, is to remove the Close button from the Titlebar, Maximize the workbook Window and then protect the workbook and disable the Alt+Tab and the other windows navigation keys. You still have to worry about the Task bar and that is a big thing to do.

    So ask yourself: Why all that? is it adding value or TRUE security to the application? Why do you need it so locked down?

    Just my two cents...

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Modules (2000)

    Hi Wassim,

    Actually, the better solution was to rework my code in my workbook. I have an "Accounting" book customized to fit our needs such as inventories, daily receipts, invoices, food categories, etc. Having spent years in the food business, it was easy for me to determine the needs i.e. the "flowchart" for such a book. The problem however, was that when I first started I did not even know what a user form was much less the difference between "activeworkbook" and "thisworkbook", among others.

    As such, some places in my code did not respond well when another workbook is open at the same time. Hence my question and inevitable probing into the class module thing. That is how it started...

    Now, I just want to know how to do it because I want to learn more about class modules!

    (I have reworked the code in my book by the way!)

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Class Modules (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Mike

    You brought me back way back to 1994 when I started dabbling with MS-Excel and VBA. I knew it would be a great thing to be able to customize and automate Excel, and that has endured all these hardships.

    Kudos to you <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> for being brave to tackle all these subjects. <font color=blue> BUT </font color=blue> now that we know what the situation is, I <font color=red> STRONGLY </font color=red> suggest that you clean up your workbook, that is remove any sensitive information, and post it here, watch the guidelines , and have the loungers pick it apart and give you their opinions. This I found a tremendous boost to the learning curve. Its like flying an FA-18 River Rattler right of off the deck of the USS Nimitz (CVN 68), vs. flying a Cessna 172 at some local municipal airport. (Substitute your own flying machines if needed).

    Good luck in your efforts, you are in the best of lounges.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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