How to prevent copy and paste over cell with drop down list in Excel?
For a cell with data validation drop down list, when copy content from another cell and paste to it, the drop down list will be gone and replaced with the new pasting content. In some cases, you want to prevent copy and paste other content over the cell with drop down list, what would you do to deal with it? In this article, you will get a VBA method of dealing with this problem.
1. Open the worksheet with the drop down list cell you want to prevent copy and paste.
2. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic Applications window.
3. In the Microsoft Visual Basic Applications window, double click the current worksheet name in the left pane, then copy and paste the below VBA code into the Code editor. See screenshot:
VBA code: Prevent copy and paste over cell with drop down list
Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 20200903 Dim xValue As String Dim xCheck1 As String Dim xCheck2 As String Dim xRg As Range Dim xArrCheck1() As String Dim xArrCheck2() As String Dim xArrValue() Dim xCount, xJ As Integer Dim xBol As Boolean ' If Target.Count > 1 Then ' Exit Sub ' End If xCount = Target.Count ReDim xArrCheck1(1 To xCount) ReDim xArrCheck2(1 To xCount) ReDim xArrValue(1 To xCount) Application.EnableEvents = False On Error Resume Next xJ = 1 For Each xRg In Target xArrValue(xJ) = xRg.Value xArrCheck1(xJ) = xRg.Validation.InCellDropdown xJ = xJ + 1 Next Application.Undo xJ = 1 For Each xRg In Target xArrCheck2(xJ) = xRg.Validation.InCellDropdown xJ = xJ + 1 Next xBol = False For xJ = 1 To xCount If xArrCheck2(xJ) <> xArrCheck1(xJ) Then xBol = True Exit For End If Next If xBol Then MsgBox "The selected cells containg data validation drop-down lists, no pasting allowed." Else xJ = 1 For Each xRg In Target xRg.Value = xArrValue(xJ) xJ = xJ + 1 Next End If Application.EnableEvents = True End Sub
4. Press Alt + Q keys to exit the Microsoft Visual Basic Applications window.
5. When you paste content to a cell with drop down list, a dialog box will pop up to remind you the pasting is no allowed. See screenshot:
Note: This VBA just work in single cell pasting. If you copy and paste in multiple cells, it won’t work.
Easily create drop-down list with checkboxes in Excel:
The Drop-down List with Check Boxes utility of Kutools for Excel can help you easily create drop-down list with checkboxes in a specified range, current worksheet, current workbook or all opened workbooks based on your needs.
Download the full feature 30-day free trail of Kutools for Excel now!
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!