Seeking help for total worksheet protection
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 - |
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 |
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 - "Stephen Bullen" wrote in message ... 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 |
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 - "Joseph Geretz" wrote in message ... 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 - "Stephen Bullen" wrote in message ... 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 |
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 |
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 - "Stephen Bullen" wrote in message ... 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 |
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 - "Stephen Bullen" wrote in message ... 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 |
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 - "Stephen Bullen" wrote in message ... 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 |
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 |
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 - wrote in message oups.com... 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 |
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 |
All times are GMT +1. The time now is 12:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com