Is there any way to apply single validation setting to multiple cells with no validation containing different validation input text?

[Ironman]

Hi

I'm trying to protect around 150 cells in Col J Row 12 onwards that do not have a validation setting i.e. they are all 'Allow: any value'.

The problem is they all contain different validation input comments that I need to retain.

I can't simply highlight the column and edit the validation because I get the "Cells contain more than one kind of validation. Do you want to erase and continue?" warning, which I can't say yes to.

Is there a VBA solution to protect the cells in Col J Row 12 onwards that contain the input validation with, say, "allow a whole number equal to" 123456 and preserve the input messages?

Many thanks!

 

[Jeffrey Mahoney]

How about a different option. This macro selects all the cells with a specific Data Validation(DV). Then you can edit one DV at a time. You must select the range first.

Sub SelectDVWhole()

  Dim Cel As Range
  Dim u As Range
  Dim vt As Variant
   
  'Name                  Value  Description
  'xlValidateCustom      7      Data is validated using an arbitrary formula.
  'xlValidateDate        4      Date values.
  'xlValidateDecimal     2      Numeric values.
  'xlValidateInputOnly   0      Validate only when user changes the value.
  'xlValidateList        3      Value must be present in a specified list.
  'xlValidateTextLength  6      Length of text.
  'xlValidateTime        5      Time values.
  'xlValidateWholeNumber 1      Whole numeric values.

  For Each Cel In Selection
    On Error Resume Next
      vt = Cel.Validation.Type
    On Error GoTo 0
    If IsError(vt) Then
      vt = 0
    End If
    If vt = xlValidateWholeNumber Then
      If Not u Is Nothing Then
        Set u = Union(Cel, u)
      Else
        Set u = Cel
      End If
    End If
  Next Cel
  If Not u Is Nothing Then u.Select
 
End Sub

 

[Ironman]

Hi Jeffrey, many thanks for putting your time and thought into this!

It's actually not an issue to identify the cells because each cell contains an identifier - the character "¤" (it's concentric rings in Wingdings font). Basically, the validation input message is my alternative to Comments because I became fed up with the dimensions and location of the numerous comments boxes enlarging/reducing/drifting over time.

Having had time to think about this more, what would be a simpler solution - if possible - would be a vba warning message when I try to amend any filled cells from Column J12 down (I can't use the lock cells method because it interferes with other things going on in the sheet).

Thanks again!

 

[Jeffrey Mahoney]

You know that you can show a Validation warning without restricting any values

 

[Ironman]

Yes, thanks Jeff, I was aware of that - all the cells with input comments in that column allow any value, which is what I'm trying to change, so only the symbol I typed is accepted, else the error msg appears, but because the input messages are all different, Excel sees it as different validation, which in my mind it isn't, as there's really no validation.

 

[Jeffrey Mahoney]

I thought you were trying to edit all the DV cells that were similar so you wouldn't overwrite the different rules. Am I hearing now that you have different error messages for the same DV rule? The command below can retrieve the error message, maybe you can change the code I gave you to select all the DV rules with the same error message.

Other than that, I'm not sure how to help

Cel.Validation.ErrorMessage

 

[Ironman]

Thanks Jeffrey, sorry if I was unclear. None of the cells with input comments have any error messages, so any unwanted changes to the cell values aren't being validated, which is what I'm looking to do i.e. allow only the character I stated earlier, else there will be an error message, but it seems that the different input messages are preventing a bulk validation of all the cells together.