JCHuysamer
New Member
- Joined
- Nov 3, 2015
- Messages
- 43
Hi All
I am creating a user form to capture date on a ws - I have got most of it to work but am stuck on 2 places:
(please see below - I got the code from another thread and started from there)
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Project List")
'ActiveSheet.Unprotect "7319"
iRow = ws.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(iRow, 1).Value = Me.DTPicker1
ws.Cells(iRow, 2).Value = Me.CboPN
'ws.Cells(iRow, 3).Value =
'ws.Cells(iRow, 4).Value =
ws.Cells(iRow, 5).Value = Me.CboPM
ws.Cells(iRow, 6).Value = Me.CboCOW
ws.Cells(iRow, 7).Value = Me.txtComplianceAudit
ws.Cells(iRow, 8).Value = Me.txtJTO
ws.Cells(iRow, 9).Value = Me.txtPI
ws.Cells(iRow, 10).Value = Me.txtTBT
ws.Cells(iRow, 11).Value = Me.PplOnSiteSun
ws.Cells(iRow, 12).Value = Me.PplOnSiteMon
ws.Cells(iRow, 13).Value = Me.PplOnSiteTue
ws.Cells(iRow, 14).Value = Me.PplOnSiteWed
ws.Cells(iRow, 15).Value = Me.PplOnSiteThu
ws.Cells(iRow, 16).Value = Me.PplOnSiteFri
ws.Cells(iRow, 17).Value = Me.PplOnSiteSat
'ws.Cells(iRow, 18).Value =
ws.Cells(iRow, 19).Value = Me.HoursWorkedSun
ws.Cells(iRow, 20).Value = Me.HoursWorkedMon
ws.Cells(iRow, 21).Value = Me.HoursWorkedTue
ws.Cells(iRow, 22).Value = Me.HoursWorkedWed
ws.Cells(iRow, 23).Value = Me.HoursWorkedThu
ws.Cells(iRow, 24).Value = Me.HoursWorkedFri
ws.Cells(iRow, 25).Value = Me.HoursWorkedSat
'ws.Cells(iRow, 26).Value =
'ws.Cells(iRow, 27).Value =
'ws.Cells(iRow, 28).Value =
'ws.Cells(iRow, 29).Value =
'ActiveSheet.Protect "7319"
End Sub
I am creating a user form to capture date on a ws - I have got most of it to work but am stuck on 2 places:
1. All numbers on the user form is copied to the ws as text - should be numbers
2. I need to put a formula on column R (irow, 18) & Z (irow, 26)
2. I need to put a formula on column R (irow, 18) & Z (irow, 26)
R =max(previous 7 columns) ie: =max(K#:Q#)
Z = sum(previous 7 columns)ie: = sum(S#:Y#)
If there is another way to get the information from the user form to the ws it might also be a bit more useful and user friendly for a novice like meZ = sum(previous 7 columns)ie: = sum(S#:Y#)

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Project List")
'ActiveSheet.Unprotect "7319"
iRow = ws.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(iRow, 1).Value = Me.DTPicker1
ws.Cells(iRow, 2).Value = Me.CboPN
'ws.Cells(iRow, 3).Value =
'ws.Cells(iRow, 4).Value =
ws.Cells(iRow, 5).Value = Me.CboPM
ws.Cells(iRow, 6).Value = Me.CboCOW
ws.Cells(iRow, 7).Value = Me.txtComplianceAudit
ws.Cells(iRow, 8).Value = Me.txtJTO
ws.Cells(iRow, 9).Value = Me.txtPI
ws.Cells(iRow, 10).Value = Me.txtTBT
ws.Cells(iRow, 11).Value = Me.PplOnSiteSun
ws.Cells(iRow, 12).Value = Me.PplOnSiteMon
ws.Cells(iRow, 13).Value = Me.PplOnSiteTue
ws.Cells(iRow, 14).Value = Me.PplOnSiteWed
ws.Cells(iRow, 15).Value = Me.PplOnSiteThu
ws.Cells(iRow, 16).Value = Me.PplOnSiteFri
ws.Cells(iRow, 17).Value = Me.PplOnSiteSat
'ws.Cells(iRow, 18).Value =
ws.Cells(iRow, 19).Value = Me.HoursWorkedSun
ws.Cells(iRow, 20).Value = Me.HoursWorkedMon
ws.Cells(iRow, 21).Value = Me.HoursWorkedTue
ws.Cells(iRow, 22).Value = Me.HoursWorkedWed
ws.Cells(iRow, 23).Value = Me.HoursWorkedThu
ws.Cells(iRow, 24).Value = Me.HoursWorkedFri
ws.Cells(iRow, 25).Value = Me.HoursWorkedSat
'ws.Cells(iRow, 26).Value =
'ws.Cells(iRow, 27).Value =
'ws.Cells(iRow, 28).Value =
'ws.Cells(iRow, 29).Value =
'ActiveSheet.Protect "7319"
End Sub