PDA

View Full Version : using inputbox



lior03
12-29-2006, 09:59 AM
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
12-29-2006, 10:06 AM
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
12-29-2006, 10:10 AM
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
12-29-2006, 03:37 PM
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
01-24-2007, 05:18 AM
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

xld
01-24-2007, 05:30 AM
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