Userform to enter data in location based on value

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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)
 
Upvote 0
So would you enter dates for FTO & DAT simultaneously or only one at a time ?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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