Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges & Inputboxes?
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 -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges & Inputboxes?
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 (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges & Inputboxes?
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" wrote: 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 (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges & Inputboxes?
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" wrote in message ... 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" wrote: 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 (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges & Inputboxes?
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 "". -- Best Regards, Faraz "Rick Rothstein" wrote: 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" wrote in message ... 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" wrote: 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 (MVP - Excel) "Faraz A. Qureshi" wrote in message ... 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 -- Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform and inputboxes | Excel Programming | |||
Positioning Userforms, InputBoxes | Excel Discussion (Misc queries) | |||
connecting 2 inputboxes and using the autofill | Excel Programming | |||
InputBoxes | Excel Programming | |||
InputBoxes Exceptions | Excel Programming |