Results 1 to 3 of 3

Made Private Sub in object This Workbook, now can't open workbook!

Threaded View

larbadar Made Private Sub in object... 07-25-2010, 01:37 PM
royUK Re: Made Private Sub in... 07-25-2010, 01:44 PM
larbadar Re: Made Private Sub in... 07-25-2010, 02:24 PM
  1. #1
    Registered User
    Join Date
    07-18-2010
    Location
    Arlington, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Made Private Sub in object This Workbook, now can't open workbook!

    So I've been trying to add a right-click menu item to my workbook. i.e. When you right-click on a cell, in addition to all the normal right-click-cell commands (copy, cut, paste, etc) there would be an additional command called "Draft Player" at the bottom of he list. It would run a macro I've defined in object Sheet1.

    I had it set up as follows:
    In object Sheet1 I had
    ' This is the sub that adds the Draft Player command to the right-click menu
    Sub AddToShortcut
    ' code goes here
    
    ' This sub would remove the Draft Player command from the right-click menu
    Sub DeleteFromShortcut
    ' code goes here
    
    ' This is the macro that would run when user clicked on the Draft Player command
    Sub DraftPlayer
    ' code goes here

    In object "This Workbook" I had
    'This sub calls the Sub AddToShortcut (from Sheet1) upon opening the workbook so that the right-click command "Draft Player" is available upon opening the workbook
    Private Sub Workbook_Open()
       Call AddToShortcut
    End Sub
    
    'This sub calls the Sub  DeleteFromShortcut (from Sheet1) upon closing the workbook so that the right-click command "Draft Player" is not available anymore when the workbook is closed
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Call DeleteFromShortcut
    End Sub
    The problem was that it didn't work. If I opened the workbook I received a "Compile Error: Sub or function not defined". This error pointed to the Private Sub Workbook_Open().

    Similarly, when I closed the workbook I received a "Compile Error: Sub or function not defined". This error pointed to the Private Sub Workbook_BeforeClose(Cancel As Boolean).

    So I thought maybe I had to move the two subs
    Sub AddToShortcut
    Sub DeleteFromShortcut

    from object: Sheet1 to object: This Workbook. Which I did. I saved and closed the workbook. Now the workbook does not open! When I double click the file name to open it, just the PERSONAL.XLSB file opens! How do I get my workbook back?!?

    I tried disabling macros to see if it would open that way, but no luck.

    Ack! Help me Obi Wan. You're my only hope!!
    Last edited by larbadar; 07-25-2010 at 02:24 PM.

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