Userform ActiveCell question

[Noz2k]

What I want to know is how to have a variable activecell?

So I already have a table which is populated by a userform. However once this initial data is input a follow up form has to be completed at a later date.

So what I am trying to do is make it so that the follow up form sends data to the correct row in the table.

e.g. To keep it simple if my table is like (assuming "Name" is in A1)

Name StartDate EndDate
James 23/01/09
Brad 28/04/09

with my first form posting Name and StartDate, and then the follow up form posting EndDate. How would I make it so that the form posts for Brad (in C3) rather than James (in C2)?

 

[Noz2k]

So as I'm unaware of how to do this. I've decided to create a unique id field at the front of my table, and then get the user to input this at the beginning of the form.

That way I should be able to reference any cells that the textboxes send data to in the table, relative to where the unique id value can be found in the first column shouldn't I.

My idea is to use a spin button for this (not that I've ever used them before), so that the user can scroll through id's in the table. Should that work?

 

[Norie]

How about a combobox or a listbox?

A combobox could list some data that will identify each record and allow the user to select the one they are interested in.

When they make a selection some textboxes could be used to show the rest of the record and allow the user to change it if required.

With a listbox you could do something similar, it would just look a bit different - for one thing it would show multiple records.

A spin button could work but it might take a bit more effort to get it to work properly.

 

[Noz2k]

Thanks Norie, you really are helpful to me on here.

The concern I have with using a combobox is that over time there could be 1000's of records in the table, so the dropdown would be huge. By using the spin control in unison with a textbox the user can also just type in the id, and this would then hopefully link to the appropriate row in the table.

I have this code which gives me the unique id

Dim SBmax As Long
SBmax = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
SpinButton1.Min = 1
SpinButton1.Max = SBmax - 2
SpinButton1.Value = 1

I just now need to work out the code, to find the row number where the unique id value in the textbox is equal to a cell value in columnA on the worksheet. And then use that row number to post textbox2.value to columnD (for instance)

 

[Norie]

You can set up a combobox to match entries as the user types.

The user could also just type the id in rather than selecting it from the list or spinning the button until they find it.

You can also check if what the user enters is on the list.

If it's not on the list you can tell them, or perhaps tell them and give them the option to add a new record.

 

[Noz2k]

Yeah, to be honest. Just typing it in would probably be fine.

How do I lookup the row number for when the value matches a cell in column A though?

 

[Norie]

It really depends on the data and how you've populated the the combobox.

If your ids where in A2:A20 and you populated the combobox using that range then the ListIndex of the selected item would correspond to the row number.

For example if you picked, from the combobox, the 4th item in the list the ListIndex would be 3.

To get the row number on the worksheet just add 2.

Probably not a good explanation, see if this helps.

1 Create a list like this starting in A1 on a worksheet called Data.

<TABLE style="WIDTH: 105pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=140><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl65 height=17 width=64>IDS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 57pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl65 width=76>FirstField</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data16</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data17</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #4f81bd 0.5pt solid; BORDER-LEFT: #4f81bd 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=17>ID19</TD><TD style="BORDER-BOTTOM: #4f81bd 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #4f81bd 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4f81bd 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Data19</TD></TR></TBODY></TABLE>

2 Now create a userform, add a combobox and a textbox.

3 Add this code to the userform module.

Option Explicit
 
Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex <> -1 Then
        TextBox1.Value = Range("A" & ComboBox1.ListIndex + 2).Offset(, 1)
    End If
End Sub
 
Private Sub UserForm_Initialize()
    ComboBox1.List = Worksheets("Data").Range("A2:A20").Value    
End Sub

Now when you run the userform and select an ID from the combobox the corresponding data from the next column should appear in the textbox.

 

[Noz2k]

That all works great, thanks you very much. Had to change a bit to get exactly what I want, but it helps me to learn it that way.

Thanks very much

 

[Noz2k]

I'm trying to do a similar thing to this but within an excel sheet rather than a userform. I'm using this code

Sub Autofill()
    ComboBox1.List = Worksheets("Data").Range("A2:A20").Value    
End Sub

But it doesn't seem to work. It doesn't give me the .List option. Any idea why this would be?

 

[Norie]

Well it could be because there isn't a List property, but that would depend on what kind of combobox you've added to the worksheet.

Is it from the Forms toolbar or the Control (ActiveX) toolbar?

By the way, if you are going to do something on a worksheet you might want to look at using Data>Validation...