Today, Episode #1437 title should be, "Learn Excel Mystery Theater at Hartville Hardware: Where is the Spreadsheet?"
"My friend Lyn opened his spreadsheet and he can't see anything! What a mystery. Things show up in the formula bar, but there is nothing visible in the Excel window. Is the Worksheet hidden? No. Are the Columns Hidden? No. 10 minutes into this issue and it's still a mystery..."
See how Bill solves this mystery and what we can do to keep this from happening in the future in Episode #1437.
"My friend Lyn opened his spreadsheet and he can't see anything! What a mystery. Things show up in the formula bar, but there is nothing visible in the Excel window. Is the Worksheet hidden? No. Are the Columns Hidden? No. 10 minutes into this issue and it's still a mystery..."
See how Bill solves this mystery and what we can do to keep this from happening in the future in Episode #1437.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1437: where is the spreadsheet?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Have you ever shopped it Hartville Hardware?
It is the most amazing hardware store in the entire world located in Hartville, Ohio.
I pulled up there Saturday and in the parking lot, I saw my friend Lyn.
Lyn runs the plumbing store down the street and she says, “Hey, I got an excel mystery for you.
I got this spreadsheet.
We have three people that share this spreadsheet and on two of the computers, when we open the spreadsheet, every once in a while it's just not there.
It's like the whole thing is hidden.
I can tell it's open because I can see a word in the formula bar.” You know, next time it happens, call me.
So, this morning at 8 o'clock I get a text from Lyn.
It's happening again.
So, I come down and this is what I have.
This was a mystery.
I mean, look, check.
I can see stuff, but if I try anything, you know, as far as-- let’s move this down here, format, cells, format, hide and unhide, you know, there's nothing to unhide.
I tried going over to VBA, Alt F11 and let's see, podcast 1437.
I looked at this workbook and view properties.
It wasn't hidden.
The scroll area wasn't set.
There was nothing.
I could-- I could query it.
I could say, you know, print active sheet dot name.
I could see that it was there, nothing hidden.
I was just completely thrown for a loop and I said okay, did it somehow break Excel?
All right, but let me pause here.
Any idea on what is going on?
The tools are there.
I can't bring it back in.
It's not a hidden workbook.
It's not that all the worksheets are hidden.
What do you think?
Any ideas?
I mean, I'm, you know-- so, I'm kind of desperate, right?
Sorry.
Well, let's try-- I don’t know, Ctrl N.
Ctrl N and then, then I noticed it.
When I did Ctrl N, Book3 opened in not full-screen mode, but in kind of the tile mode.
I’m like, oh, no.
I wonder if somehow that other workbook is open, but it's just outside of our view.
Outside of our view.
So, when I clicked this button here to maximize, that maximizes all of them and then Ctrl tab, the whole thing is back and Lyn is like, “How did you do that?” So, the problem though is that it apparently got saved in that bizarre manner.
So that way any time you minimize down, it just disappears.
So, what do we do?
Tried active window dot top; instead of 1500 is equal to 1.
That brought it right back, but, you know, now we have the situation that it must be one of his co-workers who must have this huge screen and he drags it down there.
Now, Lyn can go to the co-worker and say, hey, stop dragging it down there, but I gave him a little-- just a little macro up here on the quick access toolbar saved in his personal macro workbook that did that active window dot top equal to one.
So, I was pretty happy with myself because it was 10 minutes of sweating like, what the heck is wrong with his workbook?
And then figure it out.
So, there you go.
If you ever open a workbook and it just isn't there, it's possible that someone, probably on a larger computer monitor, managed to drag the thing down out of the way.
Alt F11 to get to VBA, Ctrl G for the immediate window-- just type active window dot top is equal to one.
You might also have to do, in case they dragged it off to the side, active window dot left is equal to one and it will bring it right back into view.
Well, hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1437: where is the spreadsheet?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Have you ever shopped it Hartville Hardware?
It is the most amazing hardware store in the entire world located in Hartville, Ohio.
I pulled up there Saturday and in the parking lot, I saw my friend Lyn.
Lyn runs the plumbing store down the street and she says, “Hey, I got an excel mystery for you.
I got this spreadsheet.
We have three people that share this spreadsheet and on two of the computers, when we open the spreadsheet, every once in a while it's just not there.
It's like the whole thing is hidden.
I can tell it's open because I can see a word in the formula bar.” You know, next time it happens, call me.
So, this morning at 8 o'clock I get a text from Lyn.
It's happening again.
So, I come down and this is what I have.
This was a mystery.
I mean, look, check.
I can see stuff, but if I try anything, you know, as far as-- let’s move this down here, format, cells, format, hide and unhide, you know, there's nothing to unhide.
I tried going over to VBA, Alt F11 and let's see, podcast 1437.
I looked at this workbook and view properties.
It wasn't hidden.
The scroll area wasn't set.
There was nothing.
I could-- I could query it.
I could say, you know, print active sheet dot name.
I could see that it was there, nothing hidden.
I was just completely thrown for a loop and I said okay, did it somehow break Excel?
All right, but let me pause here.
Any idea on what is going on?
The tools are there.
I can't bring it back in.
It's not a hidden workbook.
It's not that all the worksheets are hidden.
What do you think?
Any ideas?
I mean, I'm, you know-- so, I'm kind of desperate, right?
Sorry.
Well, let's try-- I don’t know, Ctrl N.
Ctrl N and then, then I noticed it.
When I did Ctrl N, Book3 opened in not full-screen mode, but in kind of the tile mode.
I’m like, oh, no.
I wonder if somehow that other workbook is open, but it's just outside of our view.
Outside of our view.
So, when I clicked this button here to maximize, that maximizes all of them and then Ctrl tab, the whole thing is back and Lyn is like, “How did you do that?” So, the problem though is that it apparently got saved in that bizarre manner.
So that way any time you minimize down, it just disappears.
So, what do we do?
Tried active window dot top; instead of 1500 is equal to 1.
That brought it right back, but, you know, now we have the situation that it must be one of his co-workers who must have this huge screen and he drags it down there.
Now, Lyn can go to the co-worker and say, hey, stop dragging it down there, but I gave him a little-- just a little macro up here on the quick access toolbar saved in his personal macro workbook that did that active window dot top equal to one.
So, I was pretty happy with myself because it was 10 minutes of sweating like, what the heck is wrong with his workbook?
And then figure it out.
So, there you go.
If you ever open a workbook and it just isn't there, it's possible that someone, probably on a larger computer monitor, managed to drag the thing down out of the way.
Alt F11 to get to VBA, Ctrl G for the immediate window-- just type active window dot top is equal to one.
You might also have to do, in case they dragged it off to the side, active window dot left is equal to one and it will bring it right back into view.
Well, hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
