Excel glitch. Cells from other sheets appearing on top of current sheet

[tom83]

Hi,
Can anyone help with the following glitch. Whenever I enter data in one sheet, the row from a summary sheet which has formulas linking back to this sheet is displayed which is very annoying (see overlay on row 16 and also row 10).
I have quite a bit of code in the sheet but I don't this should be affecting the sheet. Is there anything I can do to avoid this?

 

[Hui]

I would try the following

1. make a backup of the file and ensure it is saved in a separate directory
2. With the file open press Ctrl+Alt+Shift+F9
3. Once the file has recalculated, Save the file
4. Completely Close Excel
5. Open Excel
6. Open the file using the File Open dialog but use the Small Drop Down arrow and select Open and Repair...



7. Check the file now

8. If the file still has problems, try opening it and repeat the same procedure above but on a PC with lots of memory and the latest version of Excel preferably Excel 2016 if possible

9. If the above doesn't help can you post the file here?

 

[Marc L]

Hi !

Maybe display is not in its default size or just update the video driver …
​

 

[tom83]

Hui,
Thanks for that but unfortunately it hasn't stopped it from occurring. Below is a link as the file is too large to upload on here.

https://www.dropbox.com/s/mv8oi8kpikh1ej4/CVR template - final version blank1.xlsm?dl=0

If you go to sheet '6. Subs Liability' and then double click on one of the links in column B the corresponding sheet will appear. When you enter a figure in any of the columns from F-N the row from sheet '6. Subs Liability' appears??
Any help would be appreciated.
Many thanks,
Tom

 

[NARAYANK991]

Hi ,

I have tried out what you described , and I did not face the problem that you report. I have used Excel 2010.

Narayan

 

[Hui]

I also don't have any issues with Excel 2013 or 2016

What I would suggest as a long shot is adding a line into the start and end of the Worksheet_Before_Doubleclick event in the 6. Subs Liability code module

like this:



I am also unsure what the First If does, as it doesn't do anything

If selection.count=1 then
End if

Can you try using the file on a PC with more memory or a newer version of Excel?

 

[tom83]

Hi,
I'm using excel 2016 latest version on a newish laptop (4GB RAM) that should be sufficient for excel.
I was wondering if it has anything to do with the long code to double click and if there is a shorter way of writing this i.e. cell range and then if the contents equals the sheet name that one is selected and opened. I could'nt manage this so had to do a separate event for each cell.
It's strange how you guys don't have a problem yet all of us here do as this is a workbook several of us (3 of 3 have the problem) in my company need to use.
Could the fact there is 100 sheets in a workbook be causing a problem?
Thanks for your help so far.
Thanks,
Tom

 

[tom83]

another thing, i have just tried deleting all the double click code to see if this is the problem. when i delete it all the problem goes away so it must be something to do with running that long piece of code.

if somebody can come up with a code using range b13:b115 and then select sheet with name the same as the double clicked cell contents this may help? is this possible?

thanks

 

[Hui]

Have you stepped through the code line by line to see where the issue first appears?
Click on the Private Sub BeforeDoubleclick(...) line
Press F9
it will highlight a brown color

Return to Excel
Double click and then when the VBA code is shown press F8 to step through line by line the code
Watch the Excel panes as you go

 

[tom83]

sorted it, simplified the code to the below.
made sure the cell contents in column B were correct i.e. '001 not 1 and this seems to work without the strange glitches appearing.
thanks for all your comments.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

'If the cell is empty or not in column 2 (B), we exit.
If Len(Target.Value) = 0 Or Target.Column <> 2 Then Exit Sub

On Error Resume Next
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Activate
REPORT.Visible = xlSheetHidden
End Sub