Limit Copying VBA

[jamesmev]

I am looking for a VBA solution to limit the number of cells that can be copied at one time within a workbook.

The data contains emails and I am needing to secure them to where only one can be selected at any given time.

Any solutions?

 

[Fennek]

Hello,

define the key-shortcut "strg-c", e.g. with

sub T1()
application.OnKey "^c", "MyCopy"
end sub

best in the workbook.open event.

Then to check for the number of rows:

sub MyCopy()
debug.print selection.rows.count
end sub

regards

 

[jamesmev]

This isn't working to only select one cell at a time. It is allowing multiple cells still. I must be missing something, i apologize.

 

[lhartono]

Try this,

[COLOR=#a9a9a9]'
'this code will limit cells selection to 1 row, 3 columns only, anywhere within this sheet.
'[/COLOR]
[COLOR=#0000cd]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const maxRows = 1
Const maxCols = 3

Application.EnableEvents = False
If (Target.Rows.CountLarge > maxRows) Or (Target.Columns.CountLarge > maxCols) Then
    Target.Resize(maxRows, maxCols).Select
End If
Application.EnableEvents = True
End Sub[/COLOR]

 

[lhartono]

You can also place the code in Thisworkbook module.
By doing this, the selection limit will be applied to every worksheet.

[COLOR=#0000cd]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const maxRows = 1
Const maxCols = 3

Application.EnableEvents = False
If (Target.Rows.CountLarge > maxRows) Or (Target.Columns.CountLarge > maxCols) Then
    Target.Resize(maxRows, maxCols).Select
End If
Application.EnableEvents = True

End Sub[/COLOR]