Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Choosing a workbook from a form (2003 XP)

    With much help I have created a nice Excel VB Userform interface that sits as an Excel workbook behind a protected, third party generated Excel worksheet. ( In practice you open the blank xls file with the macro in, then open the xls file with the data to work with, then run the macro and the Userform interface appears.) The problem is that teachers must close the Userform down (exit), open a different Excel file / workbook, and run the Userform macro again to use the nice interface. I would like a list of the Excel files in the same directory folder to be available to select and open from within the Userform, the Userform to remain on top and to be using the data in the new opened worksheet. Two added restrictions (1)-I don't know beforehand how many files will be in the folder, there could be 2 or 20. (2)-The name of the files cannot be changed or pre-set.

    It would also be good if teachers could open the first xls file, then open the file with the macro in. But what happens is the Userform sets all the list boxes to pre-set columns, which don't exist in the empty worksheet if that is the last sheet to open. What I could then do is have a macro that automatically opens the Userform when the worksheet is opened. I hope that bit made sense?

    Can anyone offer any suggestions please?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choosing a workbook from a form (2003 XP)

    It is very difficult to to follow what you are saying without being able to look at the workbook. If possible, it will make helping you much easier if you could upload a copy of the workbook.

    If I understand it, you want your VBA code to display a list of files for the user to choose from. If that is what you want, then the easiest way to do this is to use the GetOpenFilename method to display the normal file selection dialog box and let the user select a file.
    Legare Coleman

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

    Re: Choosing a workbook from a form (2003 XP)

    You can display the standard File Open dialog from the userform:
    - Put a command button cmdOpen on the userform.
    - Create the following On Click event procedure for the command button:

    Private Sub cmdOpen_Click()
    Dim fileToOpen As Variant
    fileToOpen = Application.GetOpenFilename
    If fileToOpen = False Then Exit Sub
    Workbooks.Open fileToOpen
    End Sub

    You could save the workbook with the macro as an Excel add-in (.xla), and create a custom toolbar with a button that opens the userform. See Jan Karel Pieterse's excellent series of articles Create Addins.

  4. #4
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choosing a workbook from a form (2003 XP)

    Many thanks for the replies. If I can get these two things added, then I've cracked it. (with some help of course)

  5. #5
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choosing a workbook from a form (2003 XP)

    Added the code Hans gave me and it worked fine. The Userform didn't recognise the newly opened worksheet so I had to insert an Unload command then a Load command at the end. It works OK now. I now need to get my head around the 'Create Addins' bit.

    If I have multiple worksheets (20), how would I get bits of data from selected worksheets to add to a calculation or a list. For example if I had 12 sheets, one for each year group of children. A worksheet with a list of teachers offering different activities on different evenings and a worksheet with a list of rooms to allocate. I want to link the three groups and create tables. How do I reference the different sheets?

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

    Re: Choosing a workbook from a form (2003 XP)

    You might wait to see if someone else posts a reply, but I think you'll have to give us more specific information, and/or post a sample workbook with a detailed explanation of what you want.

  7. #7
    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: Choosing a workbook from a form (2003 XP)

    I concur with Hans. I think we can help, we just need some details...

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choosing a workbook from a form (2003 XP)

    Without having your specific workbook, here is the basic procedure for linking data in one workbook to another:


    Open a new workbook and name it : test2.xls
    In Sheet1 B2:B5 enter:
    <pre> Apple
    Orange
    Banana
    </pre>



    Open a another new workbook and name it: test1.xls

    Method 1:

    Select any cell, say D2
    Type: =
    Click on the Window menu and select test2
    Select B2 and click on Enter
    You should now have a link for test1 to test2
    Note the format in the formula bar.

    Method 2 using vba code:
    In test1 create a code module and enter:

    <pre>Option Explicit

    Sub LinkTest()
    Workbooks("test1.xls").Sheets("Sheet1").Range("B5" ) = _
    Workbooks("test2.xls").Sheets("Sheet1").Range("B5" )
    End Sub
    </pre>


    Run this code and look at cell B5

    Hope this helps you get started.

  9. #9
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choosing a workbook from a form (2003 XP)

    Thanks for the suggestion Paul. I'll give it a go.

Posting Permissions

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