Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrict Users | Excel Discussion (Misc queries) | |||
i need availibility chart for employees | Charts and Charting in Excel | |||
Macro to restrict printing to black and white | Excel Discussion (Misc queries) | |||
RESTRICT PASTE | Excel Worksheet Functions | |||
Restrict Highlighting? | Excel Discussion (Misc queries) |