Formcode

[Phil C]

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.

[Juan Pablo González]

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.

[Phil C]

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

[Juan Pablo González]

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

[Phil C]

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

[Tom Schreiner]

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

[Phil C]

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

[Tom Schreiner]

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

[Tom Schreiner]

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

[Phil C]

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