Hi Friends ,
I am using named ranges as data source in pivot. These named ranges can be selected by user from a combobox. I change data source of the pivot on the basis of named range selected from combobox.
My code works fine if the sheet of the selected name range in combobox is unprotected.
To make my code work I tried to unprotect the worksheet in code and then protect at the end . A Part of my code is :
Here DD_Sell is my combobox name . It contains the list of named ranges. I have named the ranges same as of worksheet name. For example if the work sheet name is "Sell" then named range is "Sell". "Reports" is the worksheet on which pivot exists.
Please help. This code give error Ïnvalid Procedure or argument". But if the data source sheet is unprotected and I remove unprotect code statement then it works fine.
I tried allowing use of "Locked cells in pivottable"" while protecting sheet but it also doesn't help. So I guess unprotecting the worksheet in the code is the only way out.
I am using named ranges as data source in pivot. These named ranges can be selected by user from a combobox. I change data source of the pivot on the basis of named range selected from combobox.
My code works fine if the sheet of the selected name range in combobox is unprotected.
To make my code work I tried to unprotect the worksheet in code and then protect at the end . A Part of my code is :
HTML:
Sub ChangeSellDataSource()
With ActiveSheet.Shapes("DD_Sell").ControlFormat
dd_val = .List(.ListIndex)
Sheets(dd_val).Unprotect Set Data_sht = ThisWorkbook.Worksheets(dd_val) Set Pivot_sht = ThisWorkbook.Worksheets("reports")Here DD_Sell is my combobox name . It contains the list of named ranges. I have named the ranges same as of worksheet name. For example if the work sheet name is "Sell" then named range is "Sell". "Reports" is the worksheet on which pivot exists.
Please help. This code give error Ïnvalid Procedure or argument". But if the data source sheet is unprotected and I remove unprotect code statement then it works fine.
I tried allowing use of "Locked cells in pivottable"" while protecting sheet but it also doesn't help. So I guess unprotecting the worksheet in the code is the only way out.
Last edited:
