Userform textbox format when populated by listbox

pure vito

Board Regular
Joined
Oct 7, 2021
Messages
183
Office Version
  1. 365
Platform
  1. Windows
Hi All,

The below code populates the text boxes within my userform by selecting a row in the listbox,

The only issue I am having with this is a number of the 32 textboxes contain dates that are being formatted into numbers rather than dd/mm/yyyy,

Is anyone able to apply this to the code below please I have tried myself to no avail,

Many Thanks


VBA Code:
Private Sub ListBox1_Click()
Dim i As Integer
'Loads textboxes with selected listbox values
For i = 1 To 32
Nameinput.Controls("TextBox" & i).Value = Nameinput.ListBox1.List(, i - 1)
Next i
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Code:
Private Sub ListBox1_Click()
Dim i As Integer
'Loads textboxes with selected listbox values
For i = 1 To 32
Nameinput.Controls("TextBox" & i).Value = Format(Nameinput.ListBox1.List(, i - 1),"dd/mm/yyyy")
Next i
End Sub

Try this. - I don't know what the data looks like in your listbox. I assume it is in the ,"dd/mm/yyyy" format
 
Upvote 0
Hey, I tried exactly this myself but unfortunately doesn't work 😔

I have a number of textboxes with normal text and some with dates,

Run time error 6

"Overflow"
 

Attachments

  • Captureqwrfqwfq.JPG
    Captureqwrfqwfq.JPG
    222.7 KB · Views: 13
Upvote 0
run time error 6 is an overflow error
check your text box maximum length property. make sure it can handle the date length.
 
Upvote 0
No still getting the same error changed the textbox properties and made them oversized on the form 🤔
 
Upvote 0
i noticed you have numerical dates in your text boxes. eg: 45647 instead of dd/mm/yyyy


Code:
For i = 1 To 32
  thedate = Nameinput.ListBox1.List(, i - 1)
  e1 = Year(thedate): e2=Month(thedate): :e3=Day(thedate)
  dte2ser = CLng(DateSerial(e1, e2, e3))
  Nameinput.Controls("TextBox" & i).Value = dte2ser
Next i

assumes Nameinput.ListBox1.List(, i - 1) is in valid date format
 
Upvote 0
Test1234555.xlsm

It may just be me but I'm struggling with this, I have added a few sheets from the document if you select sheet home and press add employee the userform will open

the code I have in there is just what I've been able to find online,

the is to be able to add employees, edit employees, delete employees and search, I'm not asking for much right 😅

If you can lend a hand here at all I'd be very grateful 😬
 
Upvote 0
the is to be able to add employees, edit employees, delete employees and search, I'm not asking for much right 😅

*Posted link to your test file looks like it contains real data? if this is the case suggest you break the link & only post test files that include dummy data*

Userforms can be hard work especially complex ones like yours as you have to write the code for all aspects of it & as you have discovered even relatively simple issues can be troublesome.


Having looked at your file you are using RowSource to populate the ListBox & to overcome your issue you can try the following changes & see if this resolves for you

1 - MOVE the declared variable shown in RED to the very TOP of your userforms code page OUTSIDE any procedure

Rich (BB code):
Private Sub UserForm_Activate()
Dim sh As Worksheet ' < Move

Set sh = ThisWorkbook.Worksheets("People")

'rest of code

2 - Make change to setting object variable as shown in Blue

3 - See if this update to your code will now do what you want

VBA Code:
Private Sub ListBox1_Click()
    Dim Col         As Long, SelectedRow As Long
    
    SelectedRow = Me.ListBox1.ListIndex + 2
    
    'Loads textboxes with selected range values
    For Col = 1 To 32
        Me.Controls("TextBox" & Col).Value = sh.Cells(SelectedRow, Col).Text
    Next Col
End Sub

Personally, I would suggest that you consider developing your project using an appropriate Database Management System (DBMS) like Access.

Hope Helpful

Dave
 
Upvote 0
Solution
Test1234555.xlsm

Hi dmt,

That was an oversight thank you for bringing this up, I have made changes to the file,

Your code works perfectly simply moving the declared variable made all the difference these are the kind of things I have no idea about but I'm trying,

This is more of a project than a working document but your suggestion looks good but I fear it would be another huge undertaking trying to learn the how to use it, I mostly work with excel so doing this on excel does help me even if I'm asking others for help,

Thanks for taking the time it's very much appreciated I don't suppose you have a suggestion for editing a selected row? 😬
 
Last edited:
Upvote 0
Your code works perfectly simply moving the declared variable made all the difference these are the kind of things I have no idea about but I'm trying,

Glad update to your project resolved the issue for you
This is more of a project than a working document but your suggestion looks good but I fear it would be another huge undertaking trying to learn the how to use it, I mostly work with excel so doing this on excel does help me even if I'm asking others for help,

Yes learning curve for DBMS is a bit steeper but upside is there can be much less coding required.
Thanks for taking the time it's very much appreciated I don't suppose you have a suggestion for editing a selected row? 😬

Its a different question & probably best posted in a new thread

Dave
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

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