VBA Userform Vlookup and File open then print out
Results 1 to 3 of 3

Thread: VBA Userform Vlookup and File open then print out
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Location
    Surrey, UK
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Userform Vlookup and File open then print out

    Hi All,
    First of all thanks in advance for helping me on this. I have created a Userform that has a few simple elements in it.

    1. ComboBox1
    2. ComboBox2
    3. TextBox1
    4. Okay, clear and close buttons

    From ComboBox 1 you select the job role.
    From ComboBox 2 you select the relevant file you want printed. This changes depending on your entry in the above.
    In TextBox1, you input how many copies you want printed and then the idea is is that when you click okay, it opens the file and prints x copies of it.

    I have managed to get two ComboBoxes working, and made them dynamic, so that when a new file is entered into the Locations sheet, it updates the selections possible from the ComboBoxes. I did this using the name manager, arrays and the following code:

    Code:
    Private Sub UserForm_Initialize()
    'Populate Role combo box.
    Dim rngRole As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    For Each rngRole In ws.Range("Role")
    Me.ComboBox1.AddItem rngRole.Value
    Next rngRole
    End Sub
    
    
    Private Sub ComboBox1_Change()
    Dim xRg As Range
    Set xRg = Range(Me.ComboBox1.Text)
    Me.ComboBox2.List = Application.WorksheetFunction.Transpose(xRg)
    End Sub
    But from here I can't seem to figure out the rest. The Locations sheet looks kinda like this:

    Role (A1) Template (B1) Destination (C1)
    Reception Fire Safety Sheets c:\templates\reception\fire v4.xlsx
    Nurses PEG Chart c:\templates\Nurses\PEG chart.xlsx
    Maintenance Mini Bus Weekly Checklist C:\templates\maintenance\Mini Bus weekly checklist
    Reception Visitor Log Book c:\templates\reception\visitor log book.xlsx

    So using an array formula and the name manager, this information is transferred and re-organised alphabetically so that it looks right in the ComboBoxes into a sheet called Data.

    What I want is that when someone selects Reception in ComboBox1, then Fire Safety Sheets in ComboBox2, you can then type in 10 in TextBox1, click on the okay button and then it will open the corresponding file then print off 10 copies of it.

    Can anyone please help me?

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Location
    Surrey, UK
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Userform Vlookup and File open then print out

    Anybody able to help me with this please?

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Location
    Surrey, UK
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Userform Vlookup and File open then print out

    Or can anyone please just help me with the opening a file from the C column? I'll work out the print stuff. It's this bit that's really giving me a headache

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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