PDA

View Full Version : not allow copy



clarksonneo
05-05-2011, 06:32 AM
Hi,

I want to create a macro.

I want that the macro not permit user to copy anything from column B.

For example:
the macro will not permit

(a) right click --> copy
(b) Ctrl + C

Thanks

shrivallabha
05-05-2011, 07:12 AM
Is there any reason NOT to use, protect worksheet option?

clarksonneo
05-05-2011, 07:19 AM
Is there any reason NOT to use, protect worksheet option?

i know the protect worksheet option.

the reason to ask this question is that I want to improve my VBA knowledge.

thanks

shrivallabha
05-05-2011, 08:18 AM
For disabling control c in column B, use this worksheet event code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
'***********************************************************
'^ stands for Control
'c stands for Copy
'"" Nothing is assigned
'***********************************************************
Application.OnKey "^c", ""
Else
'***********************************************************
'Resetting for other columns
'***********************************************************
Application.OnKey "^c"
End If
End Sub

clarksonneo
05-06-2011, 06:27 AM
For disabling control c in column B, use this worksheet event code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
'***********************************************************
'^ stands for Control
'c stands for Copy
'"" Nothing is assigned
'***********************************************************
Application.OnKey "^c", ""
Else
'***********************************************************
'Resetting for other columns
'***********************************************************
Application.OnKey "^c"
End If
End Sub thank you, your code works.
Can I ask you 2 further questions?

Question1:

It works only if the name of the macro is either

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
or
Sub Worksheet_SelectionChange(ByVal Target As Range)
.

Is it possible to change the name of the macro?


Question2:
I can't "see (and so can't run)" that macro via the macro dialog box.
Why?

Thanks

shrivallabha
05-06-2011, 08:00 AM
Answer 1] No. Unlike other macros, this macro is associated with a specific object "worksheet" (worksheet level). Second part is associated with the specific event when the macro will initiate. These are predefined events (built in) wherein you can specify / change the outcome but can NOT change declaration syntax.

Answer 2] Probably that is the way they have been designed ("Private" by nature / default). I know I will be taken to task on forums like these for saying things in a silly way. But the code trigger lies in the events that occur in Excel. And that is not all. You can decide the macros which will appear in Macros that you create. For instance:
Private Sub MyMacro()
will not appear in dialog box whereas,
Sub MyMacro1() or Public Sub MyMacro2()
will appear. VBA by default considers general macros to be Public.
Keywords Private / Public define scope of macro.

clarksonneo
05-06-2011, 10:00 PM
Answer 1] No. Unlike other macros, this macro is associated with a specific object "worksheet" (worksheet level). Second part is associated with the specific event when the macro will initiate. These are predefined events (built in) wherein you can specify / change the outcome but can NOT change declaration syntax.

Answer 2] Probably that is the way they have been designed ("Private" by nature / default). I know I will be taken to task on forums like these for saying things in a silly way. But the code trigger lies in the events that occur in Excel. And that is not all. You can decide the macros which will appear in Macros that you create. For instance:
Private Sub MyMacro() will not appear in dialog box whereas,
Sub MyMacro1() or Public Sub MyMacro2() will appear. VBA by default considers general macros to be Public.
Keywords Private / Public define scope of macro.
i know your answer to answer 2.
that is true for all "normal" macros.

However, that is not true for my macros.

I can't see none of the following 3 macros via the macro dialog box

(1)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
(2)
Sub Worksheet_SelectionChange(ByVal Target As Range)
(3)
Public Sub Worksheet_SelectionChange(ByVal Target As Range)

macropod
05-06-2011, 11:44 PM
Macros that take parameters (as your's do) don't appear in the macros dialogue box because you can't pass the required parameters to them. This can actually be used to hide macros that can be used with the 'private' declaration - simply give them a dummy optional parameter (eg: Sub MyMacro(Optional Dummy As Boolean)). You can also hide all macros in a module by putting 'Option Private Module' at the top of the module.

shrivallabha
05-07-2011, 12:19 AM
Google has really made life easy. Clarksonneo, here is the second part (requirement a of post#1) of your requirement. This thread is on ozgrid.
http://www.ozgrid.com/forum/showthread.php?t=75911&page=1

Here is the version for your case, credits Ozgrid:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
Application.CommandBars("Cell").Controls("Copy").Visible = False
Else
Application.CommandBars("Cell").Controls("Copy").Visible = True
End If
End Sub


Thank you Paul for your explanation. Its very useful.