Error Handling on my program

B

Brett Smith

I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and
the person hits cancel on the dialog box, I get a run-time error '424'. I am
not sure how to handle this, and I would like to have a dialog box pulled up
asking are you sure? Below is the code below....


After the code below, I have a series of select statements that follow in my
program...

Sub GetRows()
Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH: CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

'''After this line, I would like to have an error handler for the Cancel
button, but it fails miserably no matter what I do. I'm not sure how to
handle this one.
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)

'''I would like to have an error handler for this line too
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value
= " "
 
B

Bernie Deitrick

Brett,

Here's the general idea:

Dim myReturn As Range
On Error Resume Next

Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel!"
Exit Sub
Else
MsgBox myReturn.Address
End If


Of course, instead of exiting the sub, you could go back and ask for the cell again:

Dim myReturn As Range
On Error Resume Next

GetCell:
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You can't press Cancel!"
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP
 
B

Brett Smith

Thanks Bernie,

Is there a way that I can control this so that if the person presses the
cancel button twice it exits the sub? Like if they hit cancel once, it asks
are you sure, and if they hit it twice it exits completely? Thanks!

Brett

Bernie Deitrick said:
Brett,

Here's the general idea:

Dim myReturn As Range
On Error Resume Next

Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel!"
Exit Sub
Else
MsgBox myReturn.Address
End If


Of course, instead of exiting the sub, you could go back and ask for the cell again:

Dim myReturn As Range
On Error Resume Next

GetCell:
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You can't press Cancel!"
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


Brett Smith said:
I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and
the person hits cancel on the dialog box, I get a run-time error '424'. I am
not sure how to handle this, and I would like to have a dialog box pulled up
asking are you sure? Below is the code below....


After the code below, I have a series of select statements that follow in my
program...

Sub GetRows()
Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH: CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

'''After this line, I would like to have an error handler for the Cancel
button, but it fails miserably no matter what I do. I'm not sure how to
handle this one.
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)

'''I would like to have an error handler for this line too
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value
= " "
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Brett,

Dim myReturn As Range
Dim Tried As Boolean

Tried = False

GetCell:
On Error Resume Next
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel" & IIf(Tried, " AGAIN! Good-bye!", "!")
If Tried Then Exit Sub
Tried = True
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


Brett Smith said:
Thanks Bernie,

Is there a way that I can control this so that if the person presses the
cancel button twice it exits the sub? Like if they hit cancel once, it asks
are you sure, and if they hit it twice it exits completely? Thanks!

Brett

Bernie Deitrick said:
Brett,

Here's the general idea:

Dim myReturn As Range
On Error Resume Next

Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel!"
Exit Sub
Else
MsgBox myReturn.Address
End If


Of course, instead of exiting the sub, you could go back and ask for the cell again:

Dim myReturn As Range
On Error Resume Next

GetCell:
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You can't press Cancel!"
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


Brett Smith said:
I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and
the person hits cancel on the dialog box, I get a run-time error '424'. I am
not sure how to handle this, and I would like to have a dialog box pulled up
asking are you sure? Below is the code below....


After the code below, I have a series of select statements that follow in my
program...

Sub GetRows()
Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH: CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

'''After this line, I would like to have an error handler for the Cancel
button, but it fails miserably no matter what I do. I'm not sure how to
handle this one.
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)

'''I would like to have an error handler for this line too
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value
= " "
Click to expand...
Click to expand...
Click to expand...
 
B

Brett Smith

I tried it and I still get the same error which is Runtime Error '424', the
most unsolvable and annoying error I ever encountered. Is there a reason
why? Thanks!

Brett

Brett Smith said:
Thanks Bernie,

Is there a way that I can control this so that if the person presses the
cancel button twice it exits the sub? Like if they hit cancel once, it asks
are you sure, and if they hit it twice it exits completely? Thanks!

Brett

Bernie Deitrick said:
Brett,

Here's the general idea:

Dim myReturn As Range
On Error Resume Next

Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel!"
Exit Sub
Else
MsgBox myReturn.Address
End If


Of course, instead of exiting the sub, you could go back and ask for the cell again:

Dim myReturn As Range
On Error Resume Next

GetCell:
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You can't press Cancel!"
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


Brett Smith said:
I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and
the person hits cancel on the dialog box, I get a run-time error '424'. I am
not sure how to handle this, and I would like to have a dialog box pulled up
asking are you sure? Below is the code below....


After the code below, I have a series of select statements that follow in my
program...

Sub GetRows()
Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH: CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

'''After this line, I would like to have an error handler for the Cancel
button, but it fails miserably no matter what I do. I'm not sure how to
handle this one.
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)

'''I would like to have an error handler for this line too
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value
= " "
Click to expand...
Click to expand...
Click to expand...
 
B

Brett Smith

Thanks Bernie,
the reason why it didn't work the first time is because I did not declare
the On Error Resume Next expression up top and that is why I kept getting
Runtime Error '424'...EHRHH, unlucky number. But now everything works
perfectly. Thanks a billion!!

Brett

Bernie Deitrick said:
Brett,

Dim myReturn As Range
Dim Tried As Boolean

Tried = False

GetCell:
On Error Resume Next
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel" & IIf(Tried, " AGAIN! Good-bye!", "!")
If Tried Then Exit Sub
Tried = True
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


Brett Smith said:
Thanks Bernie,

Is there a way that I can control this so that if the person presses the
cancel button twice it exits the sub? Like if they hit cancel once, it asks
are you sure, and if they hit it twice it exits completely? Thanks!

Brett

Bernie Deitrick said:
Brett,

Here's the general idea:

Dim myReturn As Range
On Error Resume Next

Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You pressed Cancel!"
Exit Sub
Else
MsgBox myReturn.Address
End If


Of course, instead of exiting the sub, you could go back and ask for the cell again:

Dim myReturn As Range
On Error Resume Next

GetCell:
Set myReturn = Application.InputBox( _
"Enter bottom right cell - ONE cell only ", Type:=8)
On Error GoTo 0
If myReturn Is Nothing Then
MsgBox "You can't press Cancel!"
GoTo GetCell
Else
MsgBox myReturn.Address
End If

HTH,
Bernie
MS Excel MVP


I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and
the person hits cancel on the dialog box, I get a run-time error '424'. I am
not sure how to handle this, and I would like to have a dialog box pulled up
asking are you sure? Below is the code below....


After the code below, I have a series of select statements that follow in my
program...

Sub GetRows()
Dim FirstCell As Range, LastCell As Range
Dim Firstrow As Long, Lastrow As Long
Dim Wordstring As String
Dim filePath As String
Dim I As Integer
Dim FName As Variant



Wordstring = "$RDFILE 1" & vbCrLf & _
"$DATM " & Date & " " & Time & vbCrLf & _
"$RIREG 1" & vbCrLf & _
"$DTYPE BATCH: CHEMIST" & vbCrLf & _
"$DATUM REIDHAAJ" & vbCrLf & _
"$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _
"$DATUM [NUCLEIC ACID]" & vbCrLf & _
"$DTYPE STRUCTURE" & vbCrLf & _
"$DATUM $MFMT"

filePath = ActiveWorkbook.Path & "\Seqfile.rdf"
Open filePath For Output As #1
Print #1, Wordstring
Do
Set FirstCell = Application.InputBox("Enter top left cell - ONE cell
only ", Type:=8)

'''After this line, I would like to have an error handler for the Cancel
button, but it fails miserably no matter what I do. I'm not sure how to
handle this one.
Loop Until FirstCell.Count = 1
Firstrow = FirstCell.Row

Do
Set LastCell = Application.InputBox("Enter bottom right cell - ONE
cellonly ", Type:=8)

'''I would like to have an error handler for this line too
Loop Until LastCell.Count = 1
Lastrow = LastCell.Row

Firstrow = Int(Firstrow)
Lastrow = Int(Lastrow)


MsgBox Firstrow & " " & Lastrow

Range(Firstrow & ":" & Lastrow).Select

For I = Firstrow To Lastrow
If IsEmpty(Cells(I, "C").Value) Then

Dim G As Variant
Dim H As Variant

Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value
= " "
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can somebody help me with this code? 4
Why is my code not working the way I intend it to? 3
Vlookup error in VBA, 2042 4
Insert Row based on count and copy range to inserted rows 2
Finding last constant in range 1
Change column values depending upon the selection 3
reference error 3
Paste area Error on my macro 3

Top