Conflicts with autorefresh in pivot table activating sheet protect

[israel.a.juarez]

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]

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).

 

[ShaneDevenshire]

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.

 

[israel.a.juarez]

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

 

[Dave Peterson]

I think the finest level that you can refresh is the individual pivottable.

 

[israel.a.juarez]

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]

Doesn't the code that was posted before do what you want?

I'm confused.

 

[israel.a.juarez]

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]

Just look in the earlier post to see the code I suggested.

I'm confuse too, it's possible that you can show me the complete code, with
your code in my initial code?.

thanks

 

[israel.a.juarez]

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]

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.

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

 

[israel.a.juarez]

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]

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???

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?

 

[israel.a.juarez]

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]

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.

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

 

[israel.a.juarez]

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