Hi everyone, its my first time using VBA and any sort of coding in general so I have been cracking my head for weeks trying to do this UserForm . I'm wondering if there is anyone here that could help me with it? Any help is appreciated, Thank you in advance!!
I have 2 main issues that I've been trying to figure out,
1: I've created a UserForm to key in data into the excel spreadsheet. There is Name, Product, Date and Quantity. I would like the last column to be Time Taken ( which is Quantity*standard time ) The Data for the standard time is in another sheet ( Sheet2 ) and to get the standard time, I would have to identify first from which group does the product lies, as the standard time is tied to the group. I am able to do it with excel formula ( IFS ) quite easily but I would like to do it with VBA so whoever uses it wouldn't accidentally delete or change the formula. Is there a way to do so? I'll attach the screenshot of the excel formula below.
The Excel formula that I would like to change to VBA and the reference sheet (Sheet2) that states the group and standard time
Not sure if this is needed but, here's the UserForm I use to key in data to the excel sheet
2: After calculating the time taken above, I would like to calculate the total time taken by Name and Date. I'm creating a separate UserForm to search for specific name and date and calculate a certain figure. Once the search button (cmdSearch) is clicked, the textbox for total review DHR (txtDHR) will immediately show the number. At the same time, if it is possible I would like the spreadsheet to automatically update itself at the total review DHR. The date stated will be everyday of the month, for example 2021-10-01, 2021-10-02 and etc , and I would need to do it for every name listed. I'm not very good at explaining so I'm uploading the code below and the screenshot up here. I found the code online and tried to change here and there to fit my spreadsheet but I still couldn't get it.
The whole excel sheet
Thank you so much for reading and helping me. I appreciate every help I can find here!!
I have 2 main issues that I've been trying to figure out,
1: I've created a UserForm to key in data into the excel spreadsheet. There is Name, Product, Date and Quantity. I would like the last column to be Time Taken ( which is Quantity*standard time ) The Data for the standard time is in another sheet ( Sheet2 ) and to get the standard time, I would have to identify first from which group does the product lies, as the standard time is tied to the group. I am able to do it with excel formula ( IFS ) quite easily but I would like to do it with VBA so whoever uses it wouldn't accidentally delete or change the formula. Is there a way to do so? I'll attach the screenshot of the excel formula below.
The Excel formula that I would like to change to VBA and the reference sheet (Sheet2) that states the group and standard time
Not sure if this is needed but, here's the UserForm I use to key in data to the excel sheet
2: After calculating the time taken above, I would like to calculate the total time taken by Name and Date. I'm creating a separate UserForm to search for specific name and date and calculate a certain figure. Once the search button (cmdSearch) is clicked, the textbox for total review DHR (txtDHR) will immediately show the number. At the same time, if it is possible I would like the spreadsheet to automatically update itself at the total review DHR. The date stated will be everyday of the month, for example 2021-10-01, 2021-10-02 and etc , and I would need to do it for every name listed. I'm not very good at explaining so I'm uploading the code below and the screenshot up here. I found the code online and tried to change here and there to fit my spreadsheet but I still couldn't get it.
The whole excel sheet
VBA Code:
Private Sub cmdSearch_Click()
Dim arr, Name As Name, Dat As Date, Dat$, rng As Range, Name As Range, Name$, Quantity As Range
arr = Array("Peter", "Armin", "Vanessa", "Savannah") & Sheets("Sheet1").Cells(r, 8)
Name = Sheets("Sheet1").Cells(r, 2)
Dat = Sheets("Sheet1").Cells(r, 3)
Quantity = Sheets("Sheet1").Cells(r, 5)
With Sheets(Dat)
Set rng = .Rows(8).Find(Dat, lookat:=xlWhole)
If Not rng Is Nothing Then
Set rng = .Column(9).Find(Peter, lookat:=xlWhole)
If Not rng Is Nothing Then
Set Quantity = .Rows(9).Find(Peter, lookat:=xlWhole)
If Not Name Is Nothing Then
.Cells(Name.Row, rng.Column + 1) = cboName
txtDate.Value = Format(txtDate.Value, "yyyy-mm-dd")
.Cells(mch.Row, rng.Column + 2) = TextBox2
.Cells(mch.Row, rng.Column + 3) = TextBox3
Else
MsgBox "can not find product"
End If
Else
MsgBox "can not find date"
End If
End With
Me.Hide
End Sub
Thank you so much for reading and helping me. I appreciate every help I can find here!!