| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| data entry form | Excel Discussion (Misc queries) | |||
| Form for data entry | Excel Discussion (Misc queries) | |||
| Data entry form | Excel Programming | |||
| Data Entry Form (similar to default Excel DataForm) | Excel Programming | |||
| Data Entry Form | Excel Programming | |||
Linear Mode
