Formcode

Phil C

Board Regular
Joined
Apr 10, 2002
Messages
64
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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
 
Upvote 0
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, :cool:.Value = txtImportExport
Cells(rownumber, :cool:.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
 
Upvote 0
Hey
If this is the sub terminating your form:

<pre>

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

</pre>

You need to change the End
to
End Sub

Tom
 
Upvote 0
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
 
Upvote 0
I don't see anything in your second procedure which would cause your form to close???
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top