Application Defined or Object Defined Error in Excel with VBA

VBE points to this bit of code which is causing the error:

Sheets("Topology").Range(Me.Get_Cell_Location).Interior.Color = RGB(255, 255, 255)

The whole function is defined as follows:

Public Function Colour_Me(choice As Integer) As Boolean

If choice = 1 Then

    Debug.Print "Choice 1"

     If Me.Get_Enabled1 = True Or Me.Get_Enabled2 = True Or Me.Get_Enabled3 = True Then
        Sheets("Topology").Range(Me.Get_Cell_Location).Interior.Color = RGB(255, 255, 0)
        Colour_Me = True
     Else
        Sheets("Topology").Range(Me.Get_Cell_Location).Interior.Color = RGB(255, 255, 255)
        Colour_Me = False
     End If

ElseIf choice = 2 Then

      Debug.Print "Choice 2"

     If Me.Get_Enabled1 = True Or Me.Get_Enabled2 = True Or Me.Get_Enabled3 = True Then
        Sheets("Topology").Range(Me.Get_Cell_Location).Interior.Color = RGB(0, 0, 255)
        Colour_Me = True
     Else
        Sheets("Topology").Range(Me.Get_Cell_Location).Interior.Color = RGB(255, 255, 255)
        Colour_Me = False
     End If
End If

End Function

The code in choice 1 seems to work alright, but choice 2 is giving me problems.

UPDATE

Public Property Let Set_Cell_Location(location As String)
    cell_location = location
End Property

Public Property Get Get_Cell_Location()
    Get_Cell_Location = cell_location
End Property

Answers


I believe you are getting that error because Excel is not able to determine the range. I have introduced error handling and added a MSGBOX. See what value does it give you?

Try this

Public Function Colour_Me(choice As Integer) As Boolean
    Dim Rng As Range

    On Error GoTo Whoa

    Set Rng = Sheets("Topology").Range(Me.Get_Cell_Location)

    If choice = 1 Then
        Debug.Print "Choice 1"
        If Me.Get_Enabled1 = True Or Me.Get_Enabled2 = True Or _
        Me.Get_Enabled3 = True Then
           Rng.Interior.Color = RGB(255, 255, 0)
           Colour_Me = True
        Else
           Rng.Interior.Color = RGB(255, 255, 255)
           Colour_Me = False
        End If
    ElseIf choice = 2 Then
        Debug.Print "Choice 2"
        If Me.Get_Enabled1 = True Or Me.Get_Enabled2 = True Or _
        Me.Get_Enabled3 = True Then
           Rng.Interior.Color = RGB(0, 0, 255)
           Colour_Me = True
        Else
           Rng.Interior.Color = RGB(255, 255, 255)
           Colour_Me = False
        End If
    End If
    Exit Function
Whoa:
    '~~> I have just put this here for testing
    Msgbox Me.Get_Cell_Location
End Function

Need Your Help

Preload progressDialog makes my app crash only in ICS

android crash android-4.0-ice-cream-sandwich preload

So I'm using this code to show the message "Installing..." while the database is setup with the function 'setUpDB' and is then removed when the database function has completed. This works fine in