Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
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 -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
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) On Sat, 12 Sep 2009 11:35:01 -0700, Faraz A. Qureshi wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
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" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
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? -- Best Regards, Faraz "Chip Pearson" wrote: 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) On Sat, 12 Sep 2009 11:35:01 -0700, Faraz A. Qureshi wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
Excellent & quite detailed explanation Per!
Thanx! However, the message boxes do appear after your recommended macro. Thanx again man! -- Best Regards, Faraz "Per Jessen" wrote: 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" skrev i meddelelsen ... 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 -- Best Regards, Faraz |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBoxes & Ranges
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) On Sat, 12 Sep 2009 14:07:01 -0700, Faraz A. Qureshi wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranges & Inputboxes? | Excel Programming | |||
userform and inputboxes | Excel Programming | |||
connecting 2 inputboxes and using the autofill | Excel Programming | |||
InputBoxes | Excel Programming | |||
InputBoxes Exceptions | Excel Programming |