Error Handling on my program

[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
= " "

[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


"Brett Smith" <BrettSmith@discussions.microsoft.com> wrote in message
news:C2C90C9B-F535-42C9-A78B-9A6C38C39769@microsoft.com...
>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
> = " "
>
>

[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" wrote:

> 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" <BrettSmith@discussions.microsoft.com> wrote in message
> news:C2C90C9B-F535-42C9-A78B-9A6C38C39769@microsoft.com...
> >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
> > = " "
> >
> >
>
>
>

[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" <BrettSmith@discussions.microsoft.com> wrote in message
news:4C8E74D5-95E9-49B8-AA5A-D6ECB89D2069@microsoft.com...
> 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" wrote:
>
>> 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" <BrettSmith@discussions.microsoft.com> wrote in message
>> news:C2C90C9B-F535-42C9-A78B-9A6C38C39769@microsoft.com...
>> >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
>> > = " "
>> >
>> >
>>
>>
>>

[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" wrote:

> 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" wrote:
>
> > 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" <BrettSmith@discussions.microsoft.com> wrote in message
> > news:C2C90C9B-F535-42C9-A78B-9A6C38C39769@microsoft.com...
> > >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
> > > = " "
> > >
> > >
> >
> >
> >

[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" wrote:

> 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" <BrettSmith@discussions.microsoft.com> wrote in message
> news:4C8E74D5-95E9-49B8-AA5A-D6ECB89D2069@microsoft.com...
> > 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" wrote:
> >
> >> 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" <BrettSmith@discussions.microsoft.com> wrote in message
> >> news:C2C90C9B-F535-42C9-A78B-9A6C38C39769@microsoft.com...
> >> >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
> >> > = " "
> >> >
> >> >
> >>
> >>
> >>
>
>
>