Prevent Copy/Paste Q

[Seanie]

I have the very simple code below that stops printing of a Document,
is it possible to have just as simple a code that will prevent Copy/
Paste from the Tool bar?

I've tried - ActiveSheet.EnableSelection = xlNoSelection. This works
when a user opens in XL but not Excel 2000

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

 

[Alan Moseley]

What about putting this code in your worksheet code window. It just changes
the selection to A1 regardless of what they select:-

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sh.Range("A1").Select
End Sub

 

[Seanie]

Alan, that's very clever, I think it will work for me

Thanks

 

[Seanie]

I have tried to create a macro that would insert the above in to This
Workbook by it is not appearing. Code I have tried is as below, any
idea what I've done wrong? I have other pieces of code that I have
used to populate TW and modified accordingly, but it is not appearing
and no errors are showing



Sub Populate_TW_3()

Dim StartLine As Long
Dim msg1 As String

Dim VBEHwnd As Long
On Error GoTo ErrH:
Application.VBE.MainWindow.Visible = False
VBEHwnd = FindWindow("wndclass_desked_gsk", _
Application.VBE.MainWindow.Caption)
If VBEHwnd Then
LockWindowUpdate VBEHwnd
End If

msg1 = "Sh.Range(""A1"").Select"

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("SheetSelectionChanges", "Workbook") + 1
..InsertLines StartLine, msg1
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

 

[Alan Moseley]

How are you calling Populate_TW_3(). Does it need to be marked as public?

 

[Alan Moseley]

Try removing your error handling and see what happens. I assume that your
addin / whatever has a reference to be able to FindWindow and
lockWindowUpdate?

 

[Seanie]

Alan, on removing Err handler it points to this line, explaining Event
Handler is Invalid

StartLine = .CreateEventProc("SheetSelectionChanges", "Workbook") + 1

I haven't placed the text.. (ByVal Sh As Object, ByVal Target As
Range) but when I do (as per below) I get the same error message

StartLine = .CreateEventProc("SheetSelectionChanges(ByVal Sh As
Object, ByVal Target As Range)", "Workbook") + 1

 

[Alan Moseley]

I have never done this sort of thing before to be honest, but are you sure
that the event name is "SheetSelectionChanges". I thought that it was
"SheetSelectionChange"

 

[Seanie]

Alan, you are of course correct and when I changed it, it of course
worked as intended. That was something one could look hours at and not
spot

Thanks for your assistance

 

[Alan Moseley]

Excellent news, glad to be of service. Could you please mark my post as
helpful. Thanks.