Ranges & Inputboxes?

[Faraz A. Qureshi]

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, SECOND, 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

 

[Rick Rothstein]

Dim FIRST, SECOND, THIRD As Range

The above statement is not doing what you think it is doing... only THIRD is
declared as a Range, FIRST and SECOND are declared as Variant. In VB, you
must declare each variable's Type individually. If you wanted to do it all
on one line, you would have to do it like this...

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

If FIRST.Address = "" Then
Exit Sub
MsgBox "--:" & FIRST.Address & ":--"
End If

Now, as for the above lines of code, the MsgBox statement will never be
executed because you exited the Sub in the previous line. Reverse those two
lines and the MsgBox will appear and then, when it is dismissed, the Sub
will be exited.

 

[Faraz A. Qureshi]

Thanx 4 the first line Rick!

But the object of msgbox is to appear only and only if any range is
selected, otherwise as stated b4 the code should terminate if inputbox is
cancelled or left blank.

--
Best Regards,

Faraz

Dim FIRST, SECOND, THIRD As Range

The above statement is not doing what you think it is doing... only THIRD is
declared as a Range, FIRST and SECOND are declared as Variant. In VB, you
must declare each variable's Type individually. If you wanted to do it all
on one line, you would have to do it like this...

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

If FIRST.Address = "" Then
Exit Sub
MsgBox "--:" & FIRST.Address & ":--"
End If

Now, as for the above lines of code, the MsgBox statement will never be
executed because you exited the Sub in the previous line. Reverse those two
lines and the MsgBox will appear and then, when it is dismissed, the Sub
will be exited.

 

[Rick Rothstein]

In that case, it looks like you left out the Else statement. Try this (and
modify the other parts of your code accordingly)...

If FIRST.Address = "" Then
Exit Sub
Else
MsgBox "--:" & FIRST.Address & ":--"
End If

--
Rick (MVP - Excel)

Thanx 4 the first line Rick!

But the object of msgbox is to appear only and only if any range is
selected, otherwise as stated b4 the code should terminate if inputbox is
cancelled or left blank.

 

[Faraz A. Qureshi]

Thanx again Rick but:

1. If the inputbox is left BLANK or CANCELLED I want the code to END! &

2. Why would an else statement be required? if the condition is not met the
CODE should progress onwards. Seems like I am wrong at something at:

FIRST.Address = ""

It should be something else instead of "".