The following code will insert a new sheet, and list the data validation
messages the

'================================
Sub GetDVNotes()
Dim rngDV As Range
Dim wsNew As Worksheet
Dim ws As Worksheet
Dim lRow As Long
Dim cDV As Range
Set wsNew = Worksheets.Add
wsNew.Name = "Data Val Notes"
Application.EnableEvents = False
With wsNew
.Cells(1, 1).Value = "Sheet"
.Cells(1, 2).Value = "Cell"
.Cells(1, 3).Value = "Input Title"
.Cells(1, 4).Value = "Input Msg"
.Cells(1, 5).Value = "Error Title"
.Cells(1, 6).Value = "Error Msg"
End With
lRow = 2
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then
'do nothing
Else
For Each cDV In rngDV.Cells
With wsNew
.Cells(lRow, 1).Value = ws.Name
.Cells(lRow, 2).Value = cDV.Address
.Cells(lRow, 3).Value = cDV.Validation.InputTitle
.Cells(lRow, 4).Value = cDV.Validation.InputMessage
.Cells(lRow, 5).Value = cDV.Validation.ErrorTitle
.Cells(lRow, 6).Value = cDV.Validation.ErrorMessage
End With
lRow = lRow + 1
Next cDV
End If
Next ws
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub
'======================================

Dodson Brown wrote:
I have an Excel spreadsheet with approximately 40 different data validation
input messages and titles.

The spreadsheet isn't mine, but I need to get the input messages and titles
out of it to put into another spreadsheet. I know that I can cut and paste
each one, but this is painful, given that it is going to change, possibly
often.

Can someone point at how to automate extracting the validation input
messages and titles.

Thanks.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html