Images appearing on worksheet that are really on hidden sheets

[RichardRanney]

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.

 

[Yongle]

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)

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

Let me know how you get on

 

[MARK858]

Just to answer your first question the OP posted the below in post #1

1. Which version of Excel are you using ?

I'm using Office 365.

 

[Yongle]

thanks @MARK858

 

[RichardRanney]

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]

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]

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]

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]

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]

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.