Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Double Protect' (XL97/sr2)

    OK - I have read a lot of entries here about protecting worksheets and workbooks but I still need help.
    If I remember correctly, my husband once received a workbook that was protected so that you couldn't copy the sheets - or do a "file save as" new name to get at the data. I don't remember if you could copy individual blocks of cells from a sheet.... The workbook also had some modules that were not viewable.
    I want to do something similar because I must distribute some data from time to time that I must
    make very troublesome for someone to alter and then print.
    I know if you protect the sheet you can still get at the data.
    Also if ya make the workbook read-only - it can still be saved as a new file, etc.
    In other words, I want them to start from scratch if they really want to change the data...
    Can this be done in Excel or VBA - or must I resort to 'faxing'?
    Thanks for any help..

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    For what it is worth, when I have wanted to do something similar I send the person a bitmap file from paint. This tends to have the effect that you are looking for.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    <<I know if you protect the sheet you can still get at the data. >>

    Not true, if all cells are locked (default situation) and you protect a sheet (with a password), cells can not be edited at all without unprotecting the sheet. You can also make sure formula's are made invisible (Format, cells, protecton, hidden).

    Lastly, to use the "screencopy" approach:
    - open an empty workbook
    - go to your source workbook
    - select the range you want published
    - hold the shift key and choose Edit, copy picture
    - select the empty wkbk and paste.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Yes, all this can be done in VBA. For the no-FileSave thing you need to add code to the BeforeSave event and tell it to cancel itself. This means only your code can save the WB, not the user though.
    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "No, can't save this WB!"
    Cancel = True
    End Sub
    </pre>

    The VBA code tiself can be hidden if you protect the project itself. You do ths from within the IDE, right-click on the project and select the protect tab, enter a password and select 'lock project from viewing'.

    Someone else answered the protecting cells thing. To hide worksheets completely (I do this alot since I use hidden sheets as databases or as work areas for intermediate data) you again need VBA to set the worksheet visible property to xlVeryHidden. The .visible property has three states, xlVisible, xlHidden, xlVeryHidden and it's this last one that makes the sheet not appear no matter what you do. It doesn't need to be protected as well since it can't be viewed.

    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Thanks for the "screencopy" instructions. I think this may help.
    But let me clarify by statement about "still getting to the data" with sheet protection on...
    A user can copy the data to a new workbook and then alter the data. My objective if to not assist
    them in their efforts -- just to provide the valid data.
    Let me play around with your suggestions - they may be a good alternative -- especially with the
    smaller file size using screencopy....
    Thanks again - phyl

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    I think there are a few other possibilities:
    1) you can make a pdf file from your workbook, setting the security to read-only and not allowed to print
    2) if you have vb6, you can use the clear method of the clipboard object (to use the clipboard object you must first run the program vb6clipboardsetupsetup.exe and you need to set a reference to the library "Clipboard Object" via Tools >> References in the Excel VBE. Of course, you need a VB6 license for this and I am not sure if this works in XL97)
    3) you can use a few lines of code, like this
    Application.EnableEvents = False
    Application.CutCopyMode = False
    Application.EnableEvents = True
    in the workbook_SheetChange event. However this will only work when you copy the contents of a sheet into another sheet of the same workbook. If you want to 'disable' the copy possibilities for the whole application when your workbook is 'running', then you need to make an application event, using class modules. I haven't tried it out myself, so it is a theoretical solution (I'll try it out for myself if I can free some more time).

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Thanks, Hans, but pdf may be too much for the folks who will use the process I define. Also, we do not
    have VB6 and my co-workers know nothing of VBA.
    So I am leaning twoard the "screencopy" approach for this - I think. That way there are no macros
    in the file - which means no warning messages when opened, etc. Also I do want them to be able
    to print the report.
    But your suggestion may be useful for something else.
    Now I just have to figure out how to use VBA to select and copy only the 'page break preview' (xlPageBreakPreview) for the current sheet with the 'copyPicture Appearance:=xlPrinter, Format:=xlPicture' code.....
    Thanks for your suggestions!
    Phyl

  8. #8
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Jan, I have played around with your "screencopy" suggestion and I like it!
    What I am trying to do is write a macro for several folks to use. After they create some Excel data that needs to be e-mailed as a file, they would run the macro to do a "screencopy" of the active sheet and copy it to a new workbook.
    Usually the reports are 1 page (not always).
    I can set the view to "pageBreak Preview" but can't figure out how to get VBA to copy just that view.
    I know I can get them to select data manually and then do a "set print area" before the use my macro but I wonder if there is a way in VBA to do it without "set Print area".
    The file usually is a couple of columns wider than the actual data (used cells) due to a company logo picture embedded in the top right corner of the worksheet....???
    Any suggestions?

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    For the level of protection you want you can try the following combination of operations to achieve your goal.

    1. Protect all work sheets with a password so that data cannot be altered.

    2. Save the document with a password to Open and a different password to Modify

    3. To prevent the use of Save As include the following code in the ThisWorkbookk object :<pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
    Cancel = True
    MsgBox "No changes allowed !!"
    End If
    End Sub</pre>

    This allows you to save changes if the workbook was opened with the password to modify, but prohibits making a copy of the workbook. If when opening the workbook, a passowrd to modify was not provided, then it should not be possible to save the workbook at all.

    4. If you also include the following code in the ThisWorkbook object, when the sheet is deactivated the contents of theclipboard are cleared so that you cannot paste to a different workbook.<pre>Private Sub Workbook_Deactivate()
    Application.CutCopyMode = False
    Range("A1").Copy ' this line optional, but an added precaution.
    End Sub</pre>

    5. Make sure that the entire VBA project is password protected.

    Hope that at leat some of teh above is of use

    Andrew C

  10. #10
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Andrew, Your comments and suggestions are most helpful and I can
    surely use them in another "To Do" that I have.
    However, I have decided that I should not go with regular protection and
    include macros - because of the warning the user will get when opening
    the workbook and also file size.
    Per my post #78874 - I am now desperately trying to find out how to
    use VBA to select ONLY the "Window.view = xlPageBreakPreview" area
    on the worksheet. So far I can only do it by manually setting the print area
    (which I don't want my co-workers to have to do) and then using VBA to grab it.
    The print area may have 1 or 2 empty columns on the right due to a
    company logo embedded in the upper right corner. So selecting "A1"
    thru SpecialCells(xlLastCell) is not a clean way to approach this.???
    My objective now is to "copy picture" approach suggested by Jan Karel's
    Post #78343.
    HELP Please!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: 'Double Protect' (XL97/sr2)

    Do macros have a big impact on file size?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Adding macros to workbooks increases their size, of course... but
    by how much depends on the size of the code.
    When I referred to file size in my comments above, I was referring to
    resulting file size when using the "screen shots" of the worksheets vs.
    copies of the actual worksheet.
    Adding macros to a file you distribute to a customer also carries with it
    the ole "warning - this file contains macros"......
    "Screen shots" are good for my objective here because of resulting file
    size and the fact that the customers can't do much but print the data
    or save it for historical data.--- That's all I want to assist them in doing.
    Maybe I should start a new thread.

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    I'm a little confused !

    You don't want Macros but want to use VBA to copy the Print Preview pane, which I do not think can be achieved. What am I missing ?

    If what you want to do is get an image of your workbook and place it in a new file so that it cannot be edited, you can do that that by setting the print area, press Gtrl-G and select Print-Area and then holding the Shift key, select Edit, Copy Picture and select the appropriate type of image (experiment) and then paste to a new workbook, which you can protect.

    Andrew C

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Hi Phyl,

    Why not record a macro while setting the print area and other properties after the copy? You'll get inefficient code, but it should work.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Double Protect' (XL97/sr2)

    Hi Andrew,
    Sorry to confuse you (I guess misery likes company...). Let me see it I can
    sort this out... In my post: 78878 I attempted to but...
    I am writing a macro that I will provide to several people in our department.
    They sometimes need to provide reports (usually 1-page) to a customer as an xls file via e-mail. Only my co-workers would be using the macro.
    It would not be included in the customer's report data. (so we are using a
    macro -- but not including it in the output file)
    My code sets the view of the active worksheet to 'xlPageBreakPreview' and then
    selects the 'pageBreakPreview' portion (right now by hook or crook),
    does the 'copyPicture Appearance:=xlPrinter.Format:=xlPicture', drops it into
    a new workbook, protects the new sheet..... etc......
    The resulting workbook is what is distributed to the customer and usually contains only 1 - 4 sheet tabs.
    A customer can print it and/or archive it. However, they can't
    open it and select a cell and compromise our data, i.e. not without
    manually recreating it themselves.

    Now - when my code does the page setup to fit to 1 sheet and then
    sets the view , I want to know HOW oh HOW to use VBA to select
    just that portion of the screen with that little blue border around it .
    As stated in my previous posts, not all cells / columns are populated.
    The original worksheet also has a company logo in the top right corner.

    I can use
    ActiveSheet.Range(Cells(1), Cells.SpecialCells(xlLastCell)).Select
    or the above with a column offset of 1 or 2 (to try an make sure the logo
    gets into the picture - but I am focused on trying to ger VBA to use 'xlPagePrintPreview' as criterial for the selection.
    How?
    Sorry for the long post - but I guess I needed to clarify. I know one of you
    experienced coders out there has the answer.
    Cheers, Phyl

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •