User Form Question

Guzzlr

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Hello All,
I'm having a little difficulty with learning about User Form's

My Code Thus Far:
Code: 
 [COLOR=#339933]'While Loop Checks for Data Beginning In Line 17[/COLOR] 
RowCounter = 17
    While Cells(RowCounter, 2).Value <> ""
        RowCounter = RowCounter + 1
    Wend    
[COLOR=#339900]'Data Input Into Excel Sheet[/COLOR]
    Cells(RowCounter, 2).Value = Tbx_Date.Text
    Cells(RowCounter, 3).Value = Tbx_Time.Value

So, My User Form has two fields to fill in:
1. "Date Field"
2. "Time Field"

When I fill in the Date Field, the User Form enters the date in RANGE(B17), as it should, and continues with B18, B19 as new dates are entered. So the Date Field is working Correctly.

My "Time Field" is not working correctly. When I enter a number ie: 1.5, the code faults on:
Cells(RowCounter, 3).Value = Tbx_Time.Value

What I'm trying to accomplish, is the Date in B17 and the corresponding time in C18.

Thanks for the help
excel 2013

PS, at this time, I have no declarations, which is probably wrong...
 
Last edited:
Sort by date Sort by votes
Guzzlr said:
One more question..
How do i get the the top left corner of the user form to show on F2?
Thanks
Click to expand...

Never Mind, I found this on Google:
Code: 
[COLOR=#333333][I]With UserForm1[/I][/COLOR]  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show [COLOR=#333333][I]End With[/I][/COLOR]
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I'd like to continue this macro with some additions please.
Code: 
Private Sub Btn_ADD_Click()

Dim Lastrow As Long


'************** Data Input Into Excel Sheet ****************


Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
If Lastrow < 17 Then Lastrow = 17
Cells(Lastrow, 2).Value = Tbx_Date.Value
Cells(Lastrow, 3).Value = Tbx_Time.Value
End Sub

Here is the code, and it is working fine.
I have 3 columns
B15 is Date
C15 Is Time
D15 Is Over Time

As the time (column C) continues to build, and it gets over 40 hours, then any time over the 40 hours would be shown on the adjacent cell of Column D.
For example:
C17 = 10
C18 = 10
C19 = 10
C20 = 12

The total now is 42. So 40 would show on C20, and 2 would show on D20. Any new entries would now begin on column D21 and continue (because that would be overtime). Column C would always remain no more than 40.

I have tried IF statements on a non-macro sheet, but continued to get circular references or warnings.

Thanks
 
Upvote 0
This could be a tricky answer for me.
If John worked 10 hrs. on day 1
Then worked 8 hrs. on day 2
Then 12 hrs. on day 3
Then 8 hrs. on day 4
Then 9 hrs. on day 5
We would now be up to 47 hrs.
So on day 5. 7 would need to be put in column 3 and 2 in column 4
And all other days the time would be put in column 4

I may need to have some once else here at Mr. Excel work on this.
 
Upvote 0
My mistake:
So on day 5. 2 would need to be put in column 3 and 7 in column 4

You get the ideal
 
Upvote 0
Hopefully this code helps you out?

Code: 
Private Sub Btn_ADD_Click()


Dim Lastrow As Long




'************** Data Input Into Excel Sheet ****************




    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    If Lastrow < 17 Then Lastrow = 17
    TimeToDate = Application.WorksheetFunction.Sum(Range(Cells(17, 3), Cells(Lastrow, 3)))
    Cells(Lastrow, 2).Value = Tbx_Date.Value
    If TimeToDate < 40 Then
        If TimeToDate + Tbx_Time.Value <= 40 Then
            Cells(Lastrow, 3).Value = Tbx_Time.Value
        Else
            Cells(Lastrow, 3).Value = 40 - TimeToDate
            Cells(Lastrow, 4).Value = Tbx_Time.Value - Cells(Lastrow, 3).Value
        End If
    Else
        Cells(Lastrow, 4).Value = Tbx_Time.Value
    End If
End Sub

So if I understood correctly, each time you add a new line with time worked it will place all time values into the "C" column until 40 hours in total has been worked, then any time over 40 is placed in the "D" column for overtime.

Let u know how it goes.
 
Upvote 0
Yes that should work. I was not able to figure that out. Thanks for your help Dim
Dim Me as xlNoob said:
Hopefully this code helps you out?

Code: 
Private Sub Btn_ADD_Click()


Dim Lastrow As Long




'************** Data Input Into Excel Sheet ****************




    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    If Lastrow < 17 Then Lastrow = 17
    TimeToDate = Application.WorksheetFunction.Sum(Range(Cells(17, 3), Cells(Lastrow, 3)))
    Cells(Lastrow, 2).Value = Tbx_Date.Value
    If TimeToDate < 40 Then
        If TimeToDate + Tbx_Time.Value <= 40 Then
            Cells(Lastrow, 3).Value = Tbx_Time.Value
        Else
            Cells(Lastrow, 3).Value = 40 - TimeToDate
            Cells(Lastrow, 4).Value = Tbx_Time.Value - Cells(Lastrow, 3).Value
        End If
    Else
        Cells(Lastrow, 4).Value = Tbx_Time.Value
    End If
End Sub

So if I understood correctly, each time you add a new line with time worked it will place all time values into the "C" column until 40 hours in total has been worked, then any time over 40 is placed in the "D" column for overtime.

Let u know how it goes.
Click to expand...
 
Upvote 0
Guzzlr said:
I'd like to continue this macro with some additions please.
Code: 
Private Sub Btn_ADD_Click()

Dim Lastrow As Long


'************** Data Input Into Excel Sheet ****************


Lastrow = Cells(Rows.Count, "B").End(xlUp).Row + 1
If Lastrow < 17 Then Lastrow = 17
Cells(Lastrow, 2).Value = Tbx_Date.Value
Cells(Lastrow, 3).Value = Tbx_Time.Value
End Sub

Here is the code, and it is working fine.
I have 3 columns
B15 is Date
C15 Is Time
D15 Is Over Time

As the time (column C) continues to build, and it gets over 40 hours, then any time over the 40 hours would be shown on the adjacent cell of Column D.
For example:
C17 = 10
C18 = 10
C19 = 10
C20 = 12

The total now is 42. So 40 would show on C20, and 2 would show on D20. Any new entries would now begin on column D21 and continue (because that would be overtime). Column C would always remain no more than 40.

I have tried IF statements on a non-macro sheet, but continued to get circular references or warnings.

Thanks
Click to expand...
I see a mistake on my part when C20 at 12 hours, the total is now 42. So, C20 would show 10 to equal a total of no more than 40, not 40 showing on C20. the difference of 42 - 40 of 2 would show on D20 to match when 40 hours was obtained.
 
Upvote 0
I'll try this code when I get home from work. thank you
 
Upvote 0
Guzzlr said:
I see a mistake on my part when C20 at 12 hours, the total is now 42. So, C20 would show 10 to equal a total of no more than 40, not 40 showing on C20. the difference of 42 - 40 of 2 would show on D20 to match when 40 hours was obtained.
Click to expand...

This is how the code I posted should work. So column C will never have a total sum of more than 40 and any extra will be placed in column D.

Hope it goes well.
 
Upvote 0
OK...The program is working correctly!

Here's my test:
Little Johnny Works:

Day 1 = 10.0
Day 2 = 10.5
Day 3 = 10.5
Day 4 = 11.0
Day 5 = 5.0

the Day's were added correctly, so Regular Time totaled no more than 40 and the Over Time began adding as needed
Thank you for the help, this is very good.

​
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Regular Time [/TD]
[TD="align: center"]Over Time[/TD]
[/TR]
[TR]
[TD="align: center"]1-1-2017[/TD]
[TD="align: center"]10.0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1-2-2017[/TD]
[TD="align: center"]10.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1-3-2017[/TD]
[TD="align: center"]10.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1-4-2017[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]1-5-2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 414"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VBA to stop writing to specific sheet based on user selection
Replies
1
Views
375
Paul15
P
M
  • Question Question
VBA error not making sense
2
Replies
10
Views
237
Skyybot
Skyybot
M
  • Question Question
Protecting Sheet from users but permitting Power Automate
Replies
1
Views
575
ExcelToDAX
E
E
  • Question Question
Creating data entry form
Replies
3
Views
402
EssKayKay
E
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
141
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

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