Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default Inputbox method using type:=8 - How to Cancel?

Thanks to everyone for helping me solve my problem.
Once I saw I could use "On Error" to overcome the error, I simplified on
Vasant's solution as follows:
+++++++++++++++++++++++++++++
Sub PickRow ( )
dim mynum
On Error Goto ErrorHandler
Set mynum= Application.InputBox ( "Select Row" , type: = 8 )
"some code"

ErrorHandler :
"some code" (or nothing if you just want to stop the procedure!)
End Sub
+++++++++++++++++++++++++++++
This is all I needed on this occasion.

Cheers

Joe

"Joe 90" wrote in message
...
Thanks Vasant

I'll give it a whirl today and report back

Joe

"Vasant Nanavati" wrote in message
...
Hi Joe:

You need something like the following awkward construction:

Sub ForceRangeInput()
Dim vResp As Variant
Do Until TypeName(vResp) = "Range"
On Error Resume Next
Set vResp = Application.InputBox("Test", Type:=8)
On Error GoTo 0
Loop
vResp.EntireRow.Select
End Sub

It's tricky only because the InputBox can return two types, an object

and
a
boolean.

Regards,

Vasant.

Regards,

Vasant.



"Joe 90" wrote in message
...
Hi,

I have an input box that asks the user to select a row (by picking a

cell).
The dialog box provides for an Ok button and a Cancel button.
If the user clicks the Cancel button I get an error in VBA.
I want to create a rounded app so everything works and
I have tried all ways to get this to go away but nothing I can do
will make the inputbox accept a "Range object" as the default, and

therefore
allow the Cancel. All I get is a "Run time error 13, type mismatch"

Here's the code:
+++++++++++++++++++++++++++++++++++++++++++
Sub PickRow ( )
dim mynum
set mynum = Application.InputBox ( "Select Row" , type: = 8 )
End Sub
+++++++++++++++++++++++++++++++++++++++++++
I have tried putting a variable for a range, a range address, and a

range
reference in as default e.g.

set mynum = Application.InputBox ( "Select Row" , , "$A$1" ,type: =

8 )

What am I doing wrong, or is this a vagary of the object model, that

because
I am choosing to seek a range object type, the Cancel button will

always
produce an error??

Hoping you can help..................................

Joe








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
inputbox type 64 (array) pete the greek Excel Discussion (Misc queries) 8 May 20th 23 11:44 AM
InputBox / VBA question ok = print, cancel = exit todd78 Excel Worksheet Functions 4 August 9th 07 04:42 PM
Detecting Cancel in an InputBox Method Connie Excel Discussion (Misc queries) 2 October 19th 06 01:32 PM
Inputbox method using type:=8 - How to Cancel? Harlan Grove[_5_] Excel Programming 1 July 9th 03 12:06 AM
Inputbox method using type:=8 - How to Cancel? Jim Cone Excel Programming 0 July 8th 03 06:15 PM


All times are GMT +1. The time now is 10:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"