Ranges & Inputboxes?

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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
 
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
Click to expand...

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.
 
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


Rick Rothstein said:
Dim FIRST, SECOND, THIRD As Range
Click to expand...

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
Click to expand...

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.
Click to expand...
 
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)


Faraz A. Qureshi said:
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.
Click to expand...
 
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 "".
 
Back
Top