TextBox not updating (2K/XP)

[bandido]

On a userform, I have a combobox whose row source is a list of numbers and two text boxes. The number selected is inserted into a cell on a worksheet that serves as a VLOOKUP reference. This works fine. I need the looked up values to become the values of TextBox1 and TextBox2 but when I run the macro, the textboxes remain blank. I have done something similar with textboxes many times before without problems but I obviously cannot see the wood for the trees.
The code I am trying to use is this:

Sheets("Customer").Select
Dim customerID As Range
Set customerID = Worksheets("Interface").Range("D8")
num = ActiveCell.CurrentRegion.Rows.Count
Names.Add Name:="customer", RefersTo:=Sheets("Customer").Range("$a$2:$h$" & num)
Names.Add Name:="CID", RefersTo:=Sheets("Customer").Range("$a$2:$a$" & num)
Dim ComboBox1 As ComboBox
Set ComboBox1 = UserForm1.ComboBox1
customerID = ComboBox1
TextBox1 = Sheets("Interface").Range("E7")
TextBox2 = Sheets("Interface").Range("F7")
End Sub

When I scroll through the macro, it reports the correct values. Any ideas?

[sdckapr]

Where is the code running from? (you do NOT have the initial SUB listed)

If it is in activation of a form it works fine placing the contents of Interface!E7 and F7 in TextBox1 and TextBox2

If it is in normal module, it assumes that Textbox1 and Textbox2 are variables and it won't run unless they are DIMed (if you are using Option explicit)

One other comment, I am not crazy about using ACTIVECELL in this context. What if the macro is run when the user has selected a region NOT part the data you wish to get the current region of.

Better to use something like:
num = Sheets("Customer").Range("a1").CurrentRegion.Rows. Count

and NOT even SELECT the "customer sheet". I try to avoid SELECTING or ACTIVATING during macro execution as it slows the routine down.

Steve

[bandido]

Thanks Steve. I managed to sort it out prior to reading your post. It is in a normal module and the following code now works fine:

Sub findrecord()
Sheets("Customer").Select
Range("A1").Select
Dim customerID As Range
Dim ComboBox1 As ComboBox
Set ComboBox1 = UserForm1.ComboBox1
Set customerID = Worksheets("Interface").Range("D8")
Set TextBox1 = UserForm1.TextBox1
Set TextBox2 = UserForm1.TextBox2
num = ActiveCell.CurrentRegion.Rows.Count
Names.Add Name:="customer", RefersTo:=Sheets("Customer").Range("$a$2:$h$" & num)
Names.Add Name:="CID", RefersTo:=Sheets("Customer").Range("$a$2:$a$" & num)
customerID = ComboBox1
TextBox1.Value = Sheets("Interface").Range("E7").Value
TextBox2.Value = Sheets("Interface").Range("F7").Value
End Sub

I will modify it further, in light of your advice regarding ACTIVECELL.

[sdckapr]

I assume you don't have option explicit on.

If you had, excel would have told you the VARIABLES Textbox1 and Textbox2 are not declared. This would have told you something was wrong!

I recommend option explicit, it saves a lot of headaches in tracking down problems.

Steve