Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Hi everyone. This is my third attempt at getting this question answered, so
I'm sorry if this question looks familiar to some of you but no one has answered the question yet. Here it is: I know that Excel 2000 disables the autofilter function when the worksheet is protected and hence copied and modified a macro into "ThisWorkbook" that would fix this. However, I also just read that the sorting function is also disabled in Excel 2000. So my question is...how would I modify my existing macro (or add a new macro) to also allow me to sort? Here is the macro that I have in "ThisWorkbook": Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Worksheet") If Not .AutoFilterMode Then ..Range("A1").AutoFilter End If ..EnableAutoFilter = True ..Protect Password:="temp", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Hi
I myself use somewhat simpler code Private Sub Workbook_Open() Sheets("SheetName").Unprotect Password:="MyPassword" Sheets("SheetName").Protect Password:="MyPassword", UserInterfaceOnly:=True Sheets("SheetName").EnableAutoFilter = True ' Repeat for all protected sheets End Sub Sorry, but it looks like Excel2000 doesn't allow to sort protected sheets - there exist no appropriate property for Sheet object you can change (like you can change EnableAutoFilter property for Sheet object). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "RS" wrote in message ... Hi everyone. This is my third attempt at getting this question answered, so I'm sorry if this question looks familiar to some of you but no one has answered the question yet. Here it is: I know that Excel 2000 disables the autofilter function when the worksheet is protected and hence copied and modified a macro into "ThisWorkbook" that would fix this. However, I also just read that the sorting function is also disabled in Excel 2000. So my question is...how would I modify my existing macro (or add a new macro) to also allow me to sort? Here is the macro that I have in "ThisWorkbook": Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Worksheet") If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="temp", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Dear Arvi,
Does your code apply the same password for all protected sheets in a workbook? In my case, I have differnet passwords for different sheets in the workbook. "Arvi Laanemets" wrote: Hi I myself use somewhat simpler code Private Sub Workbook_Open() Sheets("SheetName").Unprotect Password:="MyPassword" Sheets("SheetName").Protect Password:="MyPassword", UserInterfaceOnly:=True Sheets("SheetName").EnableAutoFilter = True ' Repeat for all protected sheets End Sub Sorry, but it looks like Excel2000 doesn't allow to sort protected sheets - there exist no appropriate property for Sheet object you can change (like you can change EnableAutoFilter property for Sheet object). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "RS" wrote in message ... Hi everyone. This is my third attempt at getting this question answered, so I'm sorry if this question looks familiar to some of you but no one has answered the question yet. Here it is: I know that Excel 2000 disables the autofilter function when the worksheet is protected and hence copied and modified a macro into "ThisWorkbook" that would fix this. However, I also just read that the sorting function is also disabled in Excel 2000. So my question is...how would I modify my existing macro (or add a new macro) to also allow me to sort? Here is the macro that I have in "ThisWorkbook": Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Worksheet") If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="temp", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Hi
When sheets have different passwords, then of-course for every sheet you apply the passing one. But Excel's password protection is too weak to stop a determined and skilled hacker anyway - whe he is able to break one, then he breaks them all. When you want to apply different PW for every sheet anyway, then probably the best solution will be to store sheet names and according passwords into 2D array. Something like (on fly): Private Sub Workbook_Open() Dim arrPW(10,2) arrPW(1,1)="Sheet1" arrPW(1,2)="password1" ... arrPW(10,1)="Sheet10" arrPW(10,2)="password10" For i=1 To 10 Sheets(arrPW(i,1)).Unprotect Password:=arrPW(i,2) Sheets(arrPW(i,1)).Protect Password:=arrPW(i,2), UserInterfaceOnly:=True Sheets(arrPW(i,1)).EnableAutoFilter = True Next i End Sub Arvi Laanemets "RS" wrote in message ... Dear Arvi, Does your code apply the same password for all protected sheets in a workbook? In my case, I have differnet passwords for different sheets in the workbook. "Arvi Laanemets" wrote: Hi I myself use somewhat simpler code Private Sub Workbook_Open() Sheets("SheetName").Unprotect Password:="MyPassword" Sheets("SheetName").Protect Password:="MyPassword", UserInterfaceOnly:=True Sheets("SheetName").EnableAutoFilter = True ' Repeat for all protected sheets End Sub Sorry, but it looks like Excel2000 doesn't allow to sort protected sheets - there exist no appropriate property for Sheet object you can change (like you can change EnableAutoFilter property for Sheet object). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "RS" wrote in message ... Hi everyone. This is my third attempt at getting this question answered, so I'm sorry if this question looks familiar to some of you but no one has answered the question yet. Here it is: I know that Excel 2000 disables the autofilter function when the worksheet is protected and hence copied and modified a macro into "ThisWorkbook" that would fix this. However, I also just read that the sorting function is also disabled in Excel 2000. So my question is...how would I modify my existing macro (or add a new macro) to also allow me to sort? Here is the macro that I have in "ThisWorkbook": Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Worksheet") If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="temp", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Dear Arvi,
Thanks for the reply. In my case, even though I have different passwords for different sheets, I only am using the macro for 1 sheet because that's the only one that I need to be able to filter and sort. So...my macro works fine in terms of sorting. I just wanted to make sure that your macro only protected the one sheet that I needed and didn't affect the other sheets. You mentioned that with multiple PWs that the "best solution will be to store sheet names and according passwords into 2D array" and then you gave a macro example. Would I have to have a separate sheet or section where I would paste the sheet names and pw's side by side and then use the code for that sheet, or would I simply past the code you provided (if I needed to use the macro for multiple sheets)? "Arvi Laanemets" wrote: Hi When sheets have different passwords, then of-course for every sheet you apply the passing one. But Excel's password protection is too weak to stop a determined and skilled hacker anyway - whe he is able to break one, then he breaks them all. When you want to apply different PW for every sheet anyway, then probably the best solution will be to store sheet names and according passwords into 2D array. Something like (on fly): Private Sub Workbook_Open() Dim arrPW(10,2) arrPW(1,1)="Sheet1" arrPW(1,2)="password1" ... arrPW(10,1)="Sheet10" arrPW(10,2)="password10" For i=1 To 10 Sheets(arrPW(i,1)).Unprotect Password:=arrPW(i,2) Sheets(arrPW(i,1)).Protect Password:=arrPW(i,2), UserInterfaceOnly:=True Sheets(arrPW(i,1)).EnableAutoFilter = True Next i End Sub Arvi Laanemets "RS" wrote in message ... Dear Arvi, Does your code apply the same password for all protected sheets in a workbook? In my case, I have differnet passwords for different sheets in the workbook. "Arvi Laanemets" wrote: Hi I myself use somewhat simpler code Private Sub Workbook_Open() Sheets("SheetName").Unprotect Password:="MyPassword" Sheets("SheetName").Protect Password:="MyPassword", UserInterfaceOnly:=True Sheets("SheetName").EnableAutoFilter = True ' Repeat for all protected sheets End Sub Sorry, but it looks like Excel2000 doesn't allow to sort protected sheets - there exist no appropriate property for Sheet object you can change (like you can change EnableAutoFilter property for Sheet object). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "RS" wrote in message ... Hi everyone. This is my third attempt at getting this question answered, so I'm sorry if this question looks familiar to some of you but no one has answered the question yet. Here it is: I know that Excel 2000 disables the autofilter function when the worksheet is protected and hence copied and modified a macro into "ThisWorkbook" that would fix this. However, I also just read that the sorting function is also disabled in Excel 2000. So my question is...how would I modify my existing macro (or add a new macro) to also allow me to sort? Here is the macro that I have in "ThisWorkbook": Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Worksheet") If Not .AutoFilterMode Then .Range("A1").AutoFilter End If .EnableAutoFilter = True .Protect Password:="temp", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Any macro experts out there?
Hi
"RS" wrote in message ... Dear Arvi, Thanks for the reply. In my case, even though I have different passwords for different sheets, I only am using the macro for 1 sheet because that's the only one that I need to be able to filter and sort. So...my macro works fine in terms of sorting. I just wanted to make sure that your macro only protected the one sheet that I needed and didn't affect the other sheets. You mentioned that with multiple PWs that the "best solution will be to store sheet names and according passwords into 2D array" and then you gave a macro example. Would I have to have a separate sheet or section where I would paste the sheet names and pw's side by side and then use the code for that sheet, or would I simply past the code you provided (if I needed to use the macro for multiple sheets)? Generally you determine sheet names and their passwords directly in declarations part of procedure - after you have dimensioned the array (of -course you can write sheet names ind passwords directly into code for protecting/unprotecting sheets, but you'll have more work when you afterwards want to change passwords, or to add protection to additional sheets). Of-course you can read them from worksheet too, but it'll be hardly wise to have them there. In case you anyway want passwords managed from some worksheet, then: Create a separate sheet for them; On this sheet, enter the list of protected sheets, and according passwords (2-column table); In Project Explorer window, set this sheet to be very hidden; In Workbook's Open event, after dimensioning arrPW, read protected sheet names and passwords into array (you have to declare array arrPW as Variant, then you must estimate the number of protected sheet in passwords table, redim the array, and only after that read sheet names and passwords into array); When you are finished, you have to protect your VBA Project with password (then VBA editor will be available only through password, so other users can't unhide the very hidden sheet and will not be aware of it's existence at all). An alternate way to read a table from sheet into array is to define the table as a (dynamic) named range. In VBA procedure (in Open event in your case) you can read values from named range into a range object. .... Dim rngPW As Range rngPW=[PWNamedRange] .... But there is a drawback - users will have access to list of named ranges, whenever there is some unprotected sheet in workbook. Form Named Ranges wizard, they can see definitions of all named ranges - so they'll know the name of your hidden sheet, and where there the info is stored. And they can read this info into any unprotected cell - simply typing a link formula into it. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
link to combobox | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions |