Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    passwords for each sheet (2003)

    Hi all,

    I have an excel spreadsheet which contains 50 sheets, I have also a macro which sends each sheet by email to different users, is there a ways to password protect each sheet with a different password?

    Your help is much appreciated.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    Yes, but how do you want to assign the passwords? If you want to be able to unprotect the sheets later on, you should know the passwords. If that isn't necessary, you could assign random passwords.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: passwords for each sheet (2003)

    Skender,

    Just use Tools > Protection > Protect sheet and specify a password. You can do this once for each sheet, specifying different passwords.

    Or did you mean from a Macro?

    StuartR

  4. #4
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    The way that macro works Is; when I run the macro each sheet is sent as an email attachment, so I would like to assign a different password for each, once I do that I can let the user know what the password is to access the sheet. the sheet is sent on its own, see the macro code below:

    Sub Mail_Every_Worksheet()
    'Working in 97-2007
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String

    TempFilePath = Environ$("temp") & ""

    If Val(Application.Version) < 12 Then
    'You use Excel 97-2003
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    'You use Excel 2007
    FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("K1").Value Like "?*@?*.?*" Then

    sh.Copy
    Set wb = ActiveWorkbook

    TempFileName = "Sheet " & sh.Name & " of " _
    & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    With wb
    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    On Error Resume Next
    .SendMail sh.Range("K1").Value, _
    "Detail P&L P10 "
    On Error GoTo 0
    .Close SaveChanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    End If
    Next sh

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub


    regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    OK, so you want to know the passwords. How do you want to process them? Do you want them stored in a sheet in a workbook, or in a text file, or ...?

  6. #6
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    They can be stored in a sheet. with this macro when they get the file they can just open it no problems but because the file is private and confidential i would like to protect it. so when next time the file is sent and the user opens it it should ask them for a password to access the file. The aim is to have a separate password on each tab below the e-mail address so that it will be password protected when it gets to the user


    cheers

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    See the code in the attached text file. I've added comments to the new code bits.
    Attached Files Attached Files

  8. #8
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    Hans,

    Will this create different password each time I sent the sheet? I just want to have 1 password for each sheet and keep it permanent.

    thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    Then YOU will have to tell us what password you want to use!

  10. #10
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    sorry Hansv i meant different passwords for each sheet but keep those passwords permanent.

    Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: passwords for each sheet (2003)

    You could take a different approach to this.
    <UL><LI>Manually assign a password for each sheet
    <LI>Modify your macro so that when it runs it saves the entire workbook to a temporary file name, deletes every sheet except the one you want, forwards the workbook, then reopens the original and starts at the next sheet.[/list]StuartR

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    I'd follow Stuart's suggestion.

  13. #13
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    Hi Stuart, the thing is, this protected info should not be viewed unless you type in password to open the file, protect sheet it only gives me option to protect cells format etc...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: passwords for each sheet (2003)

    So you don't want to set a password on the sheet, but on the workbook. You have to set this password when you save the workbook. Since you want to set the same password each time this happens, YOU will have to specify the passwords you want to use.

  15. #15
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passwords for each sheet (2003)

    hello again,

    Is there a way to do this password thing this way. i.e., to type the password on a cell on each sheet so each sheet has a different password, sheet one has password1 written on cell K2, sheet 2 has password 2 written on K2 and so on, so when the macro is run the sheet is sent to the each user for instance sheet1 has the email address where the sheet should be sent to on cell K1 and the password on cell 2 etc, and then I can set a password for each sheet on cell K2, call each user and let them know what the password is to open the file once they get the file.

    Thanks

    Skender

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
  •