Obtain the Range Name used for Data Validation
 

I need to aquire the name of the range that was used in Data Validation
programatically at runtime.

Many Thanks
--
Robert Hill

Obtain the Range Name used for Data Validation
 

"Robert" wrote:

I need to aquire the name of the range that was used in Data Validation
programatically at runtime.

Many Thanks
--
Robert Hill

Robert

Insert this code in the workbook module (right-click the Excel icon on the
on the file menu) and choose View Code.

Sub workbook_open()
Dim i As Integer, x
On Error Resume Next
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Worksheets(i).Select
x = ActiveCell.SpecialCells(xlCellTypeAllValidation).S elect
If IsEmpty(x) Then
' Do nothing
Else
MsgBox Selection.Address & vbLf & ActiveSheet.Name
End If
Next i
Worksheets(1).Select
Application.ScreenUpdating = False
End Sub

Tweak to suit

regards
Peter

Obtain the Range Name used for Data Validation
 

Sub ABC()
Dim v As Validation
Dim rng As Range, s As String
Dim sName As String
Set v = Nothing
On Error Resume Next
Set v = ActiveCell.Validation
s = v.Formula1
On Error GoTo 0
If s < "" Then
If v.Type = xlValidateList Then
If InStr(1, v.Formula1, "=", vbTextCompare) Then
s = Replace(v.Formula1, "=", "")
Set rng = Application.Range(s)
On Error Resume Next
sName = rng.Name.Name
On Error GoTo 0
If sName = "" Then
MsgBox rng.Address
Else
MsgBox "Name range: " & sName
End If
Else
MsgBox "Hard coded list validation"
End If
Else
MsgBox "Validation is not of type list"
End If
Else
MsgBox "No validation"
End If
End Sub

Lightly tested.

--
Regards,
Tom Ogilvy

"Robert" wrote in message
...
I need to aquire the name of the range that was used in Data Validation
programatically at runtime.

Many Thanks
--
Robert Hill

Obtain the Range Name used for Data Validation
 

Thanks for the rating Robert

Peter

"Billy Liddel" wrote:



"Robert" wrote:

I need to aquire the name of the range that was used in Data Validation
programatically at runtime.

Many Thanks
--
Robert Hill

Robert

Insert this code in the workbook module (right-click the Excel icon on the
on the file menu) and choose View Code.

Sub workbook_open()
Dim i As Integer, x
On Error Resume Next
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Worksheets(i).Select
x = ActiveCell.SpecialCells(xlCellTypeAllValidation).S elect
If IsEmpty(x) Then
' Do nothing
Else
MsgBox Selection.Address & vbLf & ActiveSheet.Name
End If
Next i
Worksheets(1).Select
Application.ScreenUpdating = False
End Sub

Tweak to suit

regards
Peter

Obtain the Range Name used for Data Validation
 

Robert,

I am an inexperienced user like yourself, however i have the same problem as
you i found that DAO works better with this than MS Query and ODBC (look it
up on the MS knowledge base), it took me a little while to follow it, but it
works so much better, you can even pass parameters back to Access etc. I
haven't figured out the SQL it needs to query as well as MS Query, so
personally i just make the query how i want it in Access and then pull that
through using DAO.

HTH.
Emma





"Robert" wrote:

I need to aquire the name of the range that was used in Data Validation
programatically at runtime.

Many Thanks
--
Robert Hill