Results 1 to 15 of 15
Thread: Editing problem on Excel (2002)

20040401, 16:23 #1
 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.

20040401, 16:35 #2
 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

20040401, 21:58 #3
 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

20040401, 22:32 #4
 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.

20040401, 22:35 #5
 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

20040402, 00:29 #6
 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.

20040402, 00:56 #7
 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

20040402, 01:53 #8
 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

20040402, 11:12 #9
 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

20040402, 13:52 #10
 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 onetime formula hiding which included the user entry cells.

20040402, 14:41 #11
 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.jkpads.com
Professional Office Developers Association

20040402, 15:49 #12
 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

20040402, 16:37 #13
 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.

20040402, 16:57 #14
 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

20040402, 17:09 #15
 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