Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FILE > SAVE AS causes data to delete (Excel 97)

    An Excel 97 workbook has 15 worksheets. It includes activex controls (combo boxes and command l buttons).

    It includes about 60 named cells in the workbook.

    When I click on FILE > SAVE AS it deletes the contents of a cell. The FILE > SAVE command does not have the problem.
    Since this cell is linked, when its value is deleted, many other values through the workbook are changed. The cell originally held the values of one of the combo boxes. I removed the combo box in an attempt to solve the problem.

    I cannot find any VBA in the application which would cause the cell to be cleared. I created VBA code for a BEFORE SAVE event and stepped thru the VBA..There was nothing.

    Can anyone suggest why this application is deleting the values. I suspect it is a quirk of Excel 97 and Activex controls.

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

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    If you start Excel in safe mode (start, run, Excel.exe /Safe), does the behaviour persist?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    This can be hard to get a finger behind.

    One approach is to make a copy of the workbook, and start removing features (such as all ActiveX controls), checking each time whether the problem persists. You could also try rebuilding the workbook from scratch. Both will be time-consuming.

  4. #4
    Star Lounger
    Join Date
    Oct 2002
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FILE > SAVE AS causes data to delete (Excel 97

    I opened in SAFE mode ... the behaviour persists.

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

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    Is any of the controls hooked up to this offensive cel?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Star Lounger
    Join Date
    Oct 2002
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    I removed all links from controls to this cell. However, when another activex control changes, it causes this cell to be changed. However, this cell should not be changed during the save as process.

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

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    Strange indeed. Could you send me the file?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Oct 2002
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FILE > SAVE AS causes data to delete (Excel 97)

    I sent the .XLS to info@jkp-ads.com -- too big to attach

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

    Re: FILE > SAVE AS causes data to delete (Excel 97

    The problem is, that when doing a save-as, the change event of Combobox1 fires (which is clearly wrong, since it didn't change). I tried the click event, which also fires on a save-as.

    The only alternative I have is to remove combobox1 and use a combobox from the forms toolbar. Note that their result is the index of the selected item rather than the item's value. Then create a small macro in a normal module to catch the click event of the new combobox (rightclick, assign macro). This does NOT fire on save-as.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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