none
Textbox controlsource RRS feed

  • Question

  • using ms project 2007 and 2010

    is it possible to set the controlsource for a userform textbox to a cell in Excel? 

    example:  tbMyvalue is a textbox in my user form.  I want to assign its value to cell B1 in sheet1 from an Excel file.

    seems like the controlsource should be something like:  C:\mydata.xls, sheet1, B2

    That doesn't work, but I can't seem to find anything meaningful regarding how to effectively use the controlsource property in a user form.

    GMAN1941

    Monday, April 23, 2012 1:31 PM

Answers

  • John,

    Thanks for the input.    I wanted to know how to properly enter the necessary string into the Controlsource field of the textbox in the user form.  but maybe i don't need that.  maybe i simply set the excel object and then the textbox:

    tbMyvalue = s.range("b2")

    GMAN1941

    • Marked as answer by GMAN1941 Tuesday, April 24, 2012 2:19 PM
    Monday, April 23, 2012 6:18 PM
  • John's right, you can't set a source. You could link to a custom field for a task, but that is risky. DDE Links are notorious for corrupting files. For John's code to work you need to add a reference in teh Editor to Excel, or use:
    Dim xl as Object
    
    Set xl = GetObject(,"Excel.Application")
    s.Range("B2") = xl.ActiveWorksheet.Range("B1").value
    
    set xl=nothing
    For both versions the workbook needs to be open before the code is run, but you can change the code to open the file first provided the file's location is known.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    • Marked as answer by GMAN1941 Tuesday, April 24, 2012 2:19 PM
    Monday, April 23, 2012 9:31 PM
    Moderator

All replies

  • GMAN1941,

    First you need to get the Excel application object. Then I usually declare an object identifying the specific workbook and worksheet. From there it is easy and convenient to address individual objects/properties of that worksheet object. It is also advisable to declare your variables. Your best resource is to use the Object Browser from the VB Editor window. Oh, and don't forget to set a reference to the Excel Object library under Tools/References.

    Dim xl as Excel.Application

    Dim s as Worksheet

    Set xl = GetObject("C:\mydata.xls")

    set s = xl.Workbooks([name]).Worksheets(1)  'where [name] is a string of the workbook name (e.g. "MyWorkbook")

    s.Range("B2") = byMyvalue

    Hope this helps.

    John

    Monday, April 23, 2012 4:17 PM
  • John,

    Thanks for the input.    I wanted to know how to properly enter the necessary string into the Controlsource field of the textbox in the user form.  but maybe i don't need that.  maybe i simply set the excel object and then the textbox:

    tbMyvalue = s.range("b2")

    GMAN1941

    • Marked as answer by GMAN1941 Tuesday, April 24, 2012 2:19 PM
    Monday, April 23, 2012 6:18 PM
  • John's right, you can't set a source. You could link to a custom field for a task, but that is risky. DDE Links are notorious for corrupting files. For John's code to work you need to add a reference in teh Editor to Excel, or use:
    Dim xl as Object
    
    Set xl = GetObject(,"Excel.Application")
    s.Range("B2") = xl.ActiveWorksheet.Range("B1").value
    
    set xl=nothing
    For both versions the workbook needs to be open before the code is run, but you can change the code to open the file first provided the file's location is known.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    • Marked as answer by GMAN1941 Tuesday, April 24, 2012 2:19 PM
    Monday, April 23, 2012 9:31 PM
    Moderator
  • Rod,

    thanks for the input.  I have this part solved.  as an add-on question, is it possible to address a field by is user name?

    example:

    Dim jtask as task

    For Each jTask In ActiveSelection.Tasks

    jtask.IPTMgr = "Jones"  where IPTMgr is a user name for Text3

    Tuesday, April 24, 2012 2:57 PM
  • GMAN1941,

    Yes but not directly. Try the following syntax:

    jtask.getfield(application.FieldNameToFieldConstant("IPTMgr")) = "jones"

    John

    Tuesday, April 24, 2012 11:58 PM
  • John,

    exactly what  i need - however, i tried:  jtask.getfield(application.FieldNameToFieldConstant("IPTMgr")) = "jones"

    and i get an error message:

    "Function call in left-hand side of assignment must return variant or object"

    the code is:  (abreviated) - and  where Text21 is defined as IPTMgr

    dim Jtask as task

    for each Jtask in Activeselection.Tasks

                jtask.getfield(application.FieldNameToFieldConstant("IPTMgr")) = "jones"

    next Jtask

    am I missing something?

    GMAN1941        

    Wednesday, April 25, 2012 1:20 PM
  • John,

    disregard - i discovered the problem - the code should be "setfield" not "getfield"

    GMAN1941

    Wednesday, April 25, 2012 2:50 PM
  • GMAN1941,

    Yeah, my bad. Try the following:

    jtask.setfield fieldID:=application.FieldNameToFieldConstant("IPTMgr"), value:="joe"

    John

    Wednesday, April 25, 2012 3:04 PM
  • John,

    actually, it's:

    jtask.setfield fieldID:=application.FieldNameToFieldConstant("IPTMgr",pjTask), value:="joe"

    and you can substitue "IPTMgr" and "joe" with variables

    GMAN1941

    Wednesday, April 25, 2012 6:33 PM