Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ControlSource Reference (Excel 2000)

    I have a user form containing several Text Box and Combo Controls. I am trying to reference different spreadsheet cells to these controls based on a variable (UserRow). If I use a static text string things work fine.

    Ex: txtEmployeeNo.ControlSource = "A3"

    But If I try to introduce a source value based on the variable, I get a "Run-Time error '380': Could not set the ControlSource property. Invalid property value."

    Ex: txtEmployeeNo.ControlSource = "A" + Str$(UserRow)
    or
    Ex: VarRef = "A" + Str$(UserRow)
    txtEmployeeNo.ControlSource = VarRef

    Am I formatting the statement wrong?, The variable value is the same "A3" value that works in the first example.

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ControlSource Reference (Excel 2000)

    Not sure if this is it, but try

    txtEmployeeNo.ControlSource = "A" & Str$(UserRow)

    instead (ampersand instead of plus).
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ControlSource Reference (Excel 2000)

    Kevin,

    Thanks, but it's a "NoGo" (same Error)

    I tried [ txtEmployeeNo.ControlSource = "A" & Str$(UserRow) ]
    and
    VarRef = "A" & Str$(UserRow)
    txtEmployeeNo.ControlSource = VarRef

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ControlSource Reference (Excel 2000)

    Try

    ="A" & Trim(Str$(UserRow))

    or just

    = "A" & UserRow

    Using Str$ adds a leading placeholder for the sign(+/-) of the number.

    Len(Str$(100)) and Len(Str$(-100)) both return 4

    Andrew C

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ControlSource Reference (Excel 2000)

    Here's another "try this"...

    ...ControlSource = "'A" & Str$(UserRow)

    or as Andrew suggests

    ....ControlSource = "'A" & trim(Str$(UserRow))

    Note the single quote in front of the A.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ControlSource Reference (Excel 2000)

    You should Dim the variable userrow as Long:

    Dim Userrow as Long
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ControlSource Reference (Excel 2000) Solved

    Thanks much to all (Jan Karel Pieterse, Kevin, and especially Andrew Cronnolly).

    These DO work
    txtEmployeeNo.ControlSource ="A" & Trim(Str$(UserRow))
    txtEmployeeNo.ControlSource = "A" & UserRow

    These DO NOT work
    txtEmployeeNo.ControlSource = "A" & Str$(UserRow)
    txtEmployeeNo.ControlSource = "'A" & Str$(UserRow)
    txtEmployeeNo.ControlSource = "'A" & trim(Str$(UserRow))

    Andrew "hit the nail on the head" when he said "Using Str$ adds a leading placeholder for the sign(+/-) of the number". When i used an intermediate variable (VarRef), I could see a space between the column and row (ie "A 9"), but wasn't sure if it was really there or where it was coming from. Now I know

Posting Permissions

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