Would like to display data using Userform to select line from a table

[marilynstephan]

Am trying to create a macro that will launch a user form, allow the user to select a value from column A and then have the values on that line displayed.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
My simple table looks like this. The top line is a heading<o></o>
<o></o>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -0.75pt; WIDTH: 721pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=961 border=0><TBODY><TR style="HEIGHT: 18.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 247pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=329>Specialty<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 232pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=309>Words<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 242pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=323>Words2<o></o>

</TD></TR><TR style="HEIGHT: 18.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 247pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=329>Allergy and Immunology<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 232pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=309>allergy jobs<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 242pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=323>immunology jobs<o></o>

</TD></TR><TR style="HEIGHT: 18.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 247pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=329>Anesthesia Assistant<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 232pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=309>anesthesia assistant jobs<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 242pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=323>anesthesia assistant job<o></o>

</TD></TR><TR style="HEIGHT: 18.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 247pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=329>CRNA<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 232pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=309>Certified registered Nurse Anesthetist<o></o>

</TD><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 242pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=323>Nurse Anesthetist<o></o>

</TD></TR></TBODY></TABLE><o></o>
I would like the users to be able to select a specialty from a drop down box (filled with the items in column A “Specialty") and then have a screen in front of them display the Specialty with the other words from the cells to the right of the Specialty chosen, (I'd like those words displayed vertically under the specialty.()
<o></o>
Can you help me please. My table is a large one with 175 Lines (specialties) and 15 columns to the the right on the specialty column (total of 16 Columns of Words, not all of them filled).<o></o>
<o></o>
Actually I defined the range of the table, used the “form” command, and using the criteria button, got exactly what I wanted. Unfortunately, my users are not saavy enough to use the forms command and then the criteria button.

Any ideas?<o></o>

 

[CEG]

Here is what I did...
First, I made a named range called "Specialty", which included the cells that the 3 specialties you listed are in. Then I made a userform with one combobox and 2 textboxes underneath it. I clicked the combobox in the code window and in the properties on the left hand side of the code window, under RowSource, I put =Sheet1!Specialty. This fills in the combo box. Then I created a module in the code window and put the following code, which will show the userform:

Sub ShowForm()
UserForm1.Show
End Sub

Then I double clicked on the combobox in the code window and, making sure the "Change" event was set in the upper right hand corner, I added these lines of code:

Private Sub ComboBox1_Change()
    Label1 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Sheet1").Range("A:B"), 2, 1)
    Label2 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Sheet1").Range("A:C"), 3, 1)
End Sub

I think that is it. Just run the macro then.

 

[marilynstephan]

Thanks so much. I'm going to try this this morning.

I really appreciate your help and will let you know how it goes!

Best,

Marilyn

 

[marilynstephan]

Still having trouble - Here's my code

Hi,

I'm still having trouble.
Worksheet is named "Keyword"
Range is named "IndexedKeyword"

The combo box is allowing a Specialty in Column A to be selected,
but my text boxes are not populating with the rest of the line (to the right of the ComboBox value.

Here's my code:

Private Sub UserForm_Click()
Sub ShowForm()
UserForm2.Show
End Sub


Private Sub ComboBox1_Change()
Label1 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:B"), 2, 1)
Label2 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:C"), 3, 1)
Label3 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:D"), 4, 1)
Label4 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:E"), 5, 1)
Label5 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:F"), 6, 1)
Label6 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:G"), 7, 1)
Label7 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:H"), 8, 1)
Label8 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:I"), 9, 1)
Label9 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:J"), 10, 1)
Label10 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:K"), 11, 1)
Label11 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:L"), 12, 1)
Label12 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:M"), 13, 1)
Label13 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Keyword").Range("A:N"), 14, 1)
End Sub

 

[CEG]

How are you bringing up your userform? You have a "Click" event...

Private Sub UserForm_Click()
Sub ShowForm()
UserForm2.Show
End Sub

First, you don't need both of these

Private Sub UserForm_Click()
Sub ShowForm()

Can anyone else on the forum put their 2 cents in?

 

[marilynstephan]

Figured it out!!! It's Working!

Hi CEG,

I figured it out!!!

I just replaced Label with TextBox in the code. Works like a charm.

Thanks so much!

Marilyn