[RESOLVED] [Excel 2003] Validation - Input Message

[Torc]

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.

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.

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.

[westconn1]

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

[Torc]

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

If TheRange.Validation.InputMessage = "" Then

but received the same error message.

[anhn]

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

This is my version:

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

[Torc]

Many thanks anhn,

Just what was needed.