Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userforms and worksheets (VBA/Excel2003)

    Hi all!

    I'm kind of new to VBA and am having problems with a userform in an Excel workbook. My form allows input in a multitabbed page and uses both direct referrences to cells and control sources to add the data to a temporary worksheet. This data is then exported to another sheet upon exiting. My problem is that certain textboxes will only find the correct data if the temporary sheet is active in the background (i.e. the sheet is selected when the VBA code is run). All the references are in the form:

    Workbooks("Workbook.xls").Worksheets("TempData").R ange("A1").Value

    so they should work no matter what sheet is active, right? I've narrowed the possible culprits down to this single box which is having problems:

    Crisp_Box.ControlSource = CrispList.Cells(CrispRow, Year).Address

    ... where CrispList is a range and CrispRow and Year are integers.

    Of course I could activate the TempData sheet, but I would prefer if it was not visible to users during the input process, as all forms are run through a main menu. Can someone help me out?

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

    Re: Userforms and worksheets (VBA/Excel2003)

    I would strongly advise NOT to tie controls on userforms to worksheet cells. These possibilities are designed for use of controls on worksheets, not for use in Userforms. Instead, you should read the values of cells, put their data on the appropriate controls using VBA in e.g. the Userform's Initialise event before showing the form and afterwards do the opposite.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userforms and worksheets (VBA/Excel2003)

    Thanks for your quick reply!

    The only problem with this is that i have a multipage form where the users can input numbers in percent on one page or alternatively as absolute numbers on the other. The easiest way to do this was to link the text boxes that change but do not control through control sources. I guess I'll just have to reorganize my update function and read straight from the cells. Thanks for your help!

Posting Permissions

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