not allow copy

[clarksonneo]

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]

Is there any reason NOT to use, protect worksheet option?

[clarksonneo]

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]

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]

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]

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:
[VBA]Private Sub MyMacro()[/VBA]
will not appear in dialog box whereas,
[VBA]Sub MyMacro1()[/VBA] or [VBA]Public Sub MyMacro2()[/VBA]
will appear. VBA by default considers general macros to be Public.
Keywords Private / Public define scope of macro.

[clarksonneo]

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]

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]

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/showthre...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.