Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I am trying to apply a validation list (dropdown) to a cell using this following code (snippit of larger procedure)
The first part is the creation of a named range based on a dynamic range of cells. The named range is "nr_dsr_sig"
The second part places a predetermined default value of bn into a cell in column J of worksheet ws_master, as a row defined by srow. It also attempts to add list validation (dropdown) to that same cell based comprised of the values in nr_dsr_sig.
It runs through the code without error, however, I am not getting the drop validation list. The worksheet is protected, but the cell is unlocked.
What is preventing me from getting the dropdown validation for that cell?
VBA Code:
'part 1
With ws_thold
.Range("A2:A" & flcnt).Value = Range("I2:I" & flcnt).Value
.Range("I" & flcnt + 1).Value = "NA"
.Range("I" & flcnt + 2).Value = "NR"
Set rng_dsr_sig = .Range("I2:I" & flcnt + 2)
ThisWorkbook.Names.Add Name:="nr_dsr_sig", RefersTo:=rng_dsr_sig
End With
'part 2
With ws_master.Cells(srow, 10)
.Value = bn
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=nr_dsr_sig
End With
The first part is the creation of a named range based on a dynamic range of cells. The named range is "nr_dsr_sig"
The second part places a predetermined default value of bn into a cell in column J of worksheet ws_master, as a row defined by srow. It also attempts to add list validation (dropdown) to that same cell based comprised of the values in nr_dsr_sig.
It runs through the code without error, however, I am not getting the drop validation list. The worksheet is protected, but the cell is unlocked.
What is preventing me from getting the dropdown validation for that cell?