Finding out the named range used by a cell

[Steve Adams]

I know this seems to be straight forward but I cant figure this out.

Scenario : I have a list of cells each with a dropdown box which gets its values from a specific named range. I have a second worksheet containing all the values but nothing tying them together to the first worksheet except the named range (which is all over the place). What I need to do is find out the list of values that are in each dropdown so that I can transfer this to an Access DB.

There are two parts of the same problem:

First I need to loop through each of the cells in sheet one and get the name of the named ranged that the dropdown box is referring to.

Secondly I need to loop through each named range in worksheet two to get the values.

I have managed to find a couple of samples for the second part of the question but nothing about the first. Can anybody please help.

Many thanks
Steve

 

[Steve Adams]

Sorry - I forgot to mention that the worksheet is in 2007 format.

 

[Alex Blakenburg]

First I need to loop through each of the cells in sheet one and get the name of the named ranged that the dropdown box is referring to.

Secondly I need to loop through each named range in worksheet two to get the values.

I have managed to find a couple of samples for the second part of the question but nothing about the first. Can anybody please help.

Do you just need the basic syntax "for the first" or do you need more than that ?

This will print the validation named range on the ActiveSheet to the immediate window.

Sub ValidationRangeNames()

    Dim sh As Worksheet
    Dim rngValid As Range
    Dim vCell As Range
 
    Set sh = ActiveSheet
 
    Set rngValid = sh.Cells.SpecialCells(xlCellTypeAllValidation)
 
    For Each vCell In rngValid
       Debug.Print vCell.Address & vbTab & rngValid.Validation.Formula1
    Next vCell

End Sub

 

[Steve Adams]

Hi Alex

Thank you VERY much for comming back to me.

This code looks 100% correct to me and yet I get an "Object Defined Error" message

Sub ValidationRangeNames()
    Dim sh As Worksheet
    Dim rngValid As Range
    Dim vCell As Range
    Dim i As Integer
 
    Set sh = ActiveSheet

    Set rngValid = sh.Cells.SpecialCells(xlCellTypeAllValidation) ''I did try to set this only to values in column E but I couldn't get this to work
    
For i = 2 To 5 ''This put in just for testing purposes so that I dont go through all lines.
    For Each vCell In rngValid
      Debug.Print vCell.Address & vbTab & rngValid.Validation.Formula1
      sh.Cells(i, 7) = vCell.Address & vbTab & rngValid.Validation.Formula1  ''Tried storing the returned value in column G but dont know if it works because of the error
    Next vCell
Next
End Sub

Any thoughts?
Steve

 

[Alex Blakenburg]

Your “For I = 2 to 5” loop doesn’t make sense. Instead of limiting it you are running the code 4 times instead of just once.
I am in Australia and logged out for the night.

Try this.
Get rid of the additional For loop and just use this

i  = 1
For Each vCell In rngValid
      i =i + 1
      sh.Cells(i, 7) = vCell.Address & “ “ & rngValid.Validation.Formula1
Next vCell

 

[Steve Adams]

Hi Alex

I hope your awake. I'm still getting the same error (see image)

The range is correct because if I only look at the vcell.address then this is correct but when I hover over the rngvalid.validation.formula1 I get the object defined error.

I have tried running in 2007 as well as 2019 as well as on two different pc's.

Any more ideas or an alternative way of getting this?

Steve

 

[Alex Blakenburg]

Sorry, I thought I remembered changing that.
The 2nd part should also refer to vCell not rngValid

i  = 1
For Each vCell In rngValid
      i =i + 1
      sh.Cells(i, 7) = vCell.Address & “ “ & vCell.Validation.Formula1
Next vCell

 

[Steve Adams]

You are an absolute star. Thank you very much

 

[Alex Blakenburg]

Glad I could help. Thanks for the feedback.