Seeking help for total worksheet protection

[Joseph Geretz]

The code below cycles through every worksheet and protects it. It then sets
the active worksheet back to the first worksheet.

For Each WkSht In m_DocExcel.Worksheets
WkSht.Protect "mypassword", True, True, True, True
WkSht.Activate
m_AppExcel.ActiveWindow.DisplayGridlines = False
m_AppExcel.ActiveWindow.DisplayHeadings = False
Next WkSht
m_DocExcel.Worksheets(1).Activate

Is it possible to set protection on the entire workbook in one atomic
operation, or do I need to set the protection for each sheet separately, as
I am doing? And is there a more efficient way of setting the appearance
style for every worksheet or must I cycle through them individually as I am
doing? (I guess I could implement events and only set these properties if
the user actually activates a particular sheet?)

Anyway, here's my main question: With this approach I am able to lock the
contents of all cells, but this does not lock embedded VBA widgets, e.g.
Checkboxes, Command Buttons, etc. How can I lock these objects as well?

Thanks for your help!

- Joe Geretz -

 

[Stephen Bullen]

Hi Joseph,

Is it possible to set protection on the entire workbook in one atomic
operation, or do I need to set the protection for each sheet separately, as
I am doing? And is there a more efficient way of setting the appearance
style for every worksheet or must I cycle through them individually as I am
doing?

No, we're pretty much stuck with cycling through them as you're doing.

Anyway, here's my main question: With this approach I am able to lock the
contents of all cells, but this does not lock embedded VBA widgets, e.g.
Checkboxes, Command Buttons, etc. How can I lock these objects as well?

The VBA widgets each have a Locked property, which is independent of the
worksheet protection.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Joseph Geretz]

Thanks Stephen,

The VBA widgets each have a Locked property, which is independent of the
worksheet protection.

Is there a way to get access to the collection of Widgets so that I can
cycle through them and lock them?

Or maybe there's an easier way to do what I'm trying to do. We're
implementing an Excel Viewer window inside our application. Right now we're
actually using the Excel Viewer application, but it's klunky launching an
external application and then finding and embedding the window via API. We'd
much prefer using Excel itself via automation but we're finding what should
be a simple operation (setting the sheets to be read only) to be more
complex than we had anticipated. Unless I've missed something?

(I played around with m_AppExcel.Interactive = False but this left the
cursor as an hourglass and left the entire worksheet unresponsive to *any*
input to the extent that even the scrollbars are dead. Not good.)

Thanks for your help,

- Joe Geretz -

 

[Joseph Geretz]

Another problem we're seeing with worksheet protection is that if the
worksheet is already protected with an unknown password, then our own
protection fails. We can't rely though on the original protection since the
original protection almost always leaves certain fields editable, and we
want the sheet to be read-only in its entirety.

Thanks for your help,

- Joe Geretz -

 

[Stephen Bullen]

Hi Joseph,

Or maybe there's an easier way to do what I'm trying to do. We're
implementing an Excel Viewer window inside our application. Right now we're
actually using the Excel Viewer application, but it's klunky launching an
external application and then finding and embedding the window via API. We'd
much prefer using Excel itself via automation but we're finding what should
be a simple operation (setting the sheets to be read only) to be more
complex than we had anticipated. Unless I've missed something?

Hmmm, not easy! As you mentioned, there are lots of little gotchas to contend
with, such as sheets already protected, and what about third-party activeX
controls on the sheets, that might not respond to a Locked property?

I would be tempted to use an image control and a tab strip, with Excel
automated in the background (never visible). When you load a workbook, at tabs
to the tab strip for each (visible) sheet in the book; when a tab is clicked,
use Sheet.UsedRange.CopyPicture to copy an image of it to the clipboard, then
set the clipboard's image to the Image control. Whether you consider that to be
more or less of a 'hack' than using the actual Viewer is a matter of
preference!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Joseph Geretz]

Thanks Stephen,

Sheet.UsedRange.CopyPicture

If this works it is no hack, since this might do exactly what I need! I'll
let you know how this works out.

- Joe Geretz -

 

[Joseph Geretz]

Hi Stephen,

Actually I'm going to try this later on this evening, but with a slight
twist to what you propose. With the Tab Strip and Image Control approach,
I'm going to have to implement my own scroll bars and panning since the size
of the Worksheet image may very well exceed the size of the Image Control.
So I'd like to modify your suggested approach as follows:

In place of the Tab Strip and Image Control, I'll use a second copy of
Excel. When I open a worksheet in the hidden copy of excel, I'll create a
new workbook in the visible excel with the same number of sheets as the
hidden workbook has. Then I'll cycle through the hidden sheets and for each
sheet I'll take a snapshot of its image and paste it into the corresponding
sheet in the visible excel. For good measure, I can protect every sheet in
the visible excel.

If my concept is correct, the visible excel sheets should look virtually
identical to the hidden excel sheets except for the fact that they'll be
images which can't be modified.

If you have any code sample which shows how to paste from the clipboard to
the top left coordinate of a worksheet that will be a great help.

Thanks for your help!

- Joe Geretz -

 

[Joseph Geretz]

Thanks Stephen,

I used the combination of our approaches which I suggested in my previous
post. Total worksheet protection acheived. This is absolutely awesome!

Thanks for your help!

- Joe Geretz -

 

[aaron.kempf]

man what a waste

if you want to really secure something, learn how to write queries and
use Access.
you can disable and enable controls however you would like to
Excel should NOT be used for data entry

run away before it is too late

 

[Joseph Geretz]

Hi Aaron,

Thanks for your valuable insights. However, data entry, or processing for
that matter, is irrelevant to the current application. Our application is a
documents management software. As long as Excel documents exist, we will
manage them.

- Joe Geretz -

 

[Stephen Bullen]

Hi Joseph,

With the Tab Strip and Image Control approach,
I'm going to have to implement my own scroll bars and panning since the size
of the Worksheet image may very well exceed the size of the Image Control.
So I'd like to modify your suggested approach as follows:

Right. I forgot for a minute you were in VB6. In VBA userforms, you could set
the image control to autosize (which you can with a VB6 Picture control), then
have the Image in a Frame control, for which you can set the ScrollWidth and
ScrollHeigtht to the size of the image (so the Frame control would handle the
scrolling and panning for you) - but the VB6 Frame control doesn't have those
properties.

Glad you got it working anyway!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk