none
Restricting use of paste in Excel VBA RRS feed

  • Question

  • I have a sheet with drop down cells (Data Validation)

    I want to allow the user to be able to select an item or to be able to paste special a value in one of the cells

    But I don't want them to be able to paste a value thus deleting the Data Validation settings

    Is there a way of recognising that a user is changing a cell via Paste allowing me to display a warning message if they are trying to do that ?

    This would be using VBA code behind an Excel 2007 application

    <input id="1e774253-24ad-46cb-8bd4-2fa9a1b68789_attachments" type="hidden" />
    Wednesday, October 5, 2011 3:03 AM

Answers

  • Hi Jeff,

    If using VBA then don't need a warning. Simply use the code to replace the data validation. Copy the following code into the work sheets module. Not sure of your expertise in VBA so here are some guidelines in case required.

    Right click the worksheet tab name and select View Code. This will open the VBA editor in the Worksheets module.

    Copy the following code and paste into the white area of the editor.

    Private Sub Worksheet_Change(ByVal Target As Range)
      On Error GoTo ReEnableEvents
      Application.EnableEvents = False

      'Edit "E1" in the next line to the cell with drop down validation
      If Not Intersect(Target, Range("E1")) Is Nothing Then
          With Target.Validation
            .Delete
           
            'Edit Sheet2!$A$1:$A$26 in the following to the
            'data validation list range.
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$26"
             
            'Some of the following may need to be edited depending
            'on the options that you set in the data validation.
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
          End With
        End If
     
    ReEnableEvents:
      Application.EnableEvents = True
    End Sub

    You will need to edit the code where the green comments indicate to do this.

    If you have set options in the data validation then you will need to re-code the options. The easiest way to do this:

    Return to worksheet. (Alt/F11 toggles between VBA editor and your worksheet.)

    Turn on the VBA recorder

    Select the cell with validation

    Open Data validation

    Just click OK to close the dialog box.

    Close (Stop) the VBA recorder. (Warning do not forget to do this at this point.)

    Excel will think you have just set all the validation and record it.

    To get to the recorded code, Alt/F11 to toggle to the VBA editor and double click Module1 in the project explorer left column.

    Copy ONLY the part of the code that looks like the following example code and paste it into the appropriate section of the code in the worksheet module to replace the existing similar code. (Double click the worksheet module name in the project explorer to change modules.)

        'Example recorded code
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Sheet2!$A$1:$A$26"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "My Title"
            .InputMessage = ""
            .ErrorMessage = "Wrong"
            .ShowInput = True
            .ShowError = True

    Close editor (X with red background top right of editor window.)

    Save the workbook (If using xl2007 or later then save as Macro enabled.)

    You will need to enable macros. See Help for how to do this.

    Now every time a change is made in the validated cell, the data validation code is automatically called and reset the validation so it will not matter if the user copies another cell over it.


    Regards, OssieMac
    • Edited by OssieMac Wednesday, October 5, 2011 4:51 AM
    • Marked as answer by jeff Whale Thursday, October 20, 2011 2:01 AM
    Wednesday, October 5, 2011 4:48 AM

All replies

  • Hi Jeff,

    If using VBA then don't need a warning. Simply use the code to replace the data validation. Copy the following code into the work sheets module. Not sure of your expertise in VBA so here are some guidelines in case required.

    Right click the worksheet tab name and select View Code. This will open the VBA editor in the Worksheets module.

    Copy the following code and paste into the white area of the editor.

    Private Sub Worksheet_Change(ByVal Target As Range)
      On Error GoTo ReEnableEvents
      Application.EnableEvents = False

      'Edit "E1" in the next line to the cell with drop down validation
      If Not Intersect(Target, Range("E1")) Is Nothing Then
          With Target.Validation
            .Delete
           
            'Edit Sheet2!$A$1:$A$26 in the following to the
            'data validation list range.
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$26"
             
            'Some of the following may need to be edited depending
            'on the options that you set in the data validation.
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
          End With
        End If
     
    ReEnableEvents:
      Application.EnableEvents = True
    End Sub

    You will need to edit the code where the green comments indicate to do this.

    If you have set options in the data validation then you will need to re-code the options. The easiest way to do this:

    Return to worksheet. (Alt/F11 toggles between VBA editor and your worksheet.)

    Turn on the VBA recorder

    Select the cell with validation

    Open Data validation

    Just click OK to close the dialog box.

    Close (Stop) the VBA recorder. (Warning do not forget to do this at this point.)

    Excel will think you have just set all the validation and record it.

    To get to the recorded code, Alt/F11 to toggle to the VBA editor and double click Module1 in the project explorer left column.

    Copy ONLY the part of the code that looks like the following example code and paste it into the appropriate section of the code in the worksheet module to replace the existing similar code. (Double click the worksheet module name in the project explorer to change modules.)

        'Example recorded code
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Sheet2!$A$1:$A$26"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "My Title"
            .InputMessage = ""
            .ErrorMessage = "Wrong"
            .ShowInput = True
            .ShowError = True

    Close editor (X with red background top right of editor window.)

    Save the workbook (If using xl2007 or later then save as Macro enabled.)

    You will need to enable macros. See Help for how to do this.

    Now every time a change is made in the validated cell, the data validation code is automatically called and reset the validation so it will not matter if the user copies another cell over it.


    Regards, OssieMac
    • Edited by OssieMac Wednesday, October 5, 2011 4:51 AM
    • Marked as answer by jeff Whale Thursday, October 20, 2011 2:01 AM
    Wednesday, October 5, 2011 4:48 AM
  • Doh!!

    Isn't the simple solution always the best

    The real dumb thing is that I had already written code to create the drop downs in the first place as the contents vary on different circumstances

    I just hadn't made the simple leap of reusing it - trying to be too clever by half

    I'd still be interested to see if operations like Paste CAN be detected but only at an academic level

     

    Thanks a lot OssieMac


    • Edited by jeff Whale Thursday, October 6, 2011 7:25 AM
    Wednesday, October 5, 2011 11:55 PM