User Form

[JCHuysamer]

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:

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)​

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 me (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

 

[tygrrboi]

Is this what you are looking for?

[COLOR=#008000]
[/COLOR]ws.Cells(iRow, 11).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteSun[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 12).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteMon[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 13).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteTue[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 14).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteWed[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 15).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteThu[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 16).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteFri[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 17).Value = [COLOR=#00ff00]Int([/COLOR]Me.PplOnSiteSat[COLOR=#00ff00])[/COLOR][COLOR=#008000][/COLOR]
[COLOR=#ff0000]    ws.Cells(iRow, 18).Value = "=MAX($K$" & iRow & ":$Q$" & iRow & ")"[/COLOR]
    ws.Cells(iRow, 19).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedSun[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 20).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedMon[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 21).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedTue[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 22).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedWed[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 23).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedThu[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 24).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedFri[COLOR=#00ff00])[/COLOR]
    ws.Cells(iRow, 25).Value = [COLOR=#00ff00]Int([/COLOR]Me.HoursWorkedSat[COLOR=#00ff00])[/COLOR]
[COLOR=#ff0000]    ws.Cells(iRow, 26).Value =[COLOR=#ff0000] "=SUM($S$" & iRow & ":$Y$" & iRow & ")"[/COLOR][/COLOR]
[COLOR=#008000]    [/COLOR]

 

[JCHuysamer]

Thanks that worked perfectly

Now the next challenge for which I apparently don't have the gray matter capacity

I need to do a lookup from column B to populate column C - the data i require is on another ws

ws.Cells(iRow, 3).Value = "=lookup($b$" & iRow & ",'Project Data'!(A),'Project Data'!(B))"