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 "Like" 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
    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 "Like" 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
    rInput = Application.InputBox _ 
    (Prompt:="Enter the Cell Address of the points you wish to delete ... e.g. C2:", _ 
    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 "Like" 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 "Like" 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!