PDA

View Full Version : Solved: Inputbox - Cell/Range selection - Nothing selected or Cancel



Cosmos75
08-19-2004, 07:09 PM
I have the following code to ask the user to select a cell or range.
'Ask for selection
Set SelectedArea = Application.InputBox(prompt:="Select the cell/range...", _
Title:="SWITCH CELL SELECTION", _
Default:=Selection.Address, Type:=8)How do handle it when the user clicks the Cancel button or doesn't select anything?
:confused:

Also, is there any other way to tell if a single cell was selected or if a range was selected other than using
SelectedArea.Rows.Count
SelectedArea.Columns.CountThanks!

Jacob Hilderbrand
08-19-2004, 07:24 PM
Try this:


Option Explicit

Sub Test()

Dim Selectedarea As Range
Dim MyErr As Long

On Error Resume Next
Set Selectedarea = Application.InputBox(prompt:="Select the cell/range...", _
Title:="SWITCH CELL SELECTION", Default:=Selection.Address, Type:=8)
MyErr = Err
On Error GoTo 0
If MyErr <> 0 Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Sub
End If

MsgBox Selectedarea.Cells.Count 'Change to do what you want if Cells.Count = 1

End Sub

Cosmos75
08-19-2004, 07:38 PM
MyErr = Err
On Error GoTo 0
If MyErr <> 0 Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Sub
End IfThis never executes when I click cancel?

Maybe I am missing something obvious or am just plain vanilla dumb, but what does On Error GoTo 0 do?

By adding a Dim Selectedarea As Range I do however raise a error # 424 (Object Required), whereas before I got error # 13 (Type Mismatch). Perhaps trapping for that error will be enough for when Cancel is clicked.

But when I leave the inputbox empty or enter some string, I see message (see the attached picture). No idea what's going on there....

Daniel Klann
08-19-2004, 07:41 PM
Hi,

A small variation on DRJ's code:


Sub Test()
Dim Selectedarea As Range
On Error Resume Next
Set Selectedarea = Application.InputBox(prompt:="Select the cell/range...", _
Title:="SWITCH CELL SELECTION", Default:=Selection.Address, Type:=8)
If Selectedarea Is Nothing Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Sub
End If
MsgBox Selectedarea.Cells.Count 'Change to do what you want if Cells.Count = 1
End Sub


HTH
Dan

Jacob Hilderbrand
08-19-2004, 08:05 PM
I just double checked and the error does trigger when you press Cancel, so I am not sure what is happening for you.

When you Dim as Range make sure you use the Set statement to assign the Range

i.e.

Dim Rng1 as Range

Set Rng1 = ...

The picture you posted is the error that is built into the Application.Inputbox If you try to input invalid data you get the error. Since a blank "" is not a range you get the error when you leave this empty.

On Error Resume Next tells VBA to ignor errors
On Error Goto 0 tells VBA to reset what it does for errors and start handling them like it normally would.

Cosmos75
08-19-2004, 09:19 PM
On Error Resume Next tells VBA to ignor errors
On Error Goto 0 tells VBA to reset what it does for errors and start handling them like it normally would.Thanks! I did not know that you can use more than 1 "On Error" statements or even wondered what On Error Resume Next did!

Wonderful!

Zack Barresse
08-19-2004, 10:42 PM
Personally, I'd go with Dan's idea, the Is Nothing, that's what you should use. You don't need to use the expensive On Error Resume Next. You need to be extremely careful with that, it will be hard to tell what error you are getting (especially in debugging your code). It makes a good catchall but should be used carefully.

Jacob Hilderbrand
08-19-2004, 11:01 PM
Zack

Dan used On Error Resume Next as well, unless I am going blind.

Zack Barresse
08-19-2004, 11:56 PM
Doh! I knew that, argh, too tired, shouldn't have posted. I meant to say that I prefer using On Error GoTo myDest types of statements. The Resume Next is the part I'm leary of, but with only one or two things to error out on, it's not bad at all. :) (Sorry)