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

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

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

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

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

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

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

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

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