Problem with Cancel Button in Input Box

I have the following code. It works great so long as user inputs a number, but if they hit cancel theye get a type mismatch.

I think that Inputbox Cancel returns a 'zero length string' so it is choking sice I have the variable assigned to the input box dimmed as Double.


How to remedy?

[VBA]Sub RackSummary()

Dim T_critical As Double
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nSheet As Integer
Dim Summary As Worksheet
Dim ThisCell As String
Dim Space As Integer
Dim msg As String

nSheet = 9 '# of WorkSheets (Not including "Summary"). Summary must be last sheet
nRow = 250
Space = 5 'spaces out data neatly. You can adjust this and see what happens

T_critical = InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6")





Dim CaseArray() As Variant 'Holds 'titles' of each case. Must be in same order as each corresponding worksheet
'For EX: "Benchmark" is the 1st wrksht tab, "Cooler is the 2nd", and so on

CaseArray = Array("BenchMark", "Cooler 1 - 3", "Cooler 1 - 8", "Cooler 1 - 14", _
"Cooler 2 - 4", "Cooler 2 - 10", "Cooler 3 - 3", "Cooler 3 - 8", "Cooler 3 - 12")

Set Summary = Worksheets("Summary")


'Delete and clear any existing data/highlighting
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone



For k = 1 To nSheet
j = 6
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
Summary.Cells(j, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 1 + Space * (k - 1)).Value = "Rack"
Summary.Cells(j + 1, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 2 + Space * (k - 1)) = "Inlet T"
Summary.Cells(j + 1, 2 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 3 + Space * (k - 1)) = "Cold CI"
Summary.Cells(j + 1, 3 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 4 + Space * (k - 1)) = "Hot CI"
Summary.Cells(j + 1, 4 + Space * (k - 1)).Font.Bold = True


For i = 2 To nRow

ThisCell = LTrim(Worksheets(k).Cells(i, 1))

If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j + 2, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j + 2, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10) * (9 / 5) + 32
Summary.Cells(j + 2, 3 + Space * (k - 1)) = Worksheets(k).Cells(i + 2, 16)
Summary.Cells(j + 2, 4 + Space * (k - 1)) = Worksheets(k).Cells(i + 1, 17)
j = j + 1
End If

Next i
Next k

For k = 1 To nSheet
For i = 8 To nRow
For j = 1 To (nSheet * Space)
If Round(Summary.Cells(i, 2 + Space * (k - 1)), 1) >= T_critical Then
Summary.Cells(i, 2 + Space * (k - 1)).Interior.ColorIndex = 36
End If
Next j
Next i
Next k



msg = "All Inlet Temperatures That Exceed Desired Maximum Inlet Temperature Have Been Highlighted."
MsgBox msg


End Sub
[/VBA]

use the Application.InputBox instead, with its type set to 1:[vba]T_critical = Application.InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6", Type:=1)
[/vba]you can test for the cancel button with the likes of:[vba]If T_critical = "False" Then Stop[/vba]

Quote:

---

Originally Posted by p45cal

use the Application.InputBox instead, with its type set to 1:[vba]T_critical = Application.InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6", Type:=1)
[/vba]you can test for the cancel button with the likes of:[vba]If T_critical = "False" Then Stop[/vba]

---

I still get the same 'Type MisMatch' error message only now it points to this line:

[VBA]Sub RackSummary()

Dim T_critical As Double
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nSheet As Integer
Dim Summary As Worksheet
Dim ThisCell As String
Dim Space As Integer
Dim msg As String

nSheet = 9 '# of WorkSheets (Not including "Summary"). Summary must be last sheet
nRow = 250
Space = 5 'spaces out data neatly. You can adjust this and see what happens

T_critical = Application.InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6", Type:=1)

If T_critical = "False" Then Stop






Dim CaseArray() As Variant 'Holds 'titles' of each case. Must be in same order as each corresponding worksheet
'For EX: "Benchmark" is the 1st wrksht tab, "Cooler is the 2nd", and so on

CaseArray = Array("BenchMark", "Cooler 1 - 3", "Cooler 1 - 8", "Cooler 1 - 14", _
"Cooler 2 - 4", "Cooler 2 - 10", "Cooler 3 - 3", "Cooler 3 - 8", "Cooler 3 - 12")

Set Summary = Worksheets("Summary")


'Delete and clear any existing data/highlighting
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone




For k = 1 To nSheet
j = 6
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
Summary.Cells(j, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 1 + Space * (k - 1)).Value = "Rack"
Summary.Cells(j + 1, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 2 + Space * (k - 1)) = "Inlet T"
Summary.Cells(j + 1, 2 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 3 + Space * (k - 1)) = "Cold CI"
Summary.Cells(j + 1, 3 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 4 + Space * (k - 1)) = "Hot CI"
Summary.Cells(j + 1, 4 + Space * (k - 1)).Font.Bold = True


For i = 2 To nRow

ThisCell = LTrim(Worksheets(k).Cells(i, 1))

If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j + 2, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j + 2, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10) * (9 / 5) + 32
Summary.Cells(j + 2, 3 + Space * (k - 1)) = Worksheets(k).Cells(i + 2, 16)
Summary.Cells(j + 2, 4 + Space * (k - 1)) = Worksheets(k).Cells(i + 1, 17)
j = j + 1
End If

Next i
Next k

For k = 1 To nSheet
For i = 8 To nRow
For j = 1 To (nSheet * Space)
If Round(Summary.Cells(i, 2 + Space * (k - 1)), 1) >= T_critical Then
Summary.Cells(i, 2 + Space * (k - 1)).Interior.ColorIndex = 36
End If
Next j
Next i
Next k



msg = "All Inlet Temperatures That Exceed Desired Maximum Inlet Temperature Have Been Highlighted."
MsgBox msg


End Sub
[/VBA]

It works if I Dim T_critical as Variant instead of Double....but I really don't like using variant. But it will do for now. Thanks!

What is "Type 1"? Does anyone have a link handy to what the different types are?

You've got a handy link. It's called the vba help system. Search for 'inputbox method' ..it's all there.

InputBox returns a String type. You need to declare the variable As String, then it will stop causing the error.

[VBA]
Dim T_critical As String
Dim T_criticalNumber As Double
T_critical = InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6")

If Len(T_critical) > 0 Then ' something was entered, check type
If IsNumeric(T_critical) Then
T_criticalNumber = CDbl(T_critical)
End If
End If
[/VBA]