shared workbook scrolling and views

[DocEditor]

1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?

2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?

Thanks from a novice user and new forum member.

[ConneXionLost]

Hi DocEditor,

For Q1, have them check the "Scroll Lock" on their keyboards.

Cheers,

[DocEditor]

Thank you - not the problem however. The odd part is that the scroll bar moves as though you are scrolling, but the page view itself doesn't change. Strange and annoying...

[Cheeky Charlie]

So nice to see a new user take the time to title thread carefully and articulate question concisely!

1. I'm sure you know, as you use them, that shared workbooks are a bit temperamental, so the answer might just be "shared workbooks are a bit temperamental" - but one idea, are people freezing panes or creating window splits? - this may be a factor.

2. As far as I know, not by a simple "option setting". We can very easily create a macro to "reset" the view on opening, but people would have to enable macros for this to work, which is a bit tedious to explain to large groups, if they don't a warning message will pop up every time they open the book. If you're comfortable with getting people to enable the macros (we could go one further and sign the project, allowing people to "always allow" macros signed by you - takes five minutes) then let us know what sheet you want on top and we'll create the relevant macro and show you how to put it in.

CC

[DocEditor]

Thank you Cheeky Charlie!

I have a split-window and freeze panes applied, but I'm not sure if anyone else does. I shall check...

As for the macro, yes, this had occurred to me too but I thought there was a setting somewhere when you protect the sheet... "Freeze window view" or something like that - ?

(PS - My knowledge of macros is comparable to my knowledge of ancient Hebrew so if it comes to that I'll definitely need help, hahaha)

[Cheeky Charlie]

Oh yeah! If you're protecting the workbook and not the sheets, you can elect to preserve windows - testing... quirky... if you protect the workbook (w/out sharing) you can preserve Windows - then if you "protect and share" (which doesn't include the preserve windows option) the workbook will open with the active sheet as was when you protected & preserved windows - irritatingly though, it opens in a window within the Excel frame - you'll see what I mean!

Try this, let us know.

CC

[DocEditor]

Argh - so close I can taste it... now I can't figure out how to "elect to preserve windows" when I password-protect the workbook. I thought it was in the "Allow users to" list, but I don't see it there - ?

Thanks again for your help...

[Cheeky Charlie]

"Allow users to" comes up when you protect a sheet - so protecting a sheet is about granular control (one sets the "locked" property of individual cells) of each individual sheet.

Protecting workbooks is about limiting user-base and blunt editability - protect windows mandates window layout & active page - protect structure limits inserting/deleting rows, columns, sheets, cells... i.e. a blanket approach to the granular control of protecting worksheets.

So... if you want the granular control you need to apply the sheet protection first, then protect the workbook (+ windows) - apply a password there - then share ("protect and share" is not necessary)

Does that help?

[DocEditor]

Aha, I got it now, but now I have the other issue you mentioned - it opens up in a window within the Excel interface, not maximized, and not moveable! Which is actually a worse problem than the scrolling and views issue.

Still, thank you for all this help... I might be just out of luck here.

[Cheeky Charlie]

The alternative is a macro - as I said, the VBA is easy peasy and I can walk you through applying it. Enabling it for your users, however, is a bit more work - although, again, I can walk you through it - shouldn't take either of us more than ten minutes.

CC

[DocEditor]

Very generous of you and I may well take you up on this offer. I have a few things to iron out before I ask your help but it is great to know that this resource is here. Thanks once more.