Excel VBA - Referencing Range using Cells

Question

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?

Try With Worksheets("Clusters").Range("A2:A100").Offset(, cMANFCM-1).Validation — Peter Albert
– Peter Albert, Commented Feb 27, 2013 at 22:26
Cells() without a qualifying worksheet will always refer to the ActiveSheet, so instead of using (e.g.) sht.Range(Cells(1,1), Cells(100,1)) you should use sht.Range(sht.Cells(1,1), sht.Cells(100,1)) — Tim Williams
– Tim Williams, Commented Feb 27, 2013 at 23:21
Thanks everyone. Tim was right, the problem is that I wasn't referring to the sheet. sht.cells(....) worked. — user1825257
– user1825257, Commented Feb 28, 2013 at 22:36

whytheq · Accepted Answer · 2013-02-28 07:55:39Z

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

this will error if "Clusters"is not the active sheet: see my comment above.
- correct Tim: I've now amended and tested and it seems to work when other sheets are activated. I didn't realize the OPer wanted this functionality (but probably why the got the error in the first place) ...although it's good practice and defensive coding to include it

Collectives™ on Stack Overflow

Excel VBA - Referencing Range using Cells

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related