User Form

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:
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:confused: (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
 
Is this what you are looking for?

Code:
[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]
 
Upvote 0
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))"

:banghead:
 
Upvote 0

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