Userform textboxs autofill

K

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I have a UserForm2 with 3 textbox columns.
'Claim Check, City, Employee Int'.
Is there a way to make the City and Employee textboxes auto populate with the City and Employee that was entered right above it?
Claim Check textbox (Name) starts at c1 the one below it is c2 and so on. City textbox is s1 the one below it is s2 and so on and Employee is e1 and below is e2.

I attached a photo of an example of what I would like to see when I enter the first set of data.

Just trying to make this a faster data entry. Claim checks will always change but city and Employee will only change a bit.
 

Attachments

  • 20210206_182150.jpg
    20210206_182150.jpg
    132 KB · Views: 41
Sort by date Sort by votes
Zot said:
My understanding is you are talking about after data entries completed. Data is stored in rows in this sequence

Range E5, F5, G5, H5, I5, J5, and so on
Claim, City, Employee, Claim, City, Employee, and so on

Each row will have unknown column filled depends on date? So, you want to count how many Claim, City and Employee for how many total of rows?
Click to expand...
Sorry fell asleep. Ya that was a bit hard to read and decipher.

I wish I could post the worksheet but the company security programs won't let me the only thing I can do is take a picture of the screen with my phone.

Yes the data on Sheet1 is laid out and labeled as follows, A Column is Date, B Column is Truck Number, C Column is reserved for the count of claim checks recorded in that row, D Column is On or Off. E Column is Claim check 1, F Column is City, G Column is Employee, H Column is Claim check 2, I Column is City, J Column is Employee and so on all the way to NZ Column or something like that. Each claim check will always have a City and employee next to it. So I was trying to think of some vba code or formula that would count only the claim checks in a range like E5 to NZ5.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Kingchaos64 said:
Sorry fell asleep. Ya that was a bit hard to read and decipher.

I wish I could post the worksheet but the company security programs won't let me the only thing I can do is take a picture of the screen with my phone.

Yes the data on Sheet1 is laid out and labeled as follows, A Column is Date, B Column is Truck Number, C Column is reserved for the count of claim checks recorded in that row, D Column is On or Off. E Column is Claim check 1, F Column is City, G Column is Employee, H Column is Claim check 2, I Column is City, J Column is Employee and so on all the way to NZ Column or something like that. Each claim check will always have a City and employee next to it. So I was trying to think of some vba code or formula that would count only the claim checks in a range like E5 to NZ5.
Click to expand...
You can just count how my column from E to NZ and then divide by 3. Since Claim, City and Emp in group of three, the answer would be the count.... right?

Something like Range("E5", "NZ5").Count/3. YOu should get round number
 
Upvote 0
@Kingchaos64
In case you can't open the file, here's the code:
VBA Code: 
Private Sub CommandButton1_Click()
'ENTER
Dim n As Long
ListBox1.AddItem
n = ListBox1.ListCount
For i = 1 To 3
    ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value
Next
TextBox1.Value = Empty
End Sub

Private Sub CommandButton2_Click()
Dim i As Long, j As Long, k As Long, a As Long, b As Long
Dim va
'FINISH
a = ListBox1.ListCount
b = ListBox1.ColumnCount
ReDim va(1 To 1, 1 To a * b)

    For j = 0 To a - 1
        For k = 0 To b - 1
            i = i + 1
            va(1, i) = ListBox1.List(j, k)
        Next
    Next

Range("A1").Resize(1, UBound(va, 2)) = va
End Sub

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "80,80,80"
End Sub
 
Upvote 0
Akuini said:
@Kingchaos64
In case you can't open the file, here's the code:
VBA Code: 
Private Sub CommandButton1_Click()
'ENTER
Dim n As Long
ListBox1.AddItem
n = ListBox1.ListCount
For i = 1 To 3
    ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value
Next
TextBox1.Value = Empty
End Sub

Private Sub CommandButton2_Click()
Dim i As Long, j As Long, k As Long, a As Long, b As Long
Dim va
'FINISH
a = ListBox1.ListCount
b = ListBox1.ColumnCount
ReDim va(1 To 1, 1 To a * b)

    For j = 0 To a - 1
        For k = 0 To b - 1
            i = i + 1
            va(1, i) = ListBox1.List(j, k)
        Next
    Next

Range("A1").Resize(1, UBound(va, 2)) = va
End Sub

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "80,80,80"
End Sub
Click to expand...
Thank you
 
Upvote 0
Akuini said:
@Kingchaos64
In case you can't open the file, here's the code:
VBA Code: 
Private Sub CommandButton1_Click()
'ENTER
Dim n As Long
ListBox1.AddItem
n = ListBox1.ListCount
For i = 1 To 3
    ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value
Next
TextBox1.Value = Empty
End Sub

Private Sub CommandButton2_Click()
Dim i As Long, j As Long, k As Long, a As Long, b As Long
Dim va
'FINISH
a = ListBox1.ListCount
b = ListBox1.ColumnCount
ReDim va(1 To 1, 1 To a * b)

    For j = 0 To a - 1
        For k = 0 To b - 1
            i = i + 1
            va(1, i) = ListBox1.List(j, k)
        Next
    Next

Range("A1").Resize(1, UBound(va, 2)) = va
End Sub

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "80,80,80"
End Sub
Click to expand...

I got it to work in just one row through. How do I get it to move to a new row after I click on the finish button? All it does now is replace what is in row 5. I tried working it out myself and added a couple lines of code but it's not working.
Below is the altered code.

Private Sub CommandButton1_Click()



'ENTER

Dim n As Long

ListBox1.AddItem

n = ListBox1.ListCount

For i = 1 To 3

ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value

Next

TextBox1.Value = Empty



Me.TextBox1.SetFocus





End Sub



Private Sub CommandButton2_Click()



Dim iRow As Long

Dim ws As Worksheet

Dim i As Long, j As Long, k As Long, a As Long, b As Long

Dim va



Set ws = Worksheets("Sheet1")



iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row



'FINISH

a = ListBox1.ListCount

b = ListBox1.ColumnCount

ReDim va(1 To 1, 1 To a * b)



For j = 0 To a - 1

For k = 0 To b - 1

i = i + 1

va(1, i) = ListBox1.List(j, k)

Next

Next



Range("E5").Resize(1, UBound(va, 2)) = va



Rows(iRow).Insert



'Date,Truck,On

ws.Cells(iRow, 1).Value = Me.TextBox4.Value

ws.Cells(iRow, 2).Value = Me.TextBox5.Value

ws.Cells(iRow, 4).Value = Me.TextBox6.Value



Unload UserForm4



End Sub





Private Sub UserForm_Initialize()



ListBox1.ColumnCount = 3

ListBox1.ColumnWidths = "60,60,60"







End Sub
 
Upvote 0
Your statement
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

is looking for last row in column A but you are looking for last row in column E
Range("E5").Resize(1, UBound(va, 2)) = va

So I think it should be changed to
iRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
 
Upvote 0
Zot said:
Your statement
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

is looking for last row in column A but you are looking for last row in column E
Range("E5").Resize(1, UBound(va, 2)) = va

So I think it should be changed to
iRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
Click to expand...
Darn that still doesn't work. It's frustrating sometimes because it's probably some simple tweak.
I think the
Range("E5").Resize(1, UBound(va, 2)) = va
Might need something. Maybe it needs to start in the next available E row and not be specified as E5. Because I can get the new row added but all the new data from the List box goes to E5 and replaces the old data there instead of entering it in the new row starting at the E Column.
 
Upvote 0
I thought you have modified everything. My mistake, I thought everything started from E5 but your data starts from A5.

I believe your starting iRow is 5 but I haven seen where it is started. The
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
will get to last occupied row. It is like pressing Ctrl+ArrowUp key form last spreadsheet row. So, the next empty row is
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

Yes that
Range("E5").Resize(1, UBound(va, 2)) = va
will always write at E5. You need to replace the row 5 as variable iRow like
Range("E" & iRow).Resize(1, UBound(va, 2)) = va

I have no idea but probably you already have something put in A5 before. Did you get the Date, Truck correctly in A5, B5 etc? Otherwise probably you need something to make sure data starts at row 5 as initial point, like
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
If iRow < 5 then iRow = 5

You don't need this line since you already at blank new line
Rows(iRow).Insert

Here is what it looks like. I hope this works. Oh... next time click that VBA icon to paste your code to make it more readable. ;)

VBA Code: 
Private Sub CommandButton1_Click()

'ENTER
Dim n As Long
ListBox1.AddItem
n = ListBox1.ListCount
For i = 1 To 3
    ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value
Next
TextBox1.Value = Empty
Me.TextBox1.SetFocus

End Sub

Private Sub CommandButton2_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim i As Long, j As Long, k As Long, a As Long, b As Long
Dim va
Set ws = Worksheets("Sheet1")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
If iRow < 5 Then iRow = 5

'FINISH
a = ListBox1.ListCount
b = ListBox1.ColumnCount
ReDim va(1 To 1, 1 To a * b)

For j = 0 To a - 1
    For k = 0 To b - 1
        i = i + 1
        va(1, i) = ListBox1.List(j, k)
    Next
Next
Range("E" & iRow).Resize(1, UBound(va, 2)) = va

'Date,Truck,On
ws.Cells(iRow, 1).Value = Me.TextBox4.Value
ws.Cells(iRow, 2).Value = Me.TextBox5.Value
ws.Cells(iRow, 4).Value = Me.TextBox6.Value
Unload UserForm4

End Sub

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "60,60,60"

End Sub
 
Upvote 0
Solution
Zot said:
I thought you have modified everything. My mistake, I thought everything started from E5 but your data starts from A5.

I believe your starting iRow is 5 but I haven seen where it is started. The
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
will get to last occupied row. It is like pressing Ctrl+ArrowUp key form last spreadsheet row. So, the next empty row is
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

Yes that
Range("E5").Resize(1, UBound(va, 2)) = va
will always write at E5. You need to replace the row 5 as variable iRow like
Range("E" & iRow).Resize(1, UBound(va, 2)) = va

I have no idea but probably you already have something put in A5 before. Did you get the Date, Truck correctly in A5, B5 etc? Otherwise probably you need something to make sure data starts at row 5 as initial point, like
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
If iRow < 5 then iRow = 5

You don't need this line since you already at blank new line
Rows(iRow).Insert

Here is what it looks like. I hope this works. Oh... next time click that VBA icon to paste your code to make it more readable. ;)

VBA Code: 
Private Sub CommandButton1_Click()

'ENTER
Dim n As Long
ListBox1.AddItem
n = ListBox1.ListCount
For i = 1 To 3
    ListBox1.List(n - 1, i - 1) = Me.Controls("Textbox" & i).Value
Next
TextBox1.Value = Empty
Me.TextBox1.SetFocus

End Sub

Private Sub CommandButton2_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim i As Long, j As Long, k As Long, a As Long, b As Long
Dim va
Set ws = Worksheets("Sheet1")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
If iRow < 5 Then iRow = 5

'FINISH
a = ListBox1.ListCount
b = ListBox1.ColumnCount
ReDim va(1 To 1, 1 To a * b)

For j = 0 To a - 1
    For k = 0 To b - 1
        i = i + 1
        va(1, i) = ListBox1.List(j, k)
    Next
Next
Range("E" & iRow).Resize(1, UBound(va, 2)) = va

'Date,Truck,On
ws.Cells(iRow, 1).Value = Me.TextBox4.Value
ws.Cells(iRow, 2).Value = Me.TextBox5.Value
ws.Cells(iRow, 4).Value = Me.TextBox6.Value
Unload UserForm4

End Sub

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "60,60,60"

End Sub
Click to expand...
Wow this works great just what I wanted and pictured in my head. You don't know how much I appreciate you 2. This will save so much time and headaches. Thank you thank you.
 
Upvote 0
I'm glad it works.:)
But there's something else you might need.
When you add data to the listbox, is it possible that you've added wrong data so you need to delete or modify it before you send it to the sheet?
If yes then we need another code to deal with that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Userform textbox launches another userform
Replies
8
Views
339
FedElecQaEng
F
S
  • Solved
Help Required With Barcode Being Read Into A Userform...
Replies
3
Views
806
NoSparks
N
P
  • Solved
Checking Textbox For Specific Text Entry
Replies
4
Views
192
Pacman52
P
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
474
pure vito
P
R
  • Solved
validate textbox value in Userform
Replies
3
Views
145
dmt32
D

Forum statistics

Threads
1,214,566
Messages
6,120,266
Members
448,953
Latest member
Dutchie_1

Share this page

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