G
Guest
Hi, sheet1 col A is filled with values, I want col B to have a validation
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?
On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"
With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
On Error GoTo 0
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?
On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"
With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
On Error GoTo 0