|
Use VBA to Apply various Security Levels in Workbooks
I have a workbook with three worksheets. i would like to protect the
worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Private Sub Workbook_Open() ' in the thisworkbook module
Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom,
Do you think those passwords would stand up to a dictionary attack ? <g NickHK "Tom Ogilvy" ... Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Did you put it in the thisworkbook module?
why change from thisworkbook? Are events enabled when the book is opened? -- Regards, Tom Ogilvy "Tricia Young" wrote: Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom, I have never used VBA for Excel. I'm sorry. I should have specified
that in the beginning. I have only used it for Access. "Tom Ogilvy" wrote: Did you put it in the thisworkbook module? TY - How do I create a module why change from thisworkbook? TY - I thought the module had to be the same name as my excel workbook. Are events enabled when the book is opened? TY - Unknown -- Regards, Tom Ogilvy "Tricia Young" wrote: Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
Use VBA to Apply various Security Levels in Workbooks
Tom, I figured out what I was doing wrong. The code you supplied works great
and i understand each line. Another question...I would like for the AutoFilter feature to be active/enable with the read-only. " No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter." "Tom Ogilvy" wrote: Did you put it in the thisworkbook module? why change from thisworkbook? Are events enabled when the book is opened? -- Regards, Tom Ogilvy "Tricia Young" wrote: Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then paste the code, then i replaced all instances of "thisworkbook" with the title of my excel workbook. I save the file and closed it. when i reopened it nothing happened. did i miss something? Thx, Tricia "Tom Ogilvy" wrote: Private Sub Workbook_Open() ' in the thisworkbook module Dim sh as worksheet, pword as String pword = inputBox("Enter Password or click Cancel") if pword = "" then for each sh in thisworkbook.worksheets sh.Visible = True sh.Activate sh.Protect Password:="StrongLikeABull" next elseif lcase(pword) = "huggybear" then thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 1" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 1") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "toiletpaper" then thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 2" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 2") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With elseif lcase(pword) = "triciayoung" then thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible for each sh in thisworkbook.Worksheets if sh.Name < "Sheet 3" then sh.Visible = xlSheetHidden end if Next With thisworkbook.Worksheets("Sheet 3") if .ProtectContents then .Unprotect "StrongLikeABull" end if end With End if End Sub You can play with that. http://www.cpearson.com/excel/events.htm for an overview of events (Chip Pearson's site) -- Regards, Tom Ogilvy "Tricia Young" wrote: I have a workbook with three worksheets. i would like to protect the worksheets from editting. But it's complicated. upon opening there should be an option to type in one of three passwords. 1st PW will open the workbook and only show Sheet 1 - with no restrictions 2nd PW will open the workbook and only show Sheet 2 - with no restrictions 3rd PW will open the workbook and only show Sheet 3 - with no restrictions No PW will open the workbook and show all Sheets - with restrictions; users can on view the info and use AutoFilter. How can I do this with VBA code? Thanks |
| All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com