Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TextBox not updating (2K/XP)

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: TextBox not updating (2K/XP)

    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

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox not updating (2K/XP)

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: TextBox not updating (2K/XP)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •