Userform to enter data in location based on value

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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)

1596223294066.png


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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
Hi Haji Ali,

Try the below code, I have used the default names of TextBoxes & ComboBoxes ... I have also uploaded the sample file I created HERE

VBA Code:
Private Sub CommandButton2_Click()

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

If TextBox1.Value = "" Or TextBox2.Value = "" Or ComboBox1.Value = "" Then
   MsgBox "Please fill-in all 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")
   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(ComboBox1.Value = "DAT", .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 With

Unload Me

End Sub
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
Hi Haji Ali,

Try the below code, I have used the default names of TextBoxes & ComboBoxes ... I have also uploaded the sample file I created HERE

VBA Code:
Private Sub CommandButton2_Click()

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

If TextBox1.Value = "" Or TextBox2.Value = "" Or ComboBox1.Value = "" Then
   MsgBox "Please fill-in all 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")
   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(ComboBox1.Value = "DAT", .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 With

Unload Me

End Sub

Thank you so much mse330 this works great.

just a few more adjustments:
When enter is pressed instead of it userform unload me to clear the values enter and have textbox1 be active.
and since it take a bit to input the data can it hold off it the data in inputted before textbox1 is active
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
also when pressing enter on the keyboard it goes to next field after date field it goes to
CommandButton1 for done as default. is there a way i can change that to CommandButton2
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you so much mse330 this works great.

just a few more adjustments:
When enter is pressed instead of it userform unload me to clear the values enter and have textbox1 be active.
and since it take a bit to input the data can it hold off it the data in inputted before textbox1 is active

I got this accomplished just need post #4 that would be all I need
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
I got this accomplished just need post #4 that would be all I need

Good morning Ali,

You can change the enter/tab order of the UserForm, in VBE editor, right click on the UserForm then click on Tab Order & from there you can change the order as desired :)
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for that info. If I wish to get rid of the Combobox1 and make that into a Textbox3 for a field to enter FTO dates and change textbox2 to enter dates for DAT. only have one of those required fields required with textbox1 for the enter to work. So instead of selecting DAT or FTO each of them have a textbox keeping everything the same. How would I recode
VBA Code:
If TextBox1.Value = "" Or TextBox2.Value = "" Or ComboBox1.Value = "" Then
   MsgBox "Please fill-in all feilds to proceed ... ", vbExclamation: Exit Sub

and

Code:
Else
      Set Rg = IIf(ComboBox1.Value = "DAT", .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)
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
So would you enter dates for FTO & DAT simultaneously or only one at a time ?
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
So would you enter dates for FTO & DAT simultaneously or only one at a time ?
Yes at times it would be simultaneously. At times just DAT and not FTO, other times FTO and not DAT so at least DAT or FTO field required with Employee number.
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
Ok, this is a updated code based on your revised request. I have updated the same file as well HERE

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,867
Members
410,711
Latest member
Josh324
Top