I am trying to set an object variable. I get an error message when I
try to do the following:

Dim NumberofColumns As Variant
Dim BMax As Integer

Set NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If NumberofColumns Is Nothing Then
MsgBox ("Operation Canceled")
Exit Sub
Else
BMax = NumberofColumns
End If

When I enter data or click on CANCEL the error says Type Mismatch. Is
this because Variant and Type:= 1 are not compatible?


---
Message posted from http://www.ExcelForum.com/

Get rid of the 'Set' keyword in the line of code. InputBox
returns an object type variable only when Type is set to 8.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ExcelMonkey " wrote
in message ...
I am trying to set an object variable. I get an error message
when I
try to do the following:

Dim NumberofColumns As Variant
Dim BMax As Integer

Set NumberofColumns = Application.InputBox(prompt:="Enter
Number of 2D
Elements", Type:=1)

If NumberofColumns Is Nothing Then
MsgBox ("Operation Canceled")
Exit Sub
Else
BMax = NumberofColumns
End If

When I enter data or click on CANCEL the error says Type
Mismatch. Is
this because Variant and Type:= 1 are not compatible?


---
Message posted from http://www.ExcelForum.com/

ExcelMonkey,

Try something like:

Sub test()
Dim NumberofColumns As Variant
Dim BMax As Integer

NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If TypeName(NumberofColumns) = "Boolean" Then
If NumberofColumns = False Then
MsgBox ("Operation Canceled")
Exit Sub
End If
End If
BMax = NumberofColumns
End Sub


I use TypeName statement so that you can use 0 as a number. False = 0

Rob


"ExcelMonkey " wrote in message
...
I am trying to set an object variable. I get an error message when I
try to do the following:

Dim NumberofColumns As Variant
Dim BMax As Integer

Set NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If NumberofColumns Is Nothing Then
MsgBox ("Operation Canceled")
Exit Sub
Else
BMax = NumberofColumns
End If

When I enter data or click on CANCEL the error says Type Mismatch. Is
this because Variant and Type:= 1 are not compatible?


---
Message posted from http://www.ExcelForum.com/

You guys rock!

Thank

--
Message posted from http://www.ExcelForum.com

A number is not an object. So remove the "set". Then check for
"numberOfColumns = false not nothing

"ExcelMonkey " wrote in message
...
I am trying to set an object variable. I get an error message when I
try to do the following:

Dim NumberofColumns As Variant
Dim BMax As Integer

Set NumberofColumns = Application.InputBox(prompt:="Enter Number of 2D
Elements", Type:=1)

If NumberofColumns Is Nothing Then
MsgBox ("Operation Canceled")
Exit Sub
Else
BMax = NumberofColumns
End If

When I enter data or click on CANCEL the error says Type Mismatch. Is
this because Variant and Type:= 1 are not compatible?


---
Message posted from http://www.ExcelForum.com/