Userform textbox format when populated by listbox

[pure vito]

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

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

 

[rabsofty]

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

 

[pure vito]

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"

 

[rabsofty]

run time error 6 is an overflow error
check your text box maximum length property. make sure it can handle the date length.

 

[pure vito]

No still getting the same error changed the textbox properties and made them oversized on the form

 

[rabsofty]

i noticed you have numerical dates in your text boxes. eg: 45647 instead of dd/mm/yyyy

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

 

[pure vito]

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

 

[dmt32]

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

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

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

 

[pure vito]

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?

 

[dmt32]

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