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

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Seanie" wrote:

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, that's very clever, I think it will work for me

Thanks

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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("SheetSelectionChanges", "Workbook") + 1
..InsertLines StartLine, msg1
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

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

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Seanie" wrote:

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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("SheetSelectionChanges", "Workbook") + 1
..InsertLines StartLine, msg1
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

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?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Seanie" wrote:

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("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("SheetSelectionChanges", "Workbook") + 1
..InsertLines StartLine, msg1
End With
Application.VBE.MainWindow.Visible = False
ErrH:
LockWindowUpdate 0&
End Sub

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

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"

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Seanie" wrote:

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

Excellent news, glad to be of service. Could you please mark my post as
helpful. Thanks.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Seanie" wrote:

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