Restrict Macro Availibility
 

Hi,

I am trying to restrict users to run a macro, only if ActiveCell is in
a specific range of a Pivot Table ...
Is there a simple way out ?

TIA
Carim

Restrict Macro Availibility
 

Maybe something like this:

Option Explicit
Sub testme()
Dim PT As PivotTable
Set PT = Nothing
On Error Resume Next
Set PT = ActiveCell.PivotTable
On Error GoTo 0
If PT Is Nothing Then
MsgBox "hey, select a pt."
Else
MsgBox PT.Name
End If
End Sub


(Hi, Deb!)

Carim wrote:

Hi,

I am trying to restrict users to run a macro, only if ActiveCell is in
a specific range of a Pivot Table ...
Is there a simple way out ?

TIA
Carim

--

Dave Peterson

Restrict Macro Availibility
 

Hi Dave,

Thanks a lot for your input.
My ultimate objective is to have a "worksheet-selection-change" type of
solution ... i.e a command button which is only visible when user is
located inside the pivot table in range ("C2 : B90").

Your help would be greatly appreciated.

Cheers
Carim

Restrict Macro Availibility
 

Hi Dave,

Thanks a lot for your input.
My ultimate objective is to have a "worksheet-selection-change" type of
solution ... i.e a command button which is only visible when user is
located inside the pivot table in range ("C2 : B90").

Your help would be greatly appreciated.

Cheers
Carim

Restrict Macro Availibility
 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape
Dim PT As PivotTable

Set myShape = Me.Shapes("commandbutton1")
myShape.Visible = False

Set PT = Nothing
On Error Resume Next
Set PT = ActiveCell.PivotTable
On Error GoTo 0
If PT Is Nothing Then
'keep it hidden
Else
myShape.Visible = True
End If
End Sub

I put a commandbutton from the Control toolbox toolbar on the worksheet. I
named it commandbutton1 and it worked ok.

Carim wrote:

Hi Dave,

Thanks a lot for your input.
My ultimate objective is to have a "worksheet-selection-change" type of
solution ... i.e a command button which is only visible when user is
located inside the pivot table in range ("C2 : B90").

Your help would be greatly appreciated.

Cheers
Carim

--

Dave Peterson

Restrict Macro Availibility
 

Dave,

Many thanks ... it works beautifully...
I thought I had tried it this way, but it never worked ...
There will always be a hell of the difference between the pros ...
and the tiny amateurs ...
Cheers
Carim

Restrict Macro Availibility
 

Dave,

Sorry to be back again, but is there a way to push if further ... i.e
down to a couple of columns of the pivot table ...???

TIA

Restrict Macro Availibility
 

Like if it's in the data range???

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape
Dim PT As PivotTable

Set myShape = Me.Shapes("commandbutton1")
myShape.Visible = False

Set PT = Nothing
On Error Resume Next
Set PT = ActiveCell.PivotTable
On Error GoTo 0
If PT Is Nothing Then
'keep it hidden
Else
If Intersect(Target.Cells(1), PT.DataBodyRange) Is Nothing Then
'keep it hidden
Else
myShape.Visible = True
End If
End If
End Sub



Carim wrote:

Dave,

Sorry to be back again, but is there a way to push if further ... i.e
down to a couple of columns of the pivot table ...???

TIA

--

Dave Peterson

Restrict Macro Availibility
 

Dave,

Thanks to your insight, I have discovered I had to dig into the pivot
table Object...
Next to DataBodyRange which includes all data, there is, for example :
..RowRange.Columns(1).Cells
to select data subsets ...

Thanks again for your precious help
Cheers
Carim

Restrict Macro Availibility
 

There are a lot of ranges in those pivottables. Glad you found the one you
wanted.

Carim wrote:

Dave,

Thanks to your insight, I have discovered I had to dig into the pivot
table Object...
Next to DataBodyRange which includes all data, there is, for example :
.RowRange.Columns(1).Cells
to select data subsets ...

Thanks again for your precious help
Cheers
Carim

--

Dave Peterson