0

I have an Excel sheet that contains multiple non-contiguous drop-down columns. I can protect these drop-down columns from typing any unlisted entry thru cell validations. But I cannot protect them from cell overlay copy/paste. I used a macro from another post on this site, but that works only for one column in the NamedRange. (Restrict paste into dropdown cells in Excel)

I tried to combine the NamedRanges with Union and with CombinedRange syntax and run the macro. But it doesn't work for multiple drop-down columns.

Can anyone please help me with this?

4
  • (1) Did you actually get the answer from that other question to work?  I tried it and it failed. (Whenever I typed into any cell, the Worksheet_Change function called itself recursively until Excel ran out of stack; then Excel aborted.)  (2) What do you mean by “CombinedRange syntax”?  What did you try? Jun 19, 2021 at 20:03
  • extendoffice.com/documents/excel/… Is this worked for you?
    – Lee
    Jun 21, 2021 at 9:15
  • @Scott - 1. Yes, it worked for me, but only for 1 col. Ex. - i have dropdowns for col E, F, G and O. When i put a NamedRange for E5:E20 & use it in the macro, it works fine. Cell overlay copy/paste is blocked. I also have generic cell validations in place to prevent typing in the dropdown cols. But when i try multiple NamedRange for E & F & use them in the macro, it goes into infinite loop & excel closes. 2. I used UNION - Sub ColsUnion() Dim rng1 As Range Set rng1 = Union(Range("E5:E20"), Range("F5:F20")) rng1.Select End Sub This dont work. Same loop error & excel closes.
    – rabiswas
    Jun 21, 2021 at 9:31
  • OK, I figured out my  problem (it was a stupid error). Jun 21, 2021 at 21:52

1 Answer 1

0

I took the answer to the linked question “Restrict paste into dropdown cells in Excel” and adapted it for multiple ranges, and made some cosmetic changes.  I also added a safety net to catch recursion.  You may be able to delete the bold/italic lines:

Dim WSC_Recursive As Boolean
 
Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If WSC_Recursive Then
        Exit Sub
    End If
    If HasValidation(Range("DataValidationRange1")) And _
       HasValidation(Range("DataValidationRange2")) And _
       HasValidation(Range("DataValidationRange3")) And _
       HasValidation(Range("DataValidationRange4")) Then
          Exit Sub
    End If
    WSC_Recursive = True
    Application.Undo
    WSC_Recursive = False
    MsgBox "Error: You cannot paste data into these cells." & vbCrLf & _
          "Please use the drop-down to enter data instead.", vbCritical
End Sub
 
Private Function HasValidation(r) As Boolean
    'Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

Note: each of the NamedRanges must have validation applied to it — in fact, it must have the same validation applied to every cell in the range.  For example, you cannot have a range of G17:H42 if G17:G42 has one type of validation and H17:H42 has a different type — or even if they have the same type (e.g., drop-down) but different parameters (e.g., different lists of valid values).

If you have this set up and working, and you want to add a range, you should:

  1. In either order,

    • define the named range and
    • apply the validation to the cells

    and only then

  2. add the new range to the Worksheet_Change routine.

In the more general case where you’re changing validation rules, you might want to follow this procedure:

  1. Rename the Worksheet_Change routine to something else (e.g., Worksheet_ChangeX).
  2. In either order,
    • make your changes to the sheet, and
    • (if necessary) change the code, e.g., change
      HasValidation(Range("DataValidationRange2")) And _
      
      to
      HasValidation(Range("DataValidationRange2a")) And _
      HasValidation(Range("DataValidationRange2b")) And _
      
    and then
  3. Rename the routine back to Worksheet_Change.

Personally, I believe that this code could be designed better, but, if it works, it works.

4
  • Scott - apologies for a delayed reply. Your code works at 1st shot. But, if I change the NamedRange range or add/delete dropdown cols, the code runs into errors. Sometimes its Runtime Error 1004: Method 'Range' of object '_Worksheet' failed when using cells. Sometimes its an infinite loop for _Change event running the macro on itself, and it goes into every cell in the worksheet. I tried to reset 'Application.EnableEvents = False' and True, but it didnt work. So, im still stuck :) Additionally, I also need to add many-to-one dependent dropdown lists for 2 cols.. uurgh :(
    – rabiswas
    Jun 27, 2021 at 16:44
  • (1) Yeah, if one of your ranges does not have validation applied to it — in fact, if it does not have the same validation applied to every cell in the range — then the routine will blow up.  That’s an example of what I was talking about when I said that the code was designed badly.  (2) It’s really weird that you’re getting the 1004 error.  Actually, it’s normal for the routine to get the error, but the On Error Resume Next statement should prevent you from ever seeing it. … (Cont’d) Jun 27, 2021 at 20:15
  • (Cont’d) …  (3) Do you get the (infinite) loop even with my WSC_Recursive code?  Because (as I mentioned in my comment) I was getting the exact same problem, and the WSC_Recursive code fixed it. … … … … … … … … … … … … … … … … … … … … … … … … … I have added some guidance to my answer.  If you can provide some more details about what’s going wrong, I might be able to help. Jun 27, 2021 at 20:15
  • Scott - I followed your guidance, but the issue continues. 1. I have 4 cols (as of now) with each col as each NamedRange & each has a different Data List validation. DataValidationRangeD = 'MassUpload Estimation Templ v3'!$D$5:$D$255 DataValidationRangeE, DataValidationRangeF, DataValidationRangeG - with similar cell range. ********* 2. I followed your instructions but infinite loop issue comes the moment a copy-paste action is triggered. Yes, I have used the WSC_Recursive code exactly as in the ex. ********* 3. How can I share the actual xls to you ? So you can test the code on it.
    – rabiswas
    Jul 3, 2021 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.