Userform to enter data in location based on value

[hajiali]

Hello Excel experts I need help with a userform to enter data based on the value type in the userform.

here is what I have.

I have USERFORM with the Following: Textbox named: ENUMBER (must be Numerical if a letter is entered to alert user only use number")
ListBox named: DATFTO ( I wish to have the dropdown list with "DAT", "FTO")
Textbox named: DAY (use MM/DD or MM/DD/YY format would prefer to have option to enter multiple number of dates separated by a comma)
CommandButton: DONE (to End Userform)
CommandButton: ENTER (enter data onto the sheet)

I have sheet "Employee List" with the Following: column B as the ENUMBERS
Range W:AU as the DAT entry
Range AY:BH as the FTO entry

Here is what I want the Userform to do:
When users enter ENUMBER
specify DAT or FTO (from DropDown List)
Enter a Dates
and presses enter

I what each Date separated by comma to be enter in the same row of the ENUMBER of Employee list sheet. if DAT is selected then date be enter Range W:AU (same row of ENUMBER) last blank column of that range.

EX: ENUMBER = 123456
DATFTO = DAT
DATE = 8/20,8/21,8/30
and entered is pressed

if sheet Employee List
CELL B130 = 123456
and if
W130 has value
X130 has value
Y130 has value
then
Cells Z130=8/20
AA130=8/21
AB130=8/30

any help is greatly appreciated. let me know if I need to clarify anything.

 

[hajiali]

Ok, this is a updated code based on your revised request. I have updated the same file as well HERE

Private Sub CommandButton2_Click()

Dim EmpID$, dDate$, Rg As Range
EmpID = TextBox1.Value

If TextBox1.Value = "" Or (TextBox2.Value = "" And TextBox3.Value = "") Then
   MsgBox "Please fill-in Employee ID & either DAT or FTO feilds to proceed ... ", vbExclamation: Exit Sub
End If

If Not IsNumeric(EmpID) Then MsgBox "Employee ID must be numeric", vbExclamation: Exit Sub

With Sheets("Employee List")
   For i = 1 To 2
      dDate = IIf(i = 1, TextBox2.Value, TextBox3.Value)
      If Len(dDate) > 0 Then
         Set Rg = .Columns(2).Find(EmpID, lookat:=xlWhole)
         If Rg Is Nothing Then
            MsgBox "Employee ID # " & EmpID & " doesn't exist!", vbExclamation: Exit Sub
         Else
            Set Rg = IIf(i = 1, .Cells(Rg.Row, "W").Resize(, 25), .Cells(Rg.Row, "AY").Resize(, 10))
            If InStr(dDate, ",") Then
               For x = 0 To UBound(Split(dDate, ","))
                  Rg.Find("", after:=Rg.Cells(Rg.Count)) = Split(dDate, ",")(x)
               Next
            Else
               Rg.Find("", after:=Rg.Cells(Rg.Count)) = dDate
            End If
         End If
      End If
   Next
End With

Unload Me

End Sub

That works amazing mse330 Thank you so much. One last request that I just came across while trying it out. Is there a way to restrict the data entry in textbox2 and textbox3 between the "," to be in date format "MM/DD". and if any of the entry in either fields between the "," is not in that format then to alert user "You have entered one or more invalid dates please check all dates and try again"

 

[mse330]

Glad to help & you're welcome ... Please don't quote the whole post as it clutters the thread, if needed, just quote the required part.

I have two questions

1. What should happen if all the cells are filled for the employee ? i.e. no more empty cells to enter the dates ? Currently, the code will error out
2. If the user enters MM/DD only, excel by default considers current year for the date. What if you're using the file at the beginning of the year & entering data for last week which is Dec of previous year ?

 

[hajiali]

I was just thinking about that glad you asked. this gets a little complicated.

1. column V has the number of DAT available ie the number of empty cell for that employee.
so before the the userform proceed. if Textbox2 has 4 dates entered to check column V same row as EmpID and make sure it is greater than or equal to 4 if not alert users " Employee does not have sufficient number of DAT remaining"

similarly

column AX has the number of FTO available ie the number of empty cell for that employee
so before the the userform proceed. if Textbox3 has 1 dates entered to check column AX same row as EmpID and make sure it is greater than or equal to 1 if not alert users " Employee does not have sufficient number of FTO remaining"

2. If there is a way to have both MM/DD and MM/DD/YYYY acceptable formats that would be great. because you make a great point about the end of year. However, I still would like the option to have MM/DD

 

[mse330]

I have added a line in the code to check # of blank cells so I'm not depending on columns V & AX ... Regarding the date, the code will accept any valid date irrespective of the format. i.e. 22-Aug, 1/11/2021 ... etc. Anyway, good night for now & will see tomorrow if you have any more comments

File can be accessed HERE

Option Explicit
Private Sub CommandButton2_Click()

Dim EmpID$, dDate$, Rg As Range, EmpCells&, i&, x&
EmpID = TextBox1.Value

If TextBox1.Value = "" Or (TextBox2.Value = "" And TextBox3.Value = "") Then
   MsgBox "Please fill-in Employee ID & either DAT or FTO feilds to proceed ... ", vbExclamation: Exit Sub
End If

If Not IsNumeric(EmpID) Then MsgBox "Employee ID must be numeric", vbExclamation: Exit Sub

With Sheets("Employee List")
   For i = 1 To 2
      dDate = IIf(i = 1, TextBox2.Value, TextBox3.Value)
      If Len(dDate) > 0 Then
         Set Rg = .Columns(2).Find(EmpID, lookat:=xlWhole)
         If Rg Is Nothing Then
            MsgBox "Employee ID # " & EmpID & " doesn't exist!", vbExclamation: Exit Sub
         Else
            Set Rg = IIf(i = 1, .Cells(Rg.Row, "W").Resize(, 25), .Cells(Rg.Row, "AY").Resize(, 10))
            EmpCells = Evaluate("countblank(" & Rg.Address & ")")
            If UBound(Split(dDate, ",")) + 1 > EmpCells Then
               MsgBox "Employee does not have sufficient number of " & Choose(i, "DAT", "FTO") & " remaining" & vbLf & "Only " & EmpCells & " left", vbExclamation
               Exit Sub
            End If
            If InStr(dDate, ",") Then
               For x = 0 To UBound(Split(dDate, ","))
                  If Not IsDate(Split(dDate, ",")(x)) Then
                     MsgBox Split(dDate, ",")(x) & " is not a valid date ! ... Please amend", vbExclamation
                     Exit Sub
                  End If
               Next
               For x = 0 To UBound(Split(dDate, ","))
                  Rg.Find("", after:=Rg.Cells(Rg.Count)) = Split(dDate, ",")(x)
               Next
            Else
               If Not IsDate(dDate) Then MsgBox dDate & " is not a valid date ! ... Please amend", vbExclamation: Exit Sub
               Rg.Find("", after:=Rg.Cells(Rg.Count)) = dDate
            End If
         End If
      End If
   Next
End With

Unload Me

End Sub

 

[hajiali]

I would really need it to be dependent on columns V and AX as every employee has 25 empty cell for DATs and 10 empty cells however not employees will be max out on DATs and FTOs.

 

[mse330]

Good morning Ali,

Maybe I was too sleepy I didn't explain it well. What I meant is I have added 1 more variable called EmpCells in the code itself (check the below code line) to count the blank cells for the selected employee whether DATs or FTOs so I won't depend on the columns V & AX as the code will take care of that & in case he/she will exceed number of blank cells, a message box will appear to notify the user
EmpCells = Evaluate("countblank(" & Rg.Address & ")")

Regarding the date, I have also amended the code so it accepts any valid date value in any format & not only MM/DD. So, 01/03, 2-Aug-2021 ... ect. should work fine.

Try both scenarios as per my code in post # 14 & let me know in case in encounter any issue or have further comments

 

[hajiali]

Understood however it does needs to depend on the Values of V and AX and not the empty cells. Reason is the all employees have 25 empty Cells for DATs and 10 empty cells for FTOs but not all employee will have had accumulated 25 DATs and 10 FTOs. Some employee have only accumulated only 20 DATs in the beginning of the year but still have 25 empty cells I have the Sheet with conditional formatted to black out the last 5 cells for those certain employee however the cells are empty. Also, V and AX have formula to subtract 1 day from accumulated for have date that is entered. Hope this makes sense let me know if you have any questions

the Date Formatting works great ?

 

[mse330]

Ok, I see your point. In this case, you need to amend one line of the code only

From this
EmpCells = Evaluate("countblank(" & Rg.Address & ")")

To this
EmpCells = IIf(i = 1, .Cells(Rg.Row, "V"), .Cells(Rg.Row, "AX"))

 

[hajiali]

Hello Mse330 Im trying to accomplish something else similar to what we had created before. This time I want Textbox1 start from C and go down and textbox2 start from D and go across
I have adjusted the previous user form to below. the results i get are as follows with the below code

A	B	C	D	E	F	G	H	I	J
		9324​	1​	2​	3​	3​	10​	9​	8​
		18200​

The results should be as follows

A	B	C	D	E	F	G	H	I	J
		9324​	1​	2​	3​
		18200​	3​	10​	9​	8​

also if anyway possible to add date stamp in column A and time stamp in column B when CommandButton1 is click.

Option Explicit
Private Sub CommandButton1_Click()

Dim EmpID$, dDate$, Rg As Range, i&, x&
EmpID = TextBox1.Value
If TextBox1.Value = "" Or TextBox2.Value = "" Then
   MsgBox "PLEASE FILL IN EMPLOYEE NUMBER & ENTER BID LINE CHOOSES ... ", vbExclamation: Exit Sub
End If

If Not IsNumeric(EmpID) Then MsgBox "INVALID EMPLOYEE NUMBER ENTER NUMBER WITHOUT THE E", vbExclamation: Exit Sub

With Sheets("Employee List")
   For i = 1 To 2
      dDate = IIf(i = 1, TextBox1.Value, TextBox2.Value)
      If Len(dDate) > 0 Then
         Set Rg = .Columns(2).Find(EmpID, lookat:=xlWhole)
         If Rg Is Nothing Then
            MsgBox "EMPLOYEE NUMBER DOES NOT EXIST PLEASE TRY AGAIN", vbExclamation: Exit Sub
         Else
            Set Rg = IIf(i = 1, ThisWorkbook.Sheets("Bid Data").Cells(, "C").Resize(500), ThisWorkbook.Sheets("Bid Data").Cells(, "D").Resize(, 500))
            If InStr(dDate, ".") Then
               For x = 0 To UBound(Split(dDate, "."))
               Next
               For x = 0 To UBound(Split(dDate, "."))
                  Rg.Find("", after:=Rg.Cells(Rg.Count)) = Split(dDate, ".")(x)
               Next
            Else
               Rg.Find("", after:=Rg.Cells(Rg.Count)) = dDate
            End If
         End If
      End If
   Next
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox1.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

 

[mse330]

Hi Ali,

Doesn't the below line do what you need

Set Rg = IIf(i = 1, ThisWorkbook.Sheets("Bid Data").Cells(, "C").Resize(500), ThisWorkbook.Sheets("Bid Data").Cells(, "D").Resize(, 500))