User Form List

[Jak]

Hi All

I want to load a VB Modeless User Form and have the user select a cell on the spreadsheet and populate it with a heading from the UserForm. This will continue until all seven headings have been selected.

"Forename"
"Initial"
"Surname"
"DOB"
"Ethnicity"
"Gender"
"Centre Candidate ID."

I need some help on how to do this. I have added a listbox to my UserForm and I need to populate it with the above list. The list must be hard coded into the userform rather than populating the list from a spreadsheet.

The idea is to force the user to add the seven titles to a header row on a spreadsheet.

Any examples would be great.

 

[Smitty]

By allowing the user to randomly assign your headers aren't you leaving an awful lot up to chance with regards to continuity between users?

Why not pre-load the headers?

But to answer your question, you can use this with a form called "frmListBox":

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ListBox1_Click()
    ActiveCell.Value = ListBox1.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#00007F">With</SPAN> ListBox1
        .AddItem "Forename"
        .AddItem "Initial"
        .AddItem "Surname"
        .AddItem "DOB"
        .AddItem "Ethnicity"
        .AddItem "Gender"
        .AddItem "Centre Candidate ID."
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And this in a general module to call it:

<font face=Tahoma><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ShowForm()
    frmListBox.Show vbModeless
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Forcing the user to do it becomes another issue. Frankly, I think you're giving too many options...

HTH,

Smitty

 

[Jak]

Hi Smitty

Thanks for the code.

Just a quick explanation. The UserForm I am creating will be part of an Addin. At the moment my Form adds a header row with the titles I listed. Users then key in the data below the header row in the respective columns. However, some users dump data from an Access database into Excel. It has its own header row. In this scenario I want the user to change some of their headers to mine for export purposes. As there will be variation in the users header row titles and there positioning I wanted an easy way for the user to replace theirs with mine.

I am going to add some code to the close button on the UserForm to check the header row to ensure all 7 titles have been entered and if not prevent the Form from closing.

Thanks again for the code it has been a big help.

 

[Smitty]

Glad it helped, note that I didn't condense the AddItem part as it's more readable that way (for me) for small sets...

However, some users dump data from an Access database into Excel.

Could you automate that part with Excel's Data-->Import External Data function or SQL?

Smitty

 

[Jak]

Hi Smitty

The header row is temporary. The upload goes via a web site and for reasons best known to the developers they dont allow data to upload with a header row. So the header is used to help the user enter data into the right fields and order. The code you have provided will help with data dumps to Excel and I have some additional code to sort the columns thereafter into the right order.

Many thanks for the code and follow up.