Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Editing problem on Excel (2002)

    I use an Excel form that has an annoying problem. Whenever I double click the cell to edit it, the cell content is deleted. Is there a setting I need to change? The one who designed the form can't figure it out either.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing problem on Excel (2002)

    Could we see the code?

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    Is there any code in the Worksheet_BeforeDoubleClick event routine in the module behind the worksheet.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    Steve,
    I am trying to get a copy of the form that is unprotected so I can take a peek of what's going on. There are no codes in the form, only formulas.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    Legare,
    There are no codes in the form, only formulas.
    Zar

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing problem on Excel (2002)

    If the cell is being cleared from an edit, I suspect there is code somewhere that is clearing the contents

    Steve
    PS <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You dont have to respond the same way to everyone. You can reply to one and answer both questions, esp if the same answer.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    Code does not go IN the form, it goes in a module behind the Worksheet or Workbook object. Are you sure you looked in the right places?
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    Steve/Legare,
    I finally got an unprotected copy of the form. The problem was on CELL formatting under the PROTECTION tab. The problem cells have the HIDDEN formula box checked. Once the sheet is protected, double clicking the cell erases the content. I think that was weird. The double_click event would have been a more plausible cause of the problem as you both pointed out. Thanks a lot for you help. I will definitely be soliciting your help in the future.
    Zar Belen

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing problem on Excel (2002)

    I still don't understand. The hidden in the format under protection, does not clear the cell, it only hides the display of the formula in the formula bar, the value is still displayed. I also wouldn't think in a protected worksheet you would allow the user to edit a formula.

    Steve

  10. #10
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    I don't understand either. The value is still there but once protected it has the side effect of clearing the cell when it is double clicked. You may want to try it yourself. The cells in question are user entry cells so there are no formulas and nothing really to hide. I suspect the form designer selected the whole range and did a one-time formula hiding which included the user entry cells.

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

    Re: Editing problem on Excel (2002)

    Because of the hidden formula setting, there is no formula Excel wants to show in the formula bar when you double click the cell. I too wuold have thought it more logical if one would be presented with the formula's result upon double click.

    The mistake made in this case is that the cell should have had the Hidden AND the locked property checked, so formulas cannot be viewed nor changed, nor removed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing problem on Excel (2002)

    I understand what is happening, I just don't understand why you want them to be able to allow users to edit a formula? nor why you would want the formula hidden if you are going to want them to edit it? If it is a value, why hide it: they can see the value anyway?

    You "hide" it to prevent the user from seeing what the formula is. You protect it to prevent them from editing it. If you want them to edit it, why are you hiding it?

    Steve

  13. #13
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    We don't want the users to edit the formula that's why all cells with formula are locked and formula hidden. The cells where the users enter the data of course do not have formulas but were inadvertently formatted by the designer to hide the formula. This is what was causing the problem I posted.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing problem on Excel (2002)

    Can we assume that "was" the problem is now fixed by "unhiding" the cells to be edited?

    Steve

  15. #15
    New Lounger
    Join Date
    Apr 2004
    Location
    Roswell, Georgia, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing problem on Excel (2002)

    After I unchecked the Hidden property, the problem went away. I can now double click a cell and edit the contents.

    Zar

Posting Permissions

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