Protect Excel Worksheet as UserInterFaceOnly || Expand/Collapse groups in protected Worksheet
#UserInterFaceOnlyProtection #VBA #Macro
Hello Friends,
In this video you will learn how to Expand and Collapse groups in excel while worksheet is protected. You will also learn what is “UserInterFaceOnly” Worksheet Protection in Excel VBA and how to Enable Outlining on protected worksheet using VBA.
Click here to download the practice file-
www.pk-anexcelexpert.com/prot...
Download our free Excel utility Tool and improve your productivity:
www.pk-anexcelexpert.com/prod...
See our Excel Products:
www.pk-anexcelexpert.com/prod...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
www.PK-AnExcelExpert.com
Facebook:
/ pkanexcelexpert
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Pinterest:
/ pkanexcelexpert
************ Suggested Books ********
VBA: amzn.to/2TMMikX
Excel Dashboard: amzn.to/2WZi2Fj
Power Query: amzn.to/2Ibd7xR
Power Pivot and Power BI: amzn.to/2DCg8BB
Exam Ref 70-778 (Power BI): amzn.to/2GnWYTN
************ My Stuff ***************
Mic : amzn.to/2TLnF88
Video Editor: screencast-o-matic.com/refer/...
amzn.to/2PlFFFz
Пікірлер: 74
Thank you! You are the Legend ^^ I'm Vietnamese
@PKAnExcelExpert
4 күн бұрын
Most welcome
This was exactly what i was looking for! I paired it with a private sub workbook open string and it works like a charm. Thank you!
@PKAnExcelExpert
4 жыл бұрын
Thanks for your valuable feedback
The perfect solution to the problem I was facing! Thanks!!!
@PKAnExcelExpert
4 жыл бұрын
Thanks for your valuable feedback
Thank you this helped me so much!
@PKAnExcelExpert
2 ай бұрын
You're so welcome!
It works. Thank you! A humble advice: The Video is too long and contains unnecessary information. A one minute or so video is more effective.. Thank you again and have a nice day!
Very helpful sir. Thank you
@PKAnExcelExpert
4 ай бұрын
Most welcome
This REALLY helped me out today, PK. Just what we needed. Thank you!
@PKAnExcelExpert
Жыл бұрын
Most welcome
Thanks for the video. Is there a way to apply this code to all worksheets in the workbook?
this solves my problem with my project. thank you!
@PKAnExcelExpert
2 жыл бұрын
Most welcome
mygosh! thank you for this video! been looking for this for a while now! Thankssssss!!!
@PKAnExcelExpert
3 жыл бұрын
Thanks for your valuable feedback
Gr8 PK, I liked userinterfaceonly:= True
@PKAnExcelExpert
5 жыл бұрын
Thanks Raj
Very helpful. Many thanks
@PKAnExcelExpert
8 ай бұрын
Glad it was helpful!
excellent explanation!
@PKAnExcelExpert
4 жыл бұрын
Thanks for your valuable feedback
Great video again
@PKAnExcelExpert
5 жыл бұрын
Thanks for your valuable feedback
Very useful!!
@PKAnExcelExpert
3 ай бұрын
Thanks
Great! I'm able to understand it now. Thank you sir! Have a great day! :D
@PKAnExcelExpert
4 жыл бұрын
Thanks for your valuable feedback
thank you!!!
@PKAnExcelExpert
Жыл бұрын
Most welcome
Fantastic video. Thanks for sharing your knowledge. Can you please explain how you would adjust the macro to apply the same expand and collapse to other multiple worksheets? Thanks in advance
Hats off!
@PKAnExcelExpert
4 жыл бұрын
Thanks for your valuable feedback
It’s great, but it only works for a single worksheet. Pls let us know how can it be done for multiple worksheets under a single workbook?
Awesome
@PKAnExcelExpert
5 жыл бұрын
Thanks for your appreciation. Please share it with your friends and colleagues.
Thanks for the video! It's very useful and easy to follow. Can I please ask from here, how do I allow users to re-group columns while still have the file protected? For example users can see 20 columns, 15 of which are grouped but since they have used 10 columns, user want to remove grouping on 5 columns.
Hi, this is a good one. do we need to change anything if we need this to be used for grouping rows instead of columns?
is there a way to use this userinterfaceonly function on two sheets in the same workbook
Hi, it's very useful VBA, actually meet the requirements. However I faced some problems, 1. it's worked fine for 1 sheet, however when I tried to write code for multiple sheets, sometimes it doesn't work. 2. When somehow I was able to make workable for multiple sheets, however stop working when workbook reopen, 3. Worksheet permission secured by password changed automatically. Can you please help to resolve these issues.
Hello thank you for sharing this! Is there a way to set column levels as a default when the workbook opens in addition to the protection and ability for users to use grouping buttons?
@alyssagoodman6711
4 жыл бұрын
For multiple worksheets
Does this macro also work on a shared workbook used by multiple users?
Hello sir, is it possible to also Enable Filters across multiple rows Aside from Enabling Outlining (which is the Grouping function)?? Thanks
👌👌👍👍👏👏👏
@PKAnExcelExpert
5 жыл бұрын
Thanks for your valuable feedback. Please share it with your friends and colleagues.
@behnamebrahimi1625
5 жыл бұрын
@@PKAnExcelExpert I'll do
its great learning for me...!, sir, its request to solve my query... Outlook VBA on send new email, itself extract all information in a workbook like, TO, CC, Subject, Date &Time.
@PKAnExcelExpert
5 жыл бұрын
Thanks for your valuable feedback. I will definitely try to make such video very soon.
@rameshchandragupta8060
5 жыл бұрын
@@PKAnExcelExpertthank for your attention,and I m waiting for that.
Thanks for the VBA Macros, however what if some one changed the excel sheet name? Macros get crashed 😲😯
Hi PK, I have a spreadsheet that contains some some charts and I found a UDF (set Chart axis)online that allows me to have the axes set automatically based on the values of certain cells. This works great and gives me very aesthetically pleasing charts. However, when I protect the sheet that contains the charts, the UDFs all results in a #VALUE! error. I need to enforce protection on these charts so end users have the ability to see them but not modify them. Is there a way to make the UDF work when the sheet is protected?
How do I use the VBA in this video when some cells are editable and we have the expand.
How to apply this vba to multiple sheets in the same workbook?
Is it possible to use a pivot table in a protected and shared workbook? Thanks a lot in advance!
@PKAnExcelExpert
5 жыл бұрын
Hi Nishant, Yes you can use a pivot table in protected worksheet. Make sure while you are protecting a worksheet just tick "Use Pivot Table & Pivot Chart" in the list.
Hi, how to apply this to multiple worksheet?
Kindly help me to solve this issue , i had created a dashboard for sales tracker pipeline and its working fine , but when i tried to apply hide/protect the formulas and the validations given in the table's conditional formatting and dropdown list box are not working after the first row in the table. Kindly assist me on this. Jogesh David Ross.
Dear Sir, how can i do this for multiple password protected worksheets of a single workbook?
Sir thanks for excellent videos. I got real soluations but I can not use || Expand/Collapse groups in protected Worksheet. I am using following code: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect "xxx" If VBA.IsEmpty(Target) Then Target.Locked = False Else Target.Locked = True End If ActiveSheet.Protect "xxx" End Sub How can I enable outlining commad in it? plz give me solution.
Sir I'm facing a problem All program working fine but Protect sheet not working find data Data searching issue Please suggest Thanx for advanced
Hi Pk, Just wanted to know if all the tabs are protected with the same password, how do we make this code effective for all the tabs? any help will be appreciated!! Thanks
Is this applicable for the worksheet which is already protected with their password !! Please advise
@PKAnExcelExpert
5 жыл бұрын
First unprotect your worksheet then you need to protect this again using the code which I have share in video.
Hi sir, Mujhe VBA code main multiple file merge into single file but duplicate not repeat now. And multiple file another location.
@PKAnExcelExpert
5 жыл бұрын
Hi Abhishek, You can copy the data with headers. Alternatively you can code the data with header and after consolidating you can delete the headers. You can watch below given videos. kzread.info/dash/bejne/nnapy9xthqqqqqQ.html kzread.info/dash/bejne/YmaJzdSwmdvPfcY.html kzread.info/dash/bejne/hqV7ldZpYandgrw.html kzread.info/dash/bejne/i6B21puek8XYl8o.html
Flash cell color auto
@sarsoklin7726
4 жыл бұрын
You explain not clearly Please don't speak a lot Please point step by step
Hi Excel Expert: SVP I have an Excel sheet which calculates an area for me, and then I create a VBA code to calculate this area with two parameters (Y, Z) and I want to have the automatic MIN area with its parameter Y; Z and here is my code and tell me the error. ------------------------------------------------------------------------------------------------------------------------------------------------------- Sub CalculSurfaceMin() Dim s As Double, sz As Double, sy As Double Dim x As Double, y As Double, z As Double Dim i As Long, j As Long Dim sz0 As Double, sy0 As Double sy0 = 10000 For i = 1 To 31 Step 1 y = 41 - i Cells(62, 18).Value = y sz0 = 10000 For j = 1 To 41 Step 1 z = 41 - j x = 100 - y - z Cells(61, 18).Value = x sz = Cells(64, 20).Value #### this cell contains a formula for calculating the area. If (sz sz0) Then Cells(54, 18).Value = y Cells(53, 18).Value = x Cells(55, 18).Value = z Cells(56, 18).Value = sz0 End If End If Next j sz0 = Cells(64, 20).Value If (sz0 sy0) Then Cells(54, 18).Value = y Cells(53, 18).Value = x Cells(55, 18).Value = z Cells(56, 18).Value = sy0 End If End If Next i End Sub ------------------------------------------------------------------------------------------------------
PASSWORD PROTECTED EXCEL TABLE NOT EXPANDING THE ROWS WHEN WE TYPING ANYTHING,HOW CAN FIX IT Sir..?
It’s great, but it only works for a single worksheet. Pls let us know how can it be done for multiple worksheets under a single workbook?