How do I disable a user from cutting and pasting in Excel 2

[gautammalkani]

Hi All

I would like the user to manually enter the data, use the drop down list box or externally reference the data but I do not want the user cutting and pasting since it would change hte format of the template (ie colors, borders etc).

1) Is there a way of allowing them to cut and paste into the template without changing the format

2) Should I just disable the user from cutting and pasting. If so, how do i do that but allow them to do other forms of entry as mentioned?

Thanks

Gautam

 

[SkipVought]

Hi,

What you have to do is -- YOU control the format. There are any number of ways that a user can change a range format in addition to copy 'n' paste.

Your wrap-up program needs to validate the portions of your workbook that you need to maintain. Use the Before_Save event to trigger this process.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[gautammalkani]

Hi Skip

I have locked the format so that they cannot change the fill/borders/colors but they can still copy and paste from another sheet which can change the format of the cells.

I ideally wouldnt like the user to use the before save event since they will not be required to save the template.

Is there any way to detect if the format has changed when the data is pasted by the user from either another spreadsheet or another program?

Thanks for any help I can get

Gautam

 

[SkipVought]

Set up a phantom sheet that has a visible property of xlsheetveryhidden. (Only can be changed to xlsheetvisible in the VB editor or with VB code so the user will never know its there.)

Copy the CORRECT formats to this sheet.

In the Before_save event, copy the formats from the phantom sheet to the display sheet.

VOLA!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[gautammalkani]

Hi Skip

That worked in a general situation for me. But I have one problem. My sheet currently has a header and item level part. The item level part only displays one row. The user inputs the number of item level rows that he/she needs. The template then creates rows in the color/format/border as required. Therefore, a hidden sheet with the same format doesnt seem to work since the format of the sheet is dynamic (based on user input). Please let me know if you have any ideas.

Thanks

Gautam

 

[SkipVought]

then, as I stated before...

What you have to do is -- YOU control the format. There are any number of ways that a user can change a range format in addition to copy 'n' paste.

Your wrap-up program needs to validate the portions of your workbook that you need to maintain. Use the Before_Save event to trigger this process.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[rmikesmith]

Gautam,

Here is a different approach you can try. Basically, it allows the user to paste data into cells without disturbing the existing formatting. It accomplishes this by hooking into the Paste menuitems and the Ctrl-v key sequence, replacing the built-in action with a custom procedure that invokes the PasteSpecial command using the Values only option. Here is the code to do this, including a routine to restore normal command behavior:

Sub HookPasteCommands()
Dim MenuControls As CommandBarControls
Dim MenuItem As CommandBarButton

   Set MenuControls = Application.CommandBars.FindControls(ID:=22)
   For Each MenuItem In MenuControls
     MenuItem.OnAction = "CustomPaste"
   Next MenuItem
   Application.OnKey "^v", "CustomPaste"
   
End Sub


Sub ResetPasteCommands()
Dim MenuControls As CommandBarControls
Dim MenuItem As CommandBarButton

   Set MenuControls = Application.CommandBars.FindControls(ID:=22)
   For Each MenuItem In MenuControls
     MenuItem.OnAction = ""
   Next MenuItem
   Application.OnKey "^v"
   
End Sub


Sub CustomPaste()
   
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlPasteValues
   
End Sub

This solution does have some deficiencies which may or may not be a problem for you. For instance, text copied from outside Excel will not paste. There may be ways to work around that but I haven't explored it.

Hope this helps,
Mike

 

[rmikesmith]

An afterthought: If you use the above approach, you will also likely want to temporarily disable cell drag & drop, as this will also transfer formatting, wiping out the existing formatting.

Application.CellDragAndDrop = False

Mike

 

[gautammalkani]

Hi Mike

That helped greatly. However, it didn't seem to protect the format during a cut and paste from Excel.

Is there a way of detecting if it was cut (vs. copied) from Excel?

Is there a way of repressing the user to "cut and paste" from excel as well?

Thanks

Gautam

 

[rmikesmith]

Guatam,

I've modified my solution to handle a cut and paste operation. In effect, it simulates the cut & paste. This requires some additional coding. First, declare a Public variable CurrentRng in the code module (where the existing procedures reside):

Public CurrentRng As Range

Next, in the code module for the worksheet or worksheets that could serve as the source of a copy/cut place the following event handler & code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   On Error Resume Next
   If Application.CutCopyMode = False Then
     Set CurrentRng = Target
   End If
End Sub

Finally, substitute this modified version of the CustomPaste procedure for the current one:

Sub CustomPaste()
   
   On Error Resume Next
   If Application.CutCopyMode = xlCut Then
     CurrentRng.Copy
     Selection.PasteSpecial Paste:=xlPasteValues
     Application.CutCopyMode = False
     CurrentRng.ClearContents
   Else
     Selection.PasteSpecial Paste:=xlPasteValues
   End If
  
End Sub

If instead, you want to prevent a cut & paste, skip the above and substitute the following CustomPaste procedure:

Sub CustomPaste()
   
   On Error Resume Next
   If Application.CutCopyMode = xlCut Then
     Application.CutCopyMode = False
     MsgBox "Cut & Paste operation not allowed"
   Else
     Selection.PasteSpecial Paste:=xlPasteValues
   End If
End Sub

If you want to prevent cut, copy and paste, use this version of CustomPaste:

Sub CustomPaste()
   
   On Error Resume Next
   Application.CutCopyMode = False
   MsgBox "Cut & Paste and Copy & Paste operations not allowed"
End Sub

Regards,
Mike

 

[gautammalkani]

Hi Mike

That worked great!!!! Thanks so much

Gautam