Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by lucavilla
at 2024-08-03 01:10:49
ModifyPoint:500 Replies:15 POST_ID:828676USER_ID:11566
Topic:
Microsoft Excel Spreadsheet Software;;VB Script
(Excel 2010)
When programming in VBA I somethimes need to use methods that require to unprotect (unlock) the active sheet before being executed and to reprotect (lock) it when done.
For example the ShowAllData method.
Unfortunately the sheets are protected with different "allow" flags enabled/disabled and I want to leave those settings untouched by my script.
So... how can I save and restore the protection settings (flags)?
When programming in VBA I somethimes need to use methods that require to unprotect (unlock) the active sheet before being executed and to reprotect (lock) it when done.
For example the ShowAllData method.
Unfortunately the sheets are protected with different "allow" flags enabled/disabled and I want to leave those settings untouched by my script.
So... how can I save and restore the protection settings (flags)?
Author: lucavilla replied at 2024-08-08 13:22:14
impressive byundt!
Your sub works perfectly without modifications on my Excel 2010!
I love you!!!
I would to give you 5000 points!
Your sub works perfectly without modifications on my Excel 2010!
I love you!!!
I would to give you 5000 points!
Expert: byundt replied at 2024-08-08 13:13:48
You may find that recording a macro will expose the settings that are present in your version of Excel for comparison to those in Excel 2013.
Author: lucavilla replied at 2024-08-08 13:08:25
ah, great!
now I try it, adapting it to the limited settings of Excel 2010
now I try it, adapting it to the limited settings of Excel 2010
Expert: byundt replied at 2024-08-08 12:55:43
Statements 8 to 12 capture the original settings. Statements 15 to 31 reapply them along with UserInterfaceOnly:=True
Author: lucavilla replied at 2024-08-08 12:51:06
byundt I don't understand because I see a single sub.
What's the part that reads/saves the original settings and what's the part that restores them?
What's the part that reads/saves the original settings and what's the part that restores them?
Expert: byundt replied at 2024-08-07 04:39:59
Is it really impossible to detect the original settings and restore them at the end of my macros?
It is quite possible to detect the original settings and restore them at the end of the macro. That's exactly what the code in my last post is doing.
As written, it runs on Excel 2013 and restores all the protection features supported by that version of Excel. Some of those features, however, are not available on earlier versions of Excel. These unavailable features will cause run-time errors when the code is run on those earlier versions.
You will need to determine the oldest version of Excel that will be in use by one of your macro users. You then have a choice of either simplifying the code to support just that oldest version, or testing the version in the code and executing different statements for each one.
Author: lucavilla replied at 2024-08-07 03:04:49
Wait, the big problem is that I'm in a company where there are many Excel files with different protection settings.
I just want to use the ShowAllData command in a macroes in my PERSONAL.XLSB over all these files without disrupting their protection settings.
Is it really impossible to detect the original settings and restore them at the end of my macroes?
I just want to use the ShowAllData command in a macroes in my PERSONAL.XLSB over all these files without disrupting their protection settings.
Is it really impossible to detect the original settings and restore them at the end of my macroes?
Accepted Solution
Expert: byundt replied at 2024-08-04 15:23:04
500 points EXCELLENT
After further testing, you don't need to unprotect the worksheet to apply protection with UserInterfaceOnly:=True. If you want to maintain the same protection properties, however, you will need to set them in the Workbook_Open sub that applies UserInterfaceOnly:=True.
The following code was tested in Excel 2013. If you have an earlier version of Excel, you may encounter a run-time error on the protection statement. That's because protection properties were added with later versions of Excel. Try to protect a worksheet, and note the options available. Any that you can't match up to the .Protect statement in the code are candidates for deletion.
The following code was tested in Excel 2013. If you have an earlier version of Excel, you may encounter a run-time error on the protection statement. That's because protection properties were added with later versions of Excel. Try to protect a worksheet, and note the options available. Any that you can't match up to the .Protect statement in the code are candidates for deletion.
Private Sub Workbook_Open()Dim ws As WorksheetDim pro As ProtectionDim proEditRanges As AllowEditRangesDim proEditRange As AllowEditRangeDim bContents As Boolean, bDrawing As Boolean, bScenarios As BooleanWith Worksheets("Sheet1") 'Change worksheet name to suit Set pro = .Protection bContents = .ProtectContents bDrawing = .ProtectDrawingObjects bScenarios = .ProtectScenarios Set proEditRanges = pro.AllowEditRanges 'The following properties are valid for Excel 2013. _ If you get a run-time error, delete any properties that don't exist in your version of Excel. .Protect Password:="abc", UserInterfaceOnly:=True, _ DrawingObjects:=bDrawing, Contents:=bContents, Scenarios:=bScenarios, _ AllowFormattingCells:=pro.AllowFormattingCells, _ AllowFormattingColumns:=pro.AllowFormattingColumns, _ AllowFormattingRows:=pro.AllowFormattingRows, _ AllowInsertingColumns:=pro.AllowInsertingColumns, _ AllowInsertingRows:=pro.AllowInsertingRows, _ AllowInsertingHyperlinks:=pro.AllowInsertingHyperlinks, _ AllowDeletingColumns:=pro.AllowDeletingColumns, _ AllowDeletingRows:=pro.AllowDeletingRows, _ AllowSorting:=pro.AllowSorting, _ AllowFiltering:=pro.AllowFiltering, _ AllowUsingPivotTables:=pro.AllowUsingPivotTables If proEditRanges.Count > 0 Then For Each proEditRange In proEditRanges .Protection.EditRanges.Add proEditRange Next End IfEnd WithEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:
Expert: byundt replied at 2024-08-04 04:33:43
The UserInterfaceOnly property is not saved with the file, unfortunately. It must be set each time you open the file, as well as each time you apply protection.
The UserIntefaceOnly trick to allow macros to work without removing sheet protection works in many cases, but not all. But when it does work, as in your current situation, it is a very nice trick indeed.
Brad
The UserIntefaceOnly trick to allow macros to work without removing sheet protection works in many cases, but not all. But when it does work, as in your current situation, it is a very nice trick indeed.
Brad
Author: lucavilla replied at 2024-08-04 01:59:16
Wow thanks byundt! it worked!
If it's not possible (as alternative) to save and restore protection settings I will change the protection on all the files (a lot) with your "UserInterfaceOnly:= True" method.
If it's not possible (as alternative) to save and restore protection settings I will change the protection on all the files (a lot) with your "UserInterfaceOnly:= True" method.
Expert: byundt replied at 2024-08-03 10:34:52
When you open the workbook, unprotect the worksheet, then reprotect it with UserInterfaceOnly parameter set to True. You can then run your macro while the worksheet is protected.
Worksheets("Sheet1").Protect Password:="somepassword", UserInterfaceOnly:= True
Worksheets("Sheet1").Protect Password:="somepassword", UserInterfaceOnly:= True
Author: lucavilla replied at 2024-08-03 02:01:45
The problem is very easy.
Set an autofilter and filter something.
Then execute this code:
Set an autofilter and filter something.
Then execute this code:
Sub test1() With ActiveSheet If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If Else If .FilterMode Then .ShowAllData End If End If End WithEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:
Results:
if sheet is unprotected -> it removes your filter
if sheet is protected -> it crashes on the ShowAllData line with a 1004 error
I would to avoid the crash (keeping the same logic).
PS: manually removing and reenabling the protection is not an option. I've to use it often and over many files of other users.
Expert: duncanb7 replied at 2024-08-03 01:33:00
A litte bit confused, did you read this, if please write your thread much clear
http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP010078580.aspx
http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP010078580.aspx
Author: lucavilla replied at 2024-08-03 01:26:29
No, they modify the autofilter but it can't modify the autofilter if sheet si protected.
Expert: duncanb7 replied at 2024-08-03 01:20:37
Is that what your want
http://www.mrexcel.com/forum/excel-questions/228818-showalldata.html
http://www.mrexcel.com/forum/excel-questions/228818-showalldata.html
