ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Entry Form Problem (https://www.excelbanter.com/excel-programming/391298-entry-form-problem.html)

hazel
Entry Form Problem
 
Hi There

I'm having a problem with a race entry form (please don't ask) and I'm using
a UserForm to process and enter the details on to a sheet named "Channel"
each competitor can make 3 entries and each one goes on the row immeadiately
after their first entry using the following code.

Private Sub Cmd506_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Channel")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database

ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.TB511.Value) + " " &
Trim(UserForm2.TB510.Value)
ws.Cells(iRow, 2).Value = Me.Club1.Value
ws.Cells(iRow, 3).Value = Me.Tb507.Value
ws.Cells(iRow, 4).Value = Me.Tb500.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb501.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb502.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb503.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb504.Value * 5#
ws.Cells(iRow, 9).Value = Me.Tb505.Value * 1#
ws.Cells(iRow, 10).Value = Me.Tb506.Value * 2#
Me.Tb519.Value = Format(Val(Trim(Tb500.Value * 5#))) + (Val(Trim(Tb501.Value
* 0.5))) + (Val(Trim(Tb502.Value * 1#))) + (Val(Trim(Tb503.Value * 2#))) +
(Val(Trim(Tb504.Value * 5#))) + (Val(Trim(Tb505.Value * 1#))) +
(Val(Trim(Tb506.Value * 2#)))
ws.Cells(iRow, 11).Value = Me.Tb519.Value


Me.Tb500.Value = ""
Me.Tb501.Value = ""
Me.Tb502.Value = ""
Me.Tb503.Value = ""
Me.Tb504.Value = ""
Me.Tb505.Value = ""
Me.Tb506.Value = ""
Me.Tb507.Value = ""

End Sub

Everything enters on the sheet OK but I have to keep flicking between the
UserForm and the sheet to see if everything is correct and in Column L I
have the usual formula =sum(k2:K4) to finally check the grand total of the 3
entries. And of course I have to enter this formula for the next competitor
=sum(k5:k7) and so on down the sheet and with normally over 100 competitors
usually taking part its a real pain in the rear end -- get my meaning folks.
Could really do without that formula on the sheet ( deletes are so damned
easy) can the UserForm not do something similar??











--
Many thanks

hazel

Tom Ogilvy
Entry Form Problem
 
Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown)
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next

--
Regards,
Tom Ogilvy



"Hazel" wrote:

Hi There

I'm having a problem with a race entry form (please don't ask) and I'm using
a UserForm to process and enter the details on to a sheet named "Channel"
each competitor can make 3 entries and each one goes on the row immeadiately
after their first entry using the following code.

Private Sub Cmd506_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Channel")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database

ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.TB511.Value) + " " &
Trim(UserForm2.TB510.Value)
ws.Cells(iRow, 2).Value = Me.Club1.Value
ws.Cells(iRow, 3).Value = Me.Tb507.Value
ws.Cells(iRow, 4).Value = Me.Tb500.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb501.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb502.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb503.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb504.Value * 5#
ws.Cells(iRow, 9).Value = Me.Tb505.Value * 1#
ws.Cells(iRow, 10).Value = Me.Tb506.Value * 2#
Me.Tb519.Value = Format(Val(Trim(Tb500.Value * 5#))) + (Val(Trim(Tb501.Value
* 0.5))) + (Val(Trim(Tb502.Value * 1#))) + (Val(Trim(Tb503.Value * 2#))) +
(Val(Trim(Tb504.Value * 5#))) + (Val(Trim(Tb505.Value * 1#))) +
(Val(Trim(Tb506.Value * 2#)))
ws.Cells(iRow, 11).Value = Me.Tb519.Value


Me.Tb500.Value = ""
Me.Tb501.Value = ""
Me.Tb502.Value = ""
Me.Tb503.Value = ""
Me.Tb504.Value = ""
Me.Tb505.Value = ""
Me.Tb506.Value = ""
Me.Tb507.Value = ""

End Sub

Everything enters on the sheet OK but I have to keep flicking between the
UserForm and the sheet to see if everything is correct and in Column L I
have the usual formula =sum(k2:K4) to finally check the grand total of the 3
entries. And of course I have to enter this formula for the next competitor
=sum(k5:k7) and so on down the sheet and with normally over 100 competitors
usually taking part its a real pain in the rear end -- get my meaning folks.
Could really do without that formula on the sheet ( deletes are so damned
easy) can the UserForm not do something similar??











--
Many thanks

hazel

hazel
Entry Form Problem
 
Hi Tom

Thanks for quick response, have entered the code and even tried adding
another Command Button it bugs out on this line

rLast = Cells(rFirst, "K").End(xlDown) '<<<< rLast = 0

even though I have entered the members info

Any suggestions please and am I entering the code in the correct place ie
the Userform code not in a Module
--
Many thanks

hazel


"Tom Ogilvy" wrote:

Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown)
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next

--
Regards,
Tom Ogilvy



"Hazel" wrote:

Hi There

I'm having a problem with a race entry form (please don't ask) and I'm using
a UserForm to process and enter the details on to a sheet named "Channel"
each competitor can make 3 entries and each one goes on the row immeadiately
after their first entry using the following code.

Private Sub Cmd506_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Channel")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database

ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.TB511.Value) + " " &
Trim(UserForm2.TB510.Value)
ws.Cells(iRow, 2).Value = Me.Club1.Value
ws.Cells(iRow, 3).Value = Me.Tb507.Value
ws.Cells(iRow, 4).Value = Me.Tb500.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb501.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb502.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb503.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb504.Value * 5#
ws.Cells(iRow, 9).Value = Me.Tb505.Value * 1#
ws.Cells(iRow, 10).Value = Me.Tb506.Value * 2#
Me.Tb519.Value = Format(Val(Trim(Tb500.Value * 5#))) + (Val(Trim(Tb501.Value
* 0.5))) + (Val(Trim(Tb502.Value * 1#))) + (Val(Trim(Tb503.Value * 2#))) +
(Val(Trim(Tb504.Value * 5#))) + (Val(Trim(Tb505.Value * 1#))) +
(Val(Trim(Tb506.Value * 2#)))
ws.Cells(iRow, 11).Value = Me.Tb519.Value


Me.Tb500.Value = ""
Me.Tb501.Value = ""
Me.Tb502.Value = ""
Me.Tb503.Value = ""
Me.Tb504.Value = ""
Me.Tb505.Value = ""
Me.Tb506.Value = ""
Me.Tb507.Value = ""

End Sub

Everything enters on the sheet OK but I have to keep flicking between the
UserForm and the sheet to see if everything is correct and in Column L I
have the usual formula =sum(k2:K4) to finally check the grand total of the 3
entries. And of course I have to enter this formula for the next competitor
=sum(k5:k7) and so on down the sheet and with normally over 100 competitors
usually taking part its a real pain in the rear end -- get my meaning folks.
Could really do without that formula on the sheet ( deletes are so damned
easy) can the UserForm not do something similar??











--
Many thanks

hazel

Tom Ogilvy
Entry Form Problem
 
Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown).row
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next

--
Regards,
Tom Ogilvy


"Hazel" wrote:

Hi Tom

Thanks for quick response, have entered the code and even tried adding
another Command Button it bugs out on this line

rLast = Cells(rFirst, "K").End(xlDown) '<<<< rLast = 0

even though I have entered the members info

Any suggestions please and am I entering the code in the correct place ie
the Userform code not in a Module
--
Many thanks

hazel


"Tom Ogilvy" wrote:

Dim rFirst as Long, rLast as Long
Dim i as Long
' assumes first row of data is row 2
' change to suit
rFirst = 2
rLast = Cells(rFirst,"K").End(xldown)
for i = rFirst to rLast step 3
cells(i,"L").Value = Application.Sum( _
cells(i,"K").Resize(3,1))
Next

--
Regards,
Tom Ogilvy



"Hazel" wrote:

Hi There

I'm having a problem with a race entry form (please don't ask) and I'm using
a UserForm to process and enter the details on to a sheet named "Channel"
each competitor can make 3 entries and each one goes on the row immeadiately
after their first entry using the following code.

Private Sub Cmd506_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Channel")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database

ws.Cells(iRow, 1).Value = " " & Trim(UserForm2.TB511.Value) + " " &
Trim(UserForm2.TB510.Value)
ws.Cells(iRow, 2).Value = Me.Club1.Value
ws.Cells(iRow, 3).Value = Me.Tb507.Value
ws.Cells(iRow, 4).Value = Me.Tb500.Value * 5#
ws.Cells(iRow, 5).Value = Me.Tb501.Value * 0.5
ws.Cells(iRow, 6).Value = Me.Tb502.Value * 1#
ws.Cells(iRow, 7).Value = Me.Tb503.Value * 2#
ws.Cells(iRow, 8).Value = Me.Tb504.Value * 5#
ws.Cells(iRow, 9).Value = Me.Tb505.Value * 1#
ws.Cells(iRow, 10).Value = Me.Tb506.Value * 2#
Me.Tb519.Value = Format(Val(Trim(Tb500.Value * 5#))) + (Val(Trim(Tb501.Value
* 0.5))) + (Val(Trim(Tb502.Value * 1#))) + (Val(Trim(Tb503.Value * 2#))) +
(Val(Trim(Tb504.Value * 5#))) + (Val(Trim(Tb505.Value * 1#))) +
(Val(Trim(Tb506.Value * 2#)))
ws.Cells(iRow, 11).Value = Me.Tb519.Value


Me.Tb500.Value = ""
Me.Tb501.Value = ""
Me.Tb502.Value = ""
Me.Tb503.Value = ""
Me.Tb504.Value = ""
Me.Tb505.Value = ""
Me.Tb506.Value = ""
Me.Tb507.Value = ""

End Sub

Everything enters on the sheet OK but I have to keep flicking between the
UserForm and the sheet to see if everything is correct and in Column L I
have the usual formula =sum(k2:K4) to finally check the grand total of the 3
entries. And of course I have to enter this formula for the next competitor
=sum(k5:k7) and so on down the sheet and with normally over 100 competitors
usually taking part its a real pain in the rear end -- get my meaning folks.
Could really do without that formula on the sheet ( deletes are so damned
easy) can the UserForm not do something similar??











--
Many thanks

hazel


All times are GMT +1. The time now is 06:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com