aboly8000

Board Regular
Joined
Sep 4, 2019
Messages
59
Hello
I need a vba form with two fields, one of which is a drop down list of names and the other is a decimal value to store names in the cell and a decimal in the comment.
Then I want to formulate and calculate on the decimals inside the comments.
Can anyone help me?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
No doubt I can help, but your explanation is very unclear.

Userform with dropdown list and a text field
What is the source for the drop down list?
What type of data is in this list?
How is the decimal value used. You mention to store names in a cell. How is a decimal value linked to a cell?

Please provide some examples
 
Upvote 0
No doubt I can help, but your explanation is very unclear.

Userform with dropdown list and a text field
What is the source for the drop down list?
What type of data is in this list?
How is the decimal value used. You mention to store names in a cell. How is a decimal value linked to a cell?

Please provide some examples

The source of my drop-down list is a table called Table 4, which contains a list of worker names.
I have several columns called Worker Supervisor, Worker First, Worker Second, and Worker Third.
I want to enter the hours worked for each worker, then extract the total hours of work per worker by worker title, by pivot Table.
 
Upvote 0
aboly8000,

1. can you copy the header row of table 4 and post it here?
2a. are the hours worked added or in this table, or
2b. are they to be held with the worker name in a separate table as source for the pivot? If so what is the header row of this table?
 
Upvote 0
Upvote 0
I am not sure if your design for table1 is efficient for extracting the hours, but I leave that up to you. In order to record the hours I have added columns inbetween the name columns

Table1Table4
Worker namesWorker SupervisorWS hoursWorker1W1 HoursWorker2W2 HoursWorker3W3 HoursWorker4W4 Hours
KEVINKEVIN8NIMAMATTHEWGARYTIMOTHY4,5
NIMANIMAMATTHEWGARY4TIMOTHY2JOSE2
MATTHEWMATTHEW7GARYTIMOTHY4JOSELARRY4,5
GARYGARYTIMOTHY2JOSELARRY5JEFFREY5
TIMOTHYTIMOTHYJOSELARRYJEFFREY6FRANK4,333333333
JOSEJOSELARRY5JEFFREYFRANKSCOTT0
LARRYLARRY5JEFFREY6FRANK3SCOTT7ERIC7
JEFFREYJEFFREYFRANKSCOTTERIC7STEPHEN7
FRANKFRANKSCOTT7ERIC3STEPHENANDREW0
SCOTTSCOTTERIC7STEPHEN3ANDREWRAYMOND0
ERICERICSTEPHENANDREW6RAYMOND4GREGORY4
STEPHENSTEPHENANDREWRAYMONDGREGORY5KEVIN5
ANDREWANDREWRAYMONDGREGORY6KEVIN6NIMA6
RAYMONDRAYMONDGREGORY7KEVINNIMA7MATTHEW7
GREGORYGREGORYKEVINNIMA6MATTHEW8GARY8
Peter

<tbody>
</tbody>

Then I have made a userform with a combobox for the names and a textbox for the time entry. An OK button to add the time to the name and a Close button to close the form.

image sharing
q5hqtYn


AF1QipML2j8ip2AqRhjWIL8cioAh2iEaZGVyIkFF43lQ

AF1QipML2j8ip2AqRhjWIL8cioAh2iEaZGVyIkFF43lQ
The combobox I gave the name cbxNames, the textbox tbxHours, the 'Copy to sheet' button btnOK, the cancel button btnCancel

This leads to the following userform code to make it work:
Code:
Option Explicit






Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    'find name in table1 and add hours in column behind
    Dim rFound As Range
    Dim vSp As Variant
    
    vSp = Split(tbxHours, ":")
    Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
    rFound.Offset(0, 1) = vSp(0) + vSp(1) / 60


End Sub







Private Sub tbxHours_Enter()
    tbxHours = ""
End Sub


Private Sub tbxHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Len(tbxHours.Value) = 0 Then
            tbxHours.Value = "00:00"
        ElseIf Len(tbxHours.Value) = 4 Then
            tbxHours.Value = "0" & tbxHours.Value
        End If
        If IsDate(tbxHours.Value) And Len(tbxHours.Text) = 5 Then
        Else
            MsgBox "Input Hour like this Example 05:35"
            tbxHours.Text = "00:00"
        End If
End Sub


Private Sub UserForm_Initialize()
    With cbxNames
        .RowSource = Blad2.ListObjects("Table4").ListColumns(1).DataBodyRange.Address
    End With
    tbxHours = "00:00"
End Sub
 
Upvote 0
I am not sure if your design for table1 is efficient for extracting the hours, but I leave that up to you. In order to record the hours I have added columns inbetween the name columns

Table1Table4
Worker namesWorker SupervisorWS hoursWorker1W1 HoursWorker2W2 HoursWorker3W3 HoursWorker4W4 Hours
KEVINKEVIN8NIMAMATTHEWGARYTIMOTHY4,5
NIMANIMAMATTHEWGARY4TIMOTHY2JOSE2
MATTHEWMATTHEW7GARYTIMOTHY4JOSELARRY4,5
GARYGARYTIMOTHY2JOSELARRY5JEFFREY5
TIMOTHYTIMOTHYJOSELARRYJEFFREY6FRANK4,333333333
JOSEJOSELARRY5JEFFREYFRANKSCOTT0
LARRYLARRY5JEFFREY6FRANK3SCOTT7ERIC7
JEFFREYJEFFREYFRANKSCOTTERIC7STEPHEN7
FRANKFRANKSCOTT7ERIC3STEPHENANDREW0
SCOTTSCOTTERIC7STEPHEN3ANDREWRAYMOND0
ERICERICSTEPHENANDREW6RAYMOND4GREGORY4
STEPHENSTEPHENANDREWRAYMONDGREGORY5KEVIN5
ANDREWANDREWRAYMONDGREGORY6KEVIN6NIMA6
RAYMONDRAYMONDGREGORY7KEVINNIMA7MATTHEW7
GREGORYGREGORYKEVINNIMA6MATTHEW8GARY8
Peter

<tbody>
</tbody>

Then I have made a userform with a combobox for the names and a textbox for the time entry. An OK button to add the time to the name and a Close button to close the form.

image sharing
q5hqtYn


AF1QipML2j8ip2AqRhjWIL8cioAh2iEaZGVyIkFF43lQ

AF1QipML2j8ip2AqRhjWIL8cioAh2iEaZGVyIkFF43lQ
The combobox I gave the name cbxNames, the textbox tbxHours, the 'Copy to sheet' button btnOK, the cancel button btnCancel

This leads to the following userform code to make it work:
Code:
Option Explicit






Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    'find name in table1 and add hours in column behind
    Dim rFound As Range
    Dim vSp As Variant
    
    vSp = Split(tbxHours, ":")
    Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
    rFound.Offset(0, 1) = vSp(0) + vSp(1) / 60


End Sub







Private Sub tbxHours_Enter()
    tbxHours = ""
End Sub


Private Sub tbxHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Len(tbxHours.Value) = 0 Then
            tbxHours.Value = "00:00"
        ElseIf Len(tbxHours.Value) = 4 Then
            tbxHours.Value = "0" & tbxHours.Value
        End If
        If IsDate(tbxHours.Value) And Len(tbxHours.Text) = 5 Then
        Else
            MsgBox "Input Hour like this Example 05:35"
            tbxHours.Text = "00:00"
        End If
End Sub


Private Sub UserForm_Initialize()
    With cbxNames
        .RowSource = Blad2.ListObjects("Table4").ListColumns(1).DataBodyRange.Address
    End With
    tbxHours = "00:00"
End Sub


Thank you very much. Now I have a question for you.
How in this table can we extract the number of work and the sum of hours of work and the title of worker for each person by pivot table?
 
Upvote 0
That is EXACTLY why I noted that your table 1 is not efficient for extracting the hours. When you set up a workbook you need to think about both entry of data and how you are going to use it. I will look at it next week, but I will for sure change the layout of table1
 
Upvote 0
That is EXACTLY why I noted that your table 1 is not efficient for extracting the hours. When you set up a workbook you need to think about both entry of data and how you are going to use it. I will look at it next week, but I will for sure change the layout of table1
Thank you very much for taking the time
 
Upvote 0
I have arranged the two tables as follows. This allows you in Table1 to sort on position or name, etc. It also allows you to do an easy pivottable.



The pivot table could look something like:



Then I have modified the userform slightly. I have added a textbox called tbxPosition, to hold the position of the selected person:



In use the form will look like:



And the code to run the userform:
Code:
Option Explicit


Private Sub btnCancel_Click()
    Unload Me
End Sub




Private Sub btnOK_Click()
    'find name in table1 and add hours in column behind
    Dim rFound As Range
    Dim vSp As Variant
    
    vSp = Split(tbxHours, ":")
    Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
    rFound.Offset(0, 2) = rFound.Offset(0, 2) + vSp(0) + vSp(1) / 60




End Sub


Private Sub cbxNames_Change()
    Dim rFound As Range
    If Len(cbxNames) Then
    
        Set rFound = ActiveSheet.ListObjects("Table1").DataBodyRange.Find(what:=cbxNames)
        If Not rFound Is Nothing Then
            tbxPosition = rFound.Offset(0, 1)
        Else
            tbxPosition = ""
        End If
    End If
End Sub


Private Sub tbxHours_Change()
    If Len(tbxHours) = 2 And InStr(1, tbxHours, ":") = 0 Then
        tbxHours = tbxHours & ":"
    End If
End Sub






Private Sub tbxHours_Enter()
    tbxHours = ""
End Sub




Private Sub tbxHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Len(tbxHours.Value) = 0 Then
            tbxHours.Value = "00:00"
        ElseIf Len(tbxHours.Value) = 4 Then
            tbxHours.Value = "0" & tbxHours.Value
        End If
        If IsDate(tbxHours.Value) And Len(tbxHours.Text) = 5 Then
        Else
            MsgBox "Input Hour like this Example 05:35"
            tbxHours.Text = "00:00"
        End If
End Sub




Private Sub UserForm_Initialize()
    With cbxNames
        .RowSource = ActiveSheet.ListObjects("Table4").ListColumns(1).DataBodyRange.Address
    End With
    tbxHours = "00:00"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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