Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Personal.xls Problem

    The file Personal.xls is normally stored in Documents and Settings\<username>\Application Data\Microsoft\Templates and is used to store macros, VBa, etc which have a global usage across workbooks. It is usually loaded in the background, but shows up in the Project Explorer in VBA.

    I have a client who has a series of files, one for each property that he owns and one worksheet within the file for each set of leased premises within the property. I have a set of functions which compute the monthly bill, including taxes, fire brigade charges, gardening and so forth. The data is entered first, then a button on a toolbar which carries out the computations, gets a new invoice number from an underlying invoice file and then copies the invoice details back into that file.

    The problem I have is that when the button is pressed, the program comes up with an error "Cannot find Personal1.xls." (NOT Personal.xls). Can anyone tell me where the pointer to Personal.xls is stored and which appears to be correupted. I have copied Personal.xls to Personal1.xls with no joy. I have also scanned the Registry to no avail.

    Any advice will be gratefully received, provided it reaches me before the men in white coats take me away (with apologies to Tom Lehrer !).

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Can you post the code?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533

    Cool

    Quote Originally Posted by Jim Wright
    The problem I have is that when the button is pressed, the program comes up with an error "Cannot find Personal1.xls." (NOT Personal.xls). Can anyone tell me where the pointer to Personal.xls is stored and which appears to be correupted. I have copied Personal.xls to Personal1.xls with no joy. I have also scanned the Registry to no avail.

    Any advice will be gratefully received, provided it reaches me before the men in white coats take me away (with apologies to Tom Lehrer !).
    The way to avoid this problem is to create a separate toolbar with the buttons and macros assigned for your Personal.xls workbook (or whatever workbook containing your VB Code) Then Attach this toolbar to the File following these steps: (1) make sure the file is visible. (2) Use Tools->Customize... Attach... (button) on the customize dialog. (3) Save the file with the attached custom toolbar.

    (4) Place this code in the 'Workbook' class Module. Save the workbook before closing.
    Code:
    ' Replace 'CustTBName' with the actual Name used for your attached Custom Toolbar
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Toolbars("CustTBName").Delete
        ThisWorkbook.Saved = True
    End Sub
    You may need to Hide the workbook first then save it (not sure if personal.xls will be hidden by default) Type 'Workbooks("Personal.xls").Save' in the Imediate window to save the hidden workbook after Hiding.

    Now this way the toolbar buttons will not loose the workbook association even if you move the workbook or change the name. Further more if you send your client an updated workbook you will not need to consider deleting the old toolbar buttons or re-linking them. The Toolbar will be deleted each time the workbook is closed and will re-display whenever the book is opened.
    ~

    Bill

Posting Permissions

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