User Form List

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
811
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Apr 5, 2002
Messages
811
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Apr 5, 2002
Messages
811
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. :biggrin:
 

Forum statistics

Threads
1,136,348
Messages
5,675,238
Members
419,555
Latest member
Paddington

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
Top