using inputbox

[lior03]

hello
i am trying to use an inputbox to enable the user specify a range of cells,and carry an action he wants of this cells.

On Error Resume Next
Dim userrange As Range
Dim ans As String
ans = MsgBox(" select a range", vbYesNo)
Select Case ans
Case vbNo
Exit Sub
Case vbYes
userrange = Application.InputBox(prompt:="specify a range", Type:=8)
Selection.FormatConditions.Delete
End Select

that is wrong with my code?
thanks

[RichardSchollar]

hello
i am trying to use an inputbox to enable the user specify a range of cells,and carry an action he wants of this cells.

On Error Resume Next
Dim userrange As Range
Dim ans As String
ans = MsgBox(" select a range", vbYesNo)
Select Case ans
Case vbNo
Exit Sub
Case vbYes
userrange = Application.InputBox(prompt:="specify a range", Type:=8)
Selection.FormatConditions.Delete
End Select

that is wrong with my code?
thanks



You need to use the Set statement:

Set userrange = Application.InputBox(prompt:="specify a range", Type:=8)

and then

userrange.FormatConditions.Delete

Best regards

Richard

[lucas]

Here's a start for you Moshe. Will need error trapping for when user clicks cancel:

Dim rng As Range
Set rng = Application.InputBox("Select the target range with the mouse", Type:=8)

[Ken Puls]

A version with the error handling
On Error Resume Next
Set rng = Application.InputBox("Select the target range with the mouse", Type:=8)
If rng Is Nothing Then Exit Sub
On Error GoTo 0

:)

[lior03]

hello
i am pushing it a little further.i want the user to specify a range , and sum a series of integers and present a result as a rounded figure.
something like:

On Error Resume Next
Dim userrange As Range
Dim ans As String
ans = MsgBox(" select a range", vbYesNo)
Select Case ans
Case vbNo
Exit Sub
Case vbYes
userrange = Application.InputBox(prompt:="specify a range", Type:=8)
selection.FormulaArray = "=SUM(ROUNDuserrange,-3))"
End Select


can i combine an inputbox with a formula?
thanks

[Bob Phillips]

On Error Resume Next
Dim userrange As Range
Dim ans As String
ans = MsgBox(" select a range", vbYesNo)
Select Case ans
Case vbNo
Exit Sub
Case vbYes
Set userrange = Application.InputBox(prompt:="specify a range", Type:=8)
Selection.FormulaArray = "=SUM(ROUND(" & userrange.Address & ",-3))"
End Select