Object required error

  • I have the following code to clear the contents of a cell based on user input. I want to exit the sub if the Cancel button is clicked but I get an Object Required error. Any help would be terrific. Thanks.


    [VB]
    Dim rInput As Range
    Dim sAddress As String
    Set rInput = Application.InputBox _
    (Prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2:", _
    Title:="Delete Points", Type:=8)

    If rInput = "" Then
    MsgBox ("You have not entered a Cell Address. Please try again.")
    Exit Sub
    End If

    sAddress = rInput.Address
    Range(sAddress).ClearContents
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object required error


    rInput should be a string not a range. Then the last line would be

    Code
    1. Range(rInput).ClearContents

    and you can skip the sAddress line

  • Re: Object required error


    Thank you so much for your quick response. I changed rInput to a string and modified the last line but now I get an Object Required error on the Set rInput line.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object required error


    Yea I forgot about that one (sorry about that :) )....remove the Set. So it would just be:


    Code
    1. rInput = Application.InputBox _
    2. (Prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2:", _
    3. Title:="Delete Points", Type:=8)


    The Set keyword only applies to object variables (ranges, worksheets, etc.)...and a string is not.

  • Re: Object required error


    Thank you again. I did remove "set" but now I get a 'Range of Object Global Failed" error. I tried putting an "On Error Resume Next" statement in my original code as below and now it seems to work properly.


    [VB]
    Dim rInput As Range
    Dim sAddress As String
    On Error Resume Next
    Set rInput = Application.InputBox _
    (prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2.", _
    Title:="Delete Points", Type:=8)

    If rInput = "" Then
    MsgBox ("You have not entered a Cell Address. Please try again.")
    Exit Sub
    End If

    sAddress = rInput.Address
    Range(sAddress).ClearContents
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object required error


    An Application.InputBox, with type:= 8 returns a Range object if somehting is selected or the Boolean False if Canceled. This causes problems because you don't know whether to use the keyword Set until after the user presses a button.

  • Re: Object required error


    Your advice is very much appreciated. I have learned a lot and continue to do so.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.