Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel showing pasted text when double clicking on another cell

0

Hi

One of our users has a problem which has been going on for a while now. When they copy a piece of text from the same excel spreadsheet or from something else like a filename etc, they paste it into an empty cell in excel. Excel freezes after that, then if they double click on a cell that contains text the text which they copied and pasted shows up in the cell which they double clicked on, thereby overriding the text that was in the cell before they double clicked on it. This continues, so they click on another cell and the text shows up again and they need to remove the text. The only way to stop this behavior is to close the excel file and open it again. This issue is the same as the excel macro tutorial described here: 

https://www.teachexcel.com/free-excel-macros/m-20,enter-text-with-mouse-click.html

The issue is I can't find the code that looks like the above tutorial in the current macros. 


How can we fix this issue?

We use Microsoft 365 on the Azure platform and Windows server 2016.

I have attached a link with the video to demonstrate the issue.

https://drive.google.com/file/d/1sSP6BTGxoi9jrEhjymvJuqEo7P1pNTRd/view?usp=sharing

Thank you

excel
MS Office Version: 365
posted by: APG2018 on Tue Feb 9, 2021 at 12:28 am
Answer
Discuss

Discussion

Did they try pressing the Escape key? Double-click sounds like a bad idea because it enters in-cell edit mode and the behaviour you describe seems like the correct response to that.
The error, if any, occurs before that. You say Excel "freezes". You haven't told us what that means. Does the pasted content appear? What does the user expect that Excel should do at that point? Perhaps he/she just forgot to press Enter.
Please bear in mind that we won't be able to help unless we can reproduce the behaviour. Toward that end the info you have provided just isn't even near enough. We need a very precise list of clicks. Your video illustrates the result but doesn't helkp reproduce it.
Variatus (rep: 4889) Feb 14, '21 at 7:06 pm
I am waiting for the user to work on that excel workbook again and when they have the issue, I will be detailing a list of clicks and what the user does exactly. I will post it here once I have a detailed list of clicks and actions. 

Thank you 
APG2018 Feb 14, '21 at 7:13 pm
Add to Discussion

Answers

0

Thank you for the excellent step-by-step description of the fault. Your user appears to be reall well versed in the use of Excel.

The list of possible sources for the problem is quite short.

  1. The Workbook.
    It may have become corrupt.
  2. The installation (Excel).
    1. It may have an unusual setting we haven't thought of yet.
    2. It may be corrupt.
  3. The PC.
    It may be infected by a virus.
  4. Excel
    There may be a flaw.

Drive at the solution by a process of elimination. The first step, as I already said, is to replicate the error. Take the workbook to another computer. Whether you are able to replicate the error or not, that experiment will tell you a lot.

posted by: Variatus (rep: 4889) on Tue Feb 9, 2021 at 9:41 am
Discuss

Discussion

@Variatus Might the code be with a VeryHidden sheet within the problem workbook or a linked template? 
John_Ru (rep: 6152) Feb 9, '21 at 10:04 am
The code of a VeryHidden worksheet isn't hidden. If it is in another workbook it still has to be triggered from the one we see.
Variatus (rep: 4889) Feb 9, '21 at 10:17 am
Agreed. I guess the user has checked to see that there isn't a password protected VBA project. 
John_Ru (rep: 6152) Feb 9, '21 at 11:19 am
@Variatus thanks for the reply. I looked through the code and I was not able to see the lines of code that you posted. I would like to post the code that I have but there 16 modules with code. How would I go about posting the code? There is just too many lines of code. 

Thanks 
APG2018 Feb 9, '21 at 5:33 pm
APG, as @Variatus said,  you can post "a sanitized copy of your workbook". Go to your original Question, edit it and use the Add Files... button to attach an Excel file. 
John_Ru (rep: 6152) Feb 10, '21 at 2:09 am
Thanks @John_Ru, I uploaded the excel file. 
APG2018 Feb 10, '21 at 4:18 pm
Hello Yousuf, I meant to post the two procedures mentioned in my above response. In your workbook there are no event procedures at worksheet level at all. Perhaps you removed them? It's the code behind each worksheet, not the code in the 16 standdard modules or behind the user forms.
Presuming that there is no code in the sheet modules, please try to reproduce the behaviour you complain about in a copy of the workbook you posted. Tell us the sheet and the exact sequence of clicks so that we can reproduce the error. Include [ENTER] actions in your description. Normally [ENTER] should terminate the kind of behaviour your question describes. Therefore it's important to know when, in the sequence, it was pressed.
Variatus (rep: 4889) Feb 10, '21 at 9:07 pm
FIY, your workbook crashed on opening, on the first line of the Workbook_Open procedure. Off hand, I don't know why Worksheets("Program").Activate shouldn't work but the entire procedure makes no sense because it activates all the sheets, making a selection in each (which will be lost when the next sheet is activated) and ends up activating "Program" again a second time.
I presume that it's the Zoom setting you wish to apply. If so, the correct place to have that code would be in each Worksheet's Activate event procedure, not in Workbook_Open.
Variatus (rep: 4889) Feb 10, '21 at 9:15 pm
APG. I got the same failure on opening (and was puzzled by the code, like Variatus).

I saw the brief video from your Question's link but could not find the range of cells show- where are they? (Hopefully you can give detailed actions, as requested by @Variatus).

Please confirm too that the user has only this workbook open when the strange behaviour occurs.
John_Ru (rep: 6152) Feb 11, '21 at 2:47 am
Thanks John, Variatus, and I apologize for the inconvenience caused. The issue is this excel spreadsheet was created before I started working at this company by someone else and they no longer work here, so I don't know exactly how they did the code. I also don't have any experience in VBA so its all new to me. 

The user has said that the issue only occurs on this spreadsheet and possibly another spreadsheet but we want to fix this first because its more common on this one. Also they said it does not happen all the time. I will try and get a detailed explanation of the sequence and the clicks involved, hopefully sometime next week and I will post it here. 

Thank you both again for your help 
APG2018 Feb 11, '21 at 4:46 pm
Hi
These are the steps taken prior to the issue happening according to the user. 
1. Preview the email attachment.

2. Drag pdf attachment into folder previously created.

3. Right click and re-name to have job number & address.

4. Click Ctrl-C on the keyboard to copy the filename once re-named.

5. Then go to the excel file that is open on another monitor.

6. Paste the filename into the “File Name” column (ctrl-V). 

7. Use the arrow keys and go over in to the “Quote No.” column. 

8. Add the quote number in.

9. Use arrow key to “Received” and add the date the quote was received.

10. Use the arrow key and go to “Amount” and add the amount excluding GST that was provided on the quote. 

11. Click Ctrl-S to save the document.

The issue happens after, when they double click on a cell it just shows text and that text could be from the filename column, Date, or any other column. There's no pattern to it. They tried pressing the Escape key but that does not change the behaviour. 

I hope this helps. 

Thank you
APG2018 Feb 16, '21 at 7:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login