Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
Alan, that's very clever, I think it will work for me
Thanks |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
Prevent Copy/Paste Q
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 |
All times are GMT +1. The time now is 09:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com