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.