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!
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?
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.
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
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.
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.
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.
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?
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
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.
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.
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. th-cam.com/video/lBuiz4VGDzA/w-d-xo.html th-cam.com/video/02UkrwixiMc/w-d-xo.html th-cam.com/video/TqG3t01FwRY/w-d-xo.html th-cam.com/video/YlBt9ecbrgg/w-d-xo.html
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 ------------------------------------------------------------------------------------------------------
Thanks for the video. Is there a way to apply this code to all worksheets in the workbook?
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!
Thanks for your valuable feedback
Thank you! You are the Legend ^^ I'm Vietnamese
Most welcome
This REALLY helped me out today, PK. Just what we needed. Thank you!
Most welcome
this solves my problem with my project. thank you!
Most 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!
is there a way to use this userinterfaceonly function on two sheets in the same workbook
The perfect solution to the problem I was facing! Thanks!!!
Thanks for your valuable feedback
mygosh! thank you for this video! been looking for this for a while now! Thankssssss!!!
Thanks for your valuable feedback
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?
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?
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 sir, is it possible to also Enable Filters across multiple rows Aside from Enabling Outlining (which is the Grouping function)?? Thanks
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
Thank you this helped me so much!
You're so welcome!
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?
Very helpful sir. Thank you
Most welcome
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.
Thank yo so much !! could you pls let me know how to run this same macro automatically when open this excel file ? Please.
Put this code on thisworkbook open event
Does this macro also work on a shared workbook used by multiple users?
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.
Very helpful. Many thanks
Glad it was helpful!
Gr8 PK, I liked userinterfaceonly:= True
Thanks Raj
Is it possible to use a pivot table in a protected and shared workbook? Thanks a lot in advance!
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.
excellent explanation!
Thanks for your valuable feedback
Great! I'm able to understand it now. Thank you sir! Have a great day! :D
Thanks for your valuable feedback
Dear Sir, how can i do this for multiple password protected worksheets of a single workbook?
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?
For multiple worksheets
Thanks for the VBA Macros, however what if some one changed the excel sheet name? Macros get crashed 😲😯
How to apply this vba to multiple sheets in the same workbook?
Very useful!!
Thanks
Hi, how to apply this to multiple worksheet?
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
Great video again
Thanks for your valuable feedback
How do I use the VBA in this video when some cells are editable and we have the expand.
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
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.
Thanks for your valuable feedback. I will definitely try to make such video very soon.
@@PKAnExcelExpertthank for your attention,and I m waiting for that.
Is this applicable for the worksheet which is already protected with their password !! Please advise
First unprotect your worksheet then you need to protect this again using the code which I have share in video.
Hats off!
Thanks for your valuable feedback
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.
thank you!!!
Most welcome
Awesome
Thanks for your appreciation. Please share it with your friends and colleagues.
Hi sir,
Mujhe VBA code main multiple file merge into single file but duplicate not repeat now. And multiple file another location.
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.
th-cam.com/video/lBuiz4VGDzA/w-d-xo.html
th-cam.com/video/02UkrwixiMc/w-d-xo.html
th-cam.com/video/TqG3t01FwRY/w-d-xo.html
th-cam.com/video/YlBt9ecbrgg/w-d-xo.html
👌👌👍👍👏👏👏
Thanks for your valuable feedback. Please share it with your friends and colleagues.
@@PKAnExcelExpert I'll do
PASSWORD PROTECTED EXCEL TABLE NOT EXPANDING THE ROWS WHEN WE TYPING ANYTHING,HOW CAN FIX IT Sir..?
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
------------------------------------------------------------------------------------------------------
Flash cell color auto
You explain not clearly
Please don't speak a lot
Please point step by step
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?