Results 1 to 7 of 7

Thread: odd cell behavior: can overwrite cell contents but not edit cell contents
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question odd cell behavior: can overwrite cell contents but not edit cell contents

    My users are running into a problem that I can't solve. In one particular cell they cannot edit cell contents. They can write in whatever they want - but only by replacing the existing contents.

    So for example say it contains the string "Workpaper" and my user wants to make it say "Workpaper B." They should be able to hit F2 or click into the formula bar and simply add "{space}B" to the end of the string. However, when they begin to edit, the existing text disappears. If they hit ESC it is restored. If they hit enter (without typing any additional text) it is cleared (like the clear contents command). They have to type "Workpaper B" in full.

    The cell is not locked and the worksheet is not protected, and I'm not even aware of any protection setting that would result in this behavior. Trying to google about it I just get results regarding the insert key on the keyboard, or overtype mode, which is not the same thing I'm seeing.

    What's more it's only happening with this ONE cell, nowhere else.

    Any ideas?????
    Last edited by AnnaHansen; Sep 16th, 2019 at 06:39 PM.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,238
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    Is there any event code in the code module for the worksheet that cell is in (not a general code module... the worksheet code module)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Oct 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    Found it! It was the formula hidden property.

  4. #4
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    So the sheet was protected? Because the only way I know to use the Hidden property is to protect the sheet.. but then you wouldn't see the text in the formula bar.
    Unless you are referring to another setting?

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    When the locked property is off, but the sheet is protected, the user can still edit the cell (depending on the options chosen when protecting the sheet). When formula hidden is turned on, Excel doesn't let the user see the previously existing text when you go into edit mode. So they can edit it but Excel clears out the formula bar so they can't see what was there before. Makes sense when you're talking about a formula in the cell contents but sure is baffling when you've strings on the brain and not formulas.
    Last edited by AnnaHansen; Sep 16th, 2019 at 07:19 PM.

  6. #6
    New Member
    Join Date
    Oct 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    The sheet was not protected. The hidden setting is in cell properties and prevents the user from being able to see a formula in the formula bar - in this case, the 'formula' was a text string.

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: odd cell behavior: can overwrite cell contents but not edit cell contents

    Quote Originally Posted by Rick Rothstein View Post
    Is there any event code in the code module for the worksheet that cell is in (not a general code module... the worksheet code module)?
    Thank you for looking - we don't allow any sheet level code modules, but we do have a heck of a lot of modules that do various things with the cell locked property and the sheet protection options - that's also where my mind went first!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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