Images appearing on worksheet that are really on hidden sheets

RichardRanney

New Member
Joined
Nov 15, 2019
Messages
4
On this sheet, the only things that are actually there are in cells A1, A2 and A4. Everything else you can see is on some other 'veryhidden' sheet in this workbook. I'm using Office 365.

1. When I open this spreadsheet, I can type all over this sheet and hit enter and nothing happens.
2. If I go to any of the hidden sheets and click on any of the buttons, running a macro, and then come back to this sheet and type anything and hit enter, all these images show up. I typed a "1" in cell A4 and pressed enter.
2.b. If I scroll down and scroll back up, the words and color formats disappear, but the buttons remain. (Obviously, I can't click on the buttons; they're not really there.)
2.c. If I tab to a different sheet and tab back, the buttons also disappear.
2.d. If I try to type anything again, the images (not the same ones every time) reappear when I press enter.
3. If I remove the module, it doesn't happen.
4. These are very simple macros. I didn't use the macro recorder; I just typed them from scratch. They are basically just simple navigation tools for drilling around. I mean REALLY simple.

Sub MainDetail01()
Range("Submain!a1").Value = 1
SubMain.Visible = xlSheetVisible
SubMain.Select
Main.Visible = xlSheetVeryHidden
End Sub

5. Someone in IT told me this was a Graphics issue called Artifacting.
6. I have read several sites about this and can't solve the problem. (Disabling hardware graphics acceleration, not using ActiveX controls, etc.)
7. I have e-mailed the workbook to someone else and it happened on his machine too.


I am 3 weeks into a new job and VBA has been my friend for 20+ years. I don't know what's going on with this and my new coworkers are convinced that VBA is full of bugs and want nothing to do with this.
I am presently using Inserted shapes with hyperlinks to get around this problem, but that's not going to cut it in the long run.

Any help would be greatly appreciated.


1575407487439.png
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board Richard

1. Which version of Excel are you using ?
2. Does this problem happen in ONE workbook only ?


I am unlikely to be able to replicate your problem. But let's try to work out if it is related to xlSheetVeryHidden
To help diagnose the root cause of the problem simply make EVERY sheet either visible or not and AVOID using VeryHidden in the workbook to see if that eliminates the issue
(It may also happen when sheets are simply not visible rather than only when VeryHidden)

Amend above code as below (and any any other code in your workbook to use the same method to hide sheets)
VBA Code:
Sub MainDetail01()
    Range("Submain!a1").Value = 1
    SubMain.Visible = True
    SubMain.Select
    Main.Visible = False
End Sub

Let me know how you get on
 

RichardRanney

New Member
Joined
Nov 15, 2019
Messages
4

ADVERTISEMENT

I adjusted every macro as you suggested. This didn't fix the problem, but I'm kind of glad it didn't as I have been using xlSheetVisible and xlSheetVeryHidden for over 20 years with never a problem. I have only ever had this issue on one other spreadsheet years ago and never resolved it. As this is the first project with macros I have created at this new job, perhaps this is merely a 2nd isolated incident.

I do appreciate you trying and I seriously continue to welcome any further suggestions. I don't know how I'm going to do this job if I can't use VBA.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Is the problem VBA related ?

Start again
Create a NEW workbook with all the same worksheets and objects without any VBA
MANUALLY perform what was in macro you posted (simply HIDE the worksheet in usual way)

- is it possible to replicate the problem without using VBA on your PC ?
 

RichardRanney

New Member
Joined
Nov 15, 2019
Messages
4

ADVERTISEMENT

As I mentioned in my original post, if I remove the module, it doesn't happen.
I have been making spreadsheets like this for years. There doesn't seem to be anything special about this one.
The only thing I can tell you was the first time I noticed it happen.
I have a spinner control attached to a specific cell - a cell which also has its value changed by a macro upon activating the sheet, but I swear this is by far not the first time I have done this.

Again, I appreciate you trying to help.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Does this happen in any of your other workbooks ?
Can you recreate it in a NEW workbook ?
If the problem is not visible elsewhere and it cannot be recreated then there may be a minor corruption in the existing workbook

Simply re-building a workbook from the ground up has eliminated unexplained problems for me in the past.

Good luck :)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
A final thought from me
- active-x controls in the worksheet have suffered a bad press for a long time
- some users avoid them at all costs (often due to "scars from the past")
- others rarely experience any problems

I cannot tell from the image in post#1 if the "ghost" buttons are active-x command buttons or FormControl buttons
- if they are active-x ,replacing them with FormControl buttons could be worth a quick trial
- I would remove the spinner control at the same time
- and for good measure disable the Sheet_Activate event
 

RichardRanney

New Member
Joined
Nov 15, 2019
Messages
4
Well, I really do appreciate all your help. As you probably have experienced in the past, my boss said, "This is great. Can it also do....?" So, I am starting all over from scratch. I'm hoping this is just a freak thing and that I won't have the issues again.

I had read elsewhere the same thing about Active-X Controls and I never use them.

Thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,632
Messages
5,625,994
Members
416,149
Latest member
Bigpotato 668

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top