Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formcode

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    NSW. Australia
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me why the following code will not work.
    Private Sub cmdNextPage_Click()
    Dim page As Integer
    page = activesheet.Select
    If page = 40 Then
    page = 1
    Else
    page = page + 1
    End If
    End Sub

    Thanks in advance.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 01:42, Phil C wrote:
    Can anyone tell me why the following code will not work.
    Private Sub cmdNextPage_Click()
    Dim page As Integer
    page = activesheet.Select
    If page = 40 Then
    page = 1
    Else
    page = page + 1
    End If
    End Sub

    Thanks in advance.
    Some things:

    page is an integer (a Number)
    page = ActiveSheet.Select

    causes (Or should) an error because you're trying to do something like

    Number = Worksheet

    I think you're trying to use the ActiveSheet.Index property.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    NSW. Australia
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jaun Pablo G.
    I changed the ActiveSheet.Select to the ActiveSheet.Index and the code works fine except the userform exits. How do I stop the form from unloading?

    Thanks

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't see anything that should cause that, what other code are you using in that form ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    NSW. Australia
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the full code for the form.

    Option Explicit
    Dim rownumber As Integer
    Private Sub cboNameAddress_Change()
    lblLicence2.Caption = Sheets("Client Details").Cells(cboNameAddress.ListIndex + 1, 2)
    txtImportExport.SetFocus
    End Sub

    Private Sub cmdNextPage_Click()
    On Error GoTo 1
    activesheet.Next.Select
    End
    1: Sheets(2).Select
    End Sub

    Private Sub cmdPreviousPage_Click()
    Dim page As Integer
    page = activesheet.Index
    If page = 1 Then
    page = 40
    Else
    page = page + 1
    End If
    End Sub

    Private Sub cmdAddRecord_Click()
    Range("L8:O8").Select
    Selection.Copy
    Cells(rownumber, 12).Select 'selects row and cell no 12
    activesheet.Paste
    Range("T8:Z8").Select
    Selection.Copy
    Cells(rownumber, 20).Select
    activesheet.Paste
    Cells(rownumber, 1).Value = txtDate
    Cells(rownumber, 1) = Format((txtDate.Text), "m/d/yyyy")
    Cells(rownumber, 1).HorizontalAlignment = xlCenter
    Cells(rownumber, 1).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 2).Value = txtMale
    Cells(rownumber, 2) = Format((txtMale.Text), "@")
    Cells(rownumber, 2).HorizontalAlignment = xlCenter
    Cells(rownumber, 2).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 3).Value = txtFemale
    Cells(rownumber, 3) = Format((txtFemale.Text), "@")
    Cells(rownumber, 3).HorizontalAlignment = xlCenter
    Cells(rownumber, 3).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 4).Value = txtUnknown
    Cells(rownumber, 4) = Format((txtUnknown.Text), "@")
    Cells(rownumber, 4).HorizontalAlignment = xlCenter
    Cells(rownumber, 4).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 5).Value = cboMethod
    Cells(rownumber, 5) = Format((cboMethod.Text), "@")
    Cells(rownumber, 5).HorizontalAlignment = xlCenter
    Cells(rownumber, 5).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 6).Value = cboNameAddress
    Cells(rownumber, 6) = Format((cboNameAddress.Text), "@")
    Cells(rownumber, 6).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 7).Value = lblLicence2
    Cells(rownumber, 7).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, .Value = txtImportExport
    Cells(rownumber, .Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 9).Value = txtSaleNumber
    Cells(rownumber, 9).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 10).Value = txtFaunaIn
    Cells(rownumber, 10) = Format((txtFaunaIn.Text), "@")
    Cells(rownumber, 10).HorizontalAlignment = xlCenter
    Cells(rownumber, 10).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 11).Value = txtFaunaOut
    Cells(rownumber, 11) = Format((txtFaunaOut.Text), "@")
    Cells(rownumber, 11).HorizontalAlignment = xlCenter
    Cells(rownumber, 11).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 12).HorizontalAlignment = xlCenter
    Cells(rownumber, 12).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 13).Select
    Cells(rownumber, 13).HorizontalAlignment = xlCenter
    Cells(rownumber, 13).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 14).Select
    Cells(rownumber, 14).HorizontalAlignment = xlCenter
    Cells(rownumber, 14).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 15).Select
    Cells(rownumber, 15).HorizontalAlignment = xlCenter
    Cells(rownumber, 15).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 16).Value = txtRecordNo
    Cells(rownumber, 16).Select
    Cells(rownumber, 16).HorizontalAlignment = xlCenter
    Cells(rownumber, 16).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 17).Value = txtRecordYear
    Cells(rownumber, 17) = Format((txtRecordYear.Text), "@")
    Cells(rownumber, 17).Select
    Cells(rownumber, 17).HorizontalAlignment = xlCenter
    Cells(rownumber, 17).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 18).Value = txtOtherComments
    Cells(rownumber, 18).Select
    Cells(rownumber, 18).HorizontalAlignment = xlCenter
    Cells(rownumber, 18).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Cells(rownumber, 19).Value = txtPrice
    Cells(rownumber, 19) = Format((txtPrice.Text), "$#,##0.00")
    Cells(rownumber, 19).HorizontalAlignment = xlCenter
    Cells(rownumber, 19).Select
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End Sub

    Private Sub cmdExit_Click()
    Unload frmBirdDetails
    End Sub

    Private Sub cmdNextRecord_Click()
    rownumber = rownumber + 1
    txtDate.Value = ""
    txtImportExport.Value = ""
    txtOtherComments.Value = ""
    txtRecordNo.Value = ""
    txtRecordYear.Value = ""
    txtOtherComments.Value = ""
    cboMethod.Value = ""
    txtPrice.Value = ""
    txtSaleNumber.Value = ""
    txtMale.Value = ""
    txtFemale.Value = ""
    txtUnknown.Value = ""
    txtFaunaIn.Value = ""
    txtFaunaOut.Value = ""
    txtDate.SetFocus
    End Sub

    Private Sub cmdAddRecord2_Click()
    Sheets("Client Details").Select
    Cells(rownumber, 1).Value = txtNameAddress
    Cells(rownumber, 2).Value = txtLicence2
    Cells(rownumber, 3).Value = cboAreaCode
    Cells(rownumber, 4).Value = txtPhoneNo
    Range("A2:D40").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("G8").Select
    End Sub

    Private Sub cmdExit2_Click()
    Unload frmBirdDetails
    End Sub

    Private Sub cmdNext2_Click()
    rownumber = rownumber + 1
    txtLicence2.Value = ""
    txtNameAddress2.Value = ""
    txtPhoneNo2.Value = ""
    End Sub

    Private Sub txtDate_Change()
    If IsNumeric(txtDate) Then
    ' Date Ok
    Else
    MsgBox ("Date must be DD/MM/YYYY"), vbOKOnly, ("Incorrect Date")
    txtDate = 0
    End If
    End Sub

    Private Sub txtFaunaIn_Change()
    If IsNumeric(txtFaunaIn) Then
    ' Qty Ok
    Else
    MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
    txtFaunaIn = 0
    End If
    End Sub

    Private Sub txtFaunaOut_Change()
    If IsNumeric(txtFaunaOut) Then
    ' Qty Ok
    Else
    MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
    txtFaunaOut = 0
    End If
    End Sub

    Private Sub txtFemale_Change()
    If IsNumeric(txtFemale) Then
    ' Qty Ok
    Else
    MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
    txtFemale = 0
    End If
    End Sub

    Private Sub txtMale_Change()
    If IsNumeric(txtMale) Then
    ' Qty Ok
    Else
    MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
    txtMale = 0
    End If
    End Sub

    Private Sub txtPrice_Change()
    If IsNumeric(txtPrice) Then
    ' QTY Ok
    Else
    MsgBox ("Amount must be Numeric"), vbOKOnly, ("Text Entered")
    txtPrice = 0
    End If
    End Sub

    Private Sub txtRecordYear_Change()
    If IsNumeric(txtRecordYear) Then
    ' Qty Ok
    Else
    MsgBox ("Year must be YYYY"), vbOKOnly, ("Text Entered")
    txtRecordYear = 0
    End If
    End Sub

    Private Sub txtUnknown_Change()
    If IsNumeric(txtUnknown) Then
    ' Qty Ok
    Else
    MsgBox ("Qty must be Numeric"), vbOKOnly, ("Text Entered")
    txtUnknown = 0
    End If
    End Sub

    Private Sub UserForm_Initialize()
    rownumber = [A65536].End(xlUp).Row
    rownumber = rownumber + 1

    Dim address As Range
    For Each address In Range("'Client Details'!A2:A40")
    cboNameAddress.AddItem (address.Value)
    Next
    Dim licence As Range
    For Each licence In Range("'Client Details'!F1:F6")
    cboMethod.AddItem (licence.Value)
    Next
    Dim code As Range
    For Each code In Range("'Client Details'!F12:F15")
    cboAreaCode.AddItem (code.Value)
    Next
    lblTitle = Range("F1").Value
    txtDate.SetFocus
    End Sub



  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey
    If this is the sub terminating your form:



    Private Sub cmdNextPage_Click()
    On Error GoTo 1
    activesheet.Next.Select
    End
    1: Sheets(2).Select
    End Sub



    You need to change the End
    to
    End Sub

    Tom

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    NSW. Australia
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Both the following codes cause the from to exit after they have finished. The first code was posted as a reply to an earlier question on this forum and when it did not work properly I tried the second code and it works but now it also exits the form.

    Private Sub cmdNextPage_Click()
    On Error GoTo 1
    activesheet.Next.Select
    End
    1: Sheets(2).Select
    End Sub

    Private Sub cmdPreviousPage_Click()
    Dim page As Integer
    page = activesheet.Index
    If page = 1 Then
    page = 40
    Else
    page = page + 1
    End If
    End Sub

    I tried changing the end to end sub but it caused a compile error.

    Thanks

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My mistake...
    It should be "Exit Sub"
    not
    "End Sub"
    Try that out
    Tom

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't see anything in your second procedure which would cause your form to close???

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    NSW. Australia
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks TsTom
    Changing the end to exit sub has fixed the problem on the first code.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •