Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet protect
I have this code and without the sheet protection works good, but when i
activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet protect
I'm assuming that this code is behind Sheet2.
Private Sub WorkSheet_Change(ByVal Target As Range) with me .unprotect password:="TopSecret" .PivotTables(1).PivotCache.refresh .protect password:="TopSecret" end with End Sub Me refers to the object that owns the code--in this case, it's the worksheet being changed (and that has that pivottable). wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet protect
Hi,
You can turn protection on with a macro like this Sub myProtect() ActiveSheet.Protect Password:="myPassword", UserInterfaceOnly:=True End Sub The key here is the UserInterfaceOnly:=True which allows VBA code to execute against a protected sheet. I haven't tested this with a pivot table but it should work. -- Thanks, Shane Devenshire " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet protect
thanks Dave but i tried to use it but is not exaclty that I'm looking for or
maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet protect
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
Doesn't the code that was posted before do what you want?
I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
I'm confuse too, it's possible that you can show me the complete code, with
your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
Just look in the earlier post to see the code I suggested.
wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
i tried the code like this:
Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
You can only have a single worksheet_change event under any worksheet.
I'm confused at what you're changing and where the pivottable is. If the data and pivottable are on different sheets (say sheet1 for the data and the pivottable is on sheet2), then you only need the second version. Delete the first procedure. If the data and pivottable are on the same sheet, then delete the second procedure. wrote: i tried the code like this: Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
is there a way that I can send you my file and also one power point with the
explanation in order to show you what i'm trying to do? "Dave Peterson" wrote: You can only have a single worksheet_change event under any worksheet. I'm confused at what you're changing and where the pivottable is. If the data and pivottable are on different sheets (say sheet1 for the data and the pivottable is on sheet2), then you only need the second version. Delete the first procedure. If the data and pivottable are on the same sheet, then delete the second procedure. wrote: i tried the code like this: Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
No thanks.
You'd have to describe the problem anyway. So why not do it here? On the other hand, maybe someone else will chime in and ask you to send the file to them??? wrote: is there a way that I can send you my file and also one power point with the explanation in order to show you what i'm trying to do? "Dave Peterson" wrote: You can only have a single worksheet_change event under any worksheet. I'm confused at what you're changing and where the pivottable is. If the data and pivottable are on different sheets (say sheet1 for the data and the pivottable is on sheet2), then you only need the second version. Delete the first procedure. If the data and pivottable are on the same sheet, then delete the second procedure. wrote: i tried the code like this: Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
ok, I tried to that way because i think is easier to see in some picture or
..ppt file, but let me try again i have one sheet, in that sheet i have the data and the pivot table. the data will come from some scanner in determinate range of cells ( for example: data:C5:E150), every time that the operator use the scanner to read the bar code it will appear in that specific range in that sheet . And I want that the pivot do the autorefresh, everytime, all that I mentioned in the first paragraph I'm ok, and I can do that, but I don't want that the operator by error use another cell that doesn't belong to the range C5:E150 (Data), for that reason i want to protect the whole cells, except for that specific range C5:E150 and also I want that this protection will allow the autorefresh in the pivot table every time that the operator use the scanner, The reason to this is because I have another file that take information from this pivot table, that other file show me the remaining of production and I want to have the real information every time that i open the second file (production remaining). I hope this explanation can help, and please remember that you are talking with somebody that don't know nothing about codes. thanks and best regards "Dave Peterson" wrote: No thanks. You'd have to describe the problem anyway. So why not do it here? On the other hand, maybe someone else will chime in and ask you to send the file to them??? wrote: is there a way that I can send you my file and also one power point with the explanation in order to show you what i'm trying to do? "Dave Peterson" wrote: You can only have a single worksheet_change event under any worksheet. I'm confused at what you're changing and where the pivottable is. If the data and pivottable are on different sheets (say sheet1 for the data and the pivottable is on sheet2), then you only need the second version. Delete the first procedure. If the data and pivottable are on the same sheet, then delete the second procedure. wrote: i tried the code like this: Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
Is the pivottable on the same sheet as the data?
You didn't share that. Either way, I don't have anything to add to the previous message. wrote: ok, I tried to that way because i think is easier to see in some picture or .ppt file, but let me try again i have one sheet, in that sheet i have the data and the pivot table. the data will come from some scanner in determinate range of cells ( for example: data:C5:E150), every time that the operator use the scanner to read the bar code it will appear in that specific range in that sheet . And I want that the pivot do the autorefresh, everytime, all that I mentioned in the first paragraph I'm ok, and I can do that, but I don't want that the operator by error use another cell that doesn't belong to the range C5:E150 (Data), for that reason i want to protect the whole cells, except for that specific range C5:E150 and also I want that this protection will allow the autorefresh in the pivot table every time that the operator use the scanner, The reason to this is because I have another file that take information from this pivot table, that other file show me the remaining of production and I want to have the real information every time that i open the second file (production remaining). I hope this explanation can help, and please remember that you are talking with somebody that don't know nothing about codes. thanks and best regards "Dave Peterson" wrote: No thanks. You'd have to describe the problem anyway. So why not do it here? On the other hand, maybe someone else will chime in and ask you to send the file to them??? wrote: is there a way that I can send you my file and also one power point with the explanation in order to show you what i'm trying to do? "Dave Peterson" wrote: You can only have a single worksheet_change event under any worksheet. I'm confused at what you're changing and where the pivottable is. If the data and pivottable are on different sheets (say sheet1 for the data and the pivottable is on sheet2), then you only need the second version. Delete the first procedure. If the data and pivottable are on the same sheet, then delete the second procedure. wrote: i tried the code like this: Private Sub WorkSheet_Change(ByVal Target As Range) With Me .Unprotect Password:="TopSecret" .PivotTables(1).PivotCache.refresh .Protect Password:="TopSecret" End With End Sub Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub but it shows me that error compile error Ambiguous name detected: Worksheet_Change i don't know why it shows me that error, I don't have experience with codes, i'm new in this kind of work, please tell me how can i fix it. thanks and best regards "Dave Peterson" wrote: Just look in the earlier post to see the code I suggested. wrote: I'm confuse too, it's possible that you can show me the complete code, with your code in my initial code?. thanks "Dave Peterson" wrote: Doesn't the code that was posted before do what you want? I'm confused. wrote: ok, then i need to have the data and the pivot in different sheets? i tried but i could get the result, I'm not to familiar with codes, could you please tell me how can I do that "Dave Peterson" wrote: I think the finest level that you can refresh is the individual pivottable. wrote: thanks Dave but i tried to use it but is not exaclty that I'm looking for or maybe I'm doing something wrong. Let me try to explain and be more specific, I have the data and the pivot in the same sheet, the first code that i had was to auto refresh in the same sheet but it takes some time to auto refresh, i think the reason to this is because i'm using ("Sheet2") and take time to read the complete sheet, is there an option to just auto refresh an specific range of cells that contains the data? ( for example: data:C5:E150, pivot table: H5:N150). after that I want to protect the complete sheet except the C5:E150, and when somebody put some data the pivot auto refresh automatically even with the protect sheet, please tell me if this is more clear. thanks and best regards " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conflicts with autorefresh in pivot table activating sheet pro
Shane
I hope you are fine, I will try to show with words what i'm trying to do: i have one sheet, in that sheet i have the data and the pivot table. the data will come from some scanner in determinate range of cells ( for example: data:C5:E150), every time that the operator use the scanner to read the bar code it will appear in that specific range in that sheet . And I want that the pivot do the autorefresh, everytime, all that I mentioned in the first paragraph I'm ok, and I can do that, but I don't want that the operator by error use another cell that doesn't belong to the range C5:E150 (Data), for that reason i want to protect the whole cells, except for that specific range C5:E150 and also I want that this protection will allow the autorefresh in the pivot table every time that the operator use the scanner, actually if I activete the sheet protection the pivot doesn't auto refresh and also it shows me an error The reason to this is because I have another file that take information from this pivot table, that other file show me the remaining of production and I want to have the real information every time that i open the second file (production remaining). I hope this explanation can help, and please remember that you are talking with somebody that don't know nothing about codes. thanks and best regards "ShaneDevenshire" wrote: Hi, You can turn protection on with a macro like this Sub myProtect() ActiveSheet.Protect Password:="myPassword", UserInterfaceOnly:=True End Sub The key here is the UserInterfaceOnly:=True which allows VBA code to execute against a protected sheet. I haven't tested this with a pivot table but it should work. -- Thanks, Shane Devenshire " wrote: I have this code and without the sheet protection works good, but when i activate the sheet protection it shows me an error, the code is as follows: Private Sub WorkSheet_Change(ByVal Target As Range) Sheets("Sheet2").PivotTables(1).PivotCache.refresh End Sub is there an option to avoid this error? regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PROTECT PIVOT TABLE | Excel Discussion (Misc queries) | |||
Protect part of pivot table | Excel Discussion (Misc queries) | |||
macro sheet name conflicts | Excel Worksheet Functions | |||
Pivot table with protect workbook | Excel Discussion (Misc queries) | |||
Sheet protection code conflicts with Pivot Table "auto refresh" | Excel Discussion (Misc queries) |