VBA Userform Vlookup and File open then print out

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
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)
ReceptionFire Safety Sheetsc:\templates\reception\fire v4.xlsx
NursesPEG Chartc:\templates\Nurses\PEG chart.xlsx
MaintenanceMini Bus Weekly ChecklistC:\templates\maintenance\Mini Bus weekly checklist
ReceptionVisitor Log Bookc:\templates\reception\visitor log book.xlsx

<tbody>
</tbody>

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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top