Check if "move or copy" is being used in Excel 1

[koresnordic]

HI,

I have a Excel sheet that has pivot tables that pull data direct from sensitive company data. I have no problem with the end user drilling down to the data inside the spreadsheet, but I do not want them to be able to right click on the sheet and select "move or copy" to another sheet, whilst retaining the connection. I have no problem with them copying to another workbook, as they will sometimes need to send data to other people, but I want the connection broken, so the end recipient cannot drill down into more detail. So disabling the right click option isn't really the best option. Is there a way of detecting if they are performing a move / copy and removing the connections prior to this? I already do this before they save or close the workbook to prevent them just sending the original file with the data. I also remove any sheets over and above the originals prior to both events. I could remove the connections directly after the pivot tables have been updated in the file, but if they then want to drill down inside the original file, they will be unable to do so, which they will often need to do.

If it isn't possible then I will have to just disable the option and create my own VBA equivalent to move to a new workbook without the connections.

Thanks




Graham

 

[SkipVought]

hi,

In the workbook_SheetSelectionChange event, assign the Application.CutCopyMode to FALSE

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Application.CutCopyMode = FALSE
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[koresnordic]

HI Skip,

thanks for the prompt reply, but I do want them to be able to copy and paste to another workbook, just need to remove the data connections prior to this.



Graham

 

[SkipVought]

Why not use MS Query or ActiveX Data Objects to extract to a sheet, ONLY the data you want the users to see, and then Pivot that data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[koresnordic]

HI Skip,
I do

' Create the connection
' strSQL contains the SQL script selecting just the fields I need to subtotal by. The table the data comes from has more detail than in the pivot, which the
' end user will need to see when they double click on the totals
Workbooks("Cogmotive reports.xlsm").Connections.Add2 "Query from Cogmotive", "", "ODBC;DSN=Cogmotive;", strSQL, 2 

' Create the pivot table
Sheet_Name = Sheet_Name & "!R1C1"
' Setup to use the newly created connection
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("Query from Cogmotive"), Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=Sheet_Name, TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15

Select Case Selected
' Select the appropriate subtotal - going down the left column
    Case "Summary by day"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("datte")
            .Orientation = xlRowField
            .Position = 1
        End With
        
    Case "Summary by user"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("email_address")
            .Orientation = xlRowField
            .Position = 1
        End With
        
End Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("entity_name")
    .Orientation = xlPageField
    .Position = 1
End With

' Add the fields that are to be totalled.
With ActiveSheet.PivotTables("PivotTable1").PivotFields("incoming")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("outgoing")
    .Orientation = xlColumnField
    .Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("not_known")
    .Orientation = xlColumnField
    .Position = 3
End With

Select Case Selected

    Case "Summary by user"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("datte")
            .Orientation = xlPageField
            .Position = 2
        End With

End Select
     
' Add the column headers
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("incoming"), "Total incoming", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("outgoing"), "Total outgoing", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("not_known"), "Total unknown", xlSum

But as I do not remove the connection, they can still drill down to the details of the pivot (i.e. by double clicking on a total for incoming, they can see all the lines that make that up, rather than just the total), which is a facility they need. But I do not want them to copy that sheet to another workbook, to pass onto someone else, who will be able to double click themselves to get more detail. The reason they would need to copy to another workbook to pass details onto someone is because I remove all sheets prior to a save, so any other user looking at the file would not see anything they are not authorised to see (I have password protected various report type, so they can only run the options they are allowed to).

If it can't be done that I pick up the request to move / copy the sheet, I will have to disable it in total and add a button to copy to another workbook and copy the sheet into there, without the connections.

regards



Graham

 

[SkipVought]

That is NOT what I suggested!

I suggested that you use MS Query to return ONLY the data you want the user to see to a sheet. You can HIDE this sheet if you want.

Then, on another sheet, pivot the imported data table sheet. The PivotTable can be copied. [highlight #FCE94F]Your source data is in your workbook, decoupled from the sensitive company data.[/highlight]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[koresnordic]

Ah, got it .

I will try that.

thanks



Graham

 

[SkipVought]

No problem

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

A bit uncomfortable, but:
1. To prevent copying cells: set worksheet's protection, disable permission to select unlocked cells, enable working with pivot table.
2. To prevent copying worksheet: protect workbook's structure.
3. Copying data: macro copying the worksheet, next removing protection of copy and copying and pasting as values pivot table range (to the same range).


combo