InputBoxes & Ranges

[Faraz A. Qureshi]

Dear Friends,

I posted this question last week. Although a friend tried to help he
couldn't do so. Sure would oblige if any1 else could help me outta this mess:

I always get into a trouble when referring to a range via an inputbox and a
Cancel button is clicked or it is left Blank. Any suggestions?

Furthermore, what might be wrong with the following code?

Sub CHECK()

Dim FIRST As Range, SECOND As Range, THIRD As Range

Set FIRST = Application.InputBox("FIRST!", , , , , , , 8)
If FIRST.Address = "" Then
Exit Sub
MsgBox "--:" & FIRST.Address & ":--"
End If

Set SECOND = Application.InputBox("SECOND!", , , , , , , 8)
If SECOND.Address = "" Then
Exit Sub
MsgBox "--:" & SECOND.Address & ":--"
End If

Set THIRD = Application.InputBox("THIRD", , , , , , , 8)
If THIRD.Address = "" Then
Exit Sub
MsgBox "--:" & THIRD.Address & ":--"
End If

End Sub

 

[Chip Pearson]

Try something like

Dim R As Range
On Error Resume Next
Set R = Application.InputBox(prompt:="Select", Type:=8)
If R Is Nothing Then
Debug.Print "Nothing selected"
Else
Debug.Print "Selected: " & R.Address
End If


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Per Jessen]

Hi

Two things:
You have to declare the variables as variant, to allow user to press
cancel. And you need an error handler as the Set statement require a Object
(could be a range, but not a blank).

Also you MsgBox statements will never show. Look at the code below:

Sub CHECK()

Dim FIRST As Variant, SECOND As Variant, THIRD As Variant
On Error Resume Next
Set FIRST = Application.InputBox("FIRST!", , , , , , , 8)
If FIRST.Address = "" Then
Exit Sub
End If
MsgBox "--:" & FIRST.Address & ":--"

Set SECOND = Application.InputBox("SECOND!", , , , , , , 8)
If SECOND.Address = "" Then
Exit Sub
End If
MsgBox "--:" & SECOND.Address & ":--"

Set THIRD = Application.InputBox("THIRD", , , , , , , 8)
If THIRD.Address = "" Then
Exit Sub
End If
MsgBox "--:" & THIRD.Address & ":--"
On Error GoTo 0 ' reset error handler
End Sub

Regards,
Per

 

[Faraz A. Qureshi]

Nice 2 hear from u after quite a longtime Chip!

However, the resulting messages even dont appear after debug statement.

Could u please redevise the macro I have presented?

 

[Faraz A. Qureshi]

Excellent & quite detailed explanation Per!
Thanx!
However, the message boxes do appear after your recommended macro.
Thanx again man!

 

[Chip Pearson]

Try something like

Sub CHECK()
Dim FIRST As Range, SECOND As Range, THIRD As Range
On Error Resume Next
Set FIRST = Application.InputBox(prompt:="First", Type:=8)
If FIRST Is Nothing Then
MsgBox "First was not set."
Else
MsgBox "First: " & FIRST.Address
End If
Set SECOND = Application.InputBox(prompt:="Second", Type:=8)
If SECOND Is Nothing Then
MsgBox "Second was not set."
Else
MsgBox "Second: " & SECOND.Address
End If
Set THIRD = Application.InputBox(prompt:="Third", Type:=8)
If THIRD Is Nothing Then
MsgBox "Third was not set."
Else
MsgBox "Third: " & THIRD.Address
End If
End Sub


You'll find that code is easier to read and write if you use named
arguments rather than positional arguments. E.g., instead of

Set FIRST = Application.InputBox("First", , , , , , , 8)

use

Set FIRST = Application.InputBox(prompt:="First", Type:=8)

With positional notation, it is too easy to get the wrong number of
commas, even with intellisense support.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)