Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cell to variable to cell (Excel 2003 VBA)

    I am creating a form that will pull certain values from a template spreadsheet and place them in summary sheet. However, I am having problems getting the values into variables so that I can enter back on to the summary sheet.
    There are multiple values in the template sheet contained in cells, text boxes, and combo boxes.
    How do I set a variable to equal these?
    Note: the template is being opened by 'set wbk = workbooks.open(vFileName)'

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    For cells you can do something like this:

    MyVariable = wbk.Worksheets("Sheet1").Range("B37")

    What kind of text boxes do you have? From the Drawing toolbar or from the Control Toolbox?
    What kind of combo boxes do you have? From the Forms toolbar or from the Control Toolbox?

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    The controls are all made from the control toolbox. They're named "text box 18" or "drop down 52"

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    Also, once I've grabbed the files, how do I change the activesheet back to the summary sheet?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    Are you sure? The name of controls from the Control Toolbox cannot contain spaces.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    Good point. The text boxes must have been made using the drawing toolbar.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    For a text box, you can use something like

    MyVariable = wbk.Worksheets("Sheet1").Shapes("Text Box 18").TextFrame.Characters.Text

    For a combo box named ComboBox1 from the Control Toolbox, you can use

    MyVariable = wbk.Worksheets("Sheet1").ComboBox1.Text

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    The combo boxes are named "Drop Down 49", "Drop Down 52"... so I'm not sure how they were created.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell to variable to cell (Excel 2003 VBA)

    Probably from the Forms toolbar. Use:

    MyVariable = wbk.Worksheets("Sheet1").Shapes("Drop Down 49").ControlFormat.Value

    Note: this will be the index of the selected item (1, 2, 3, ...). You'll have to inspect the list fill range to get the text of the selected item.

Posting Permissions

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