CODEWITHSUNDEEP
vbaexcel
user1825257
user1825257

Reputation: 143

Excel VBA - Referencing Range using Cells

I am trying to dynamically add validation (using DataValidation:List) to ranges in a worksheet. I recorded a macro that produced the following code:

With Worksheets("Clusters").Range("C2:C100").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Managers"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With


This was working fine for the static range C2:C100, but the column may not be always be C. I have a variable cMANFCM that contains the column number. I tried to edit the code to use this:

With Worksheets("Clusters").Range(Cells(2,cMANFCM), Cells(100, cMANFCM)).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Managers"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With


Why doesn't this work and how do I fix it?

Upvotes: 3

Views: 7089

Answers (1)

whytheq
whytheq

Reputation: 35557

Your code works on mine - I've added a line to delete all existing validation and it does create new validation without throwing an error:

Lots of the validation arguments can probably be ignored...and you've got a choice of how to reference the sheet/range when other sheets are active:

Option Explicit
Sub control()
    'Call changeValidation(4)
    'Call changeValidationPAlbert(5)
    Call changeValidationTWilliams(6)
End Sub

Sub changeValidation(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Range(.Cells(2, cMANFCM), .Cells(100, cMANFCM)).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub
Sub changeValidationAlbert(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Range("A2:A100").Offset(, cMANFCM - 1).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub
Sub changeValidationTWilliams(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Cells(2, cMANFCM).Resize(100, 1).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub

Upvotes: 1

Related Questions