[RESOLVED] [Excel 2003] Validation - Input Message

I have a spreadsheet which is creating a template spreadsheet for users to
fill out. On some cells I have data validation to restrict the values to a
specific list of values.

For other cells I have no data validation, but I am trying to set these celsl up
so that the InputMessage comes up when selected in the same way when I
have validation set on a cell.

I am using the following code.

Code:

---

Public Sub InputMessage(ByRef TheRange As Range, ByVal TextMessage As String, _
                      Optional ByVal TitleMessage As String = "")
' Sets the InputMessage for a Cell or a Range of Cells.
' note that if there is already a message then this will NOT be overwritten.
    If IsNull(TheRange.Validation.InputMessage) Then
        With TheRange.Validation
            .InputMessage = TextMessage
            .InputTitle = TitleMessage
            .ShowInput = True
        End With
    End If
End Sub

---

However it is falling over at the following line.

Code:

---

If IsNull(TheRange.Validation.InputMessage) Then

---

With this message:-

Run Time Error 1004
Application defined or Object defined error

I cannot help thinking I am missing something really obvious, but I cannot
work out what.

Any suggestions

Before anyone suggests it I have looked at Koolsid's Data Validation tutorial.

are you expecting null values in cells?
or should that be if isempty(..................

Hi westconn1.

What I am trying to do in my code is to check to see if there is already an
InputMessage on the validation for the Cell or range.

I do not want to enter something if there is already a message there.

I had tried

Code:

---

If TheRange.Validation.InputMessage = "" Then

---

but received the same error message.

If TheRange.Validation does not exist, you cannot access to TheRange.Validation.InputMessage.

This is my version:

Code:

---

Public Function SetInputMessage(aRange As Range, Message As String,
                                Optional Title As String) As Long
  '-- return 0: Validation Existed with InputMessage, not added
  '-- return 1: Validation Existed with No InputMessage, add new InputMessage
  '-- return 2: New added Validation with new InputMessage
  Dim sTest As String
 
  With aRange.Validation
      On Error Resume Next
      sTest = .InputMessage
      If Err <> 0 Then
        .Add xlValidateInputOnly
        SetInputMessage = 1
      End If
      On Error GoTo 0
      If sTest = "" Then
        SetInputMessage = SetInputMessage + 1
        If Title <> "" Then .InputTitle = Title
        .InputMessage = Message
        .ShowInput = True
      End If
  End With
End Function

---

Many thanks anhn,

Just what was needed.