Validation (List) Failing To Applied To Cell

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am trying to apply a validation list (dropdown) to a cell using this following code (snippit of larger procedure)

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?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
OK. Thanks Rory! I appreciate your help and patience.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,704
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,732
Messages
5,638,042
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top