Using a Combo Box in Excel to open new workbooks? 1

[Prion]

I want to use a combo box in an Excel spreadsheet to open a new workbook without going to File, Open etc. Each workbook contains a budget of different projects, I use different sheets in each workbook to enter specific data on purchases, which automatically updates a balance sheet on the first page of each workbook. The combo box should therefore contain a reference code to each project. When the project is selected in the combo box it should save the current workbook(project budget), and open the selected project. Any help would be appreciated!

 

[LoNeRaVeR]

Prion:

The easiest way to do this would be to pull the filenames from a list in another worksheet within the same workbook. The following example uses a Combo Box with the list of files in an Input Range in column C and the Cell Link in cell A1. Copy and paste the following Macro into a Module within your workbook.

Sub OpenFile()
Dim iValue As Integer
Dim sDir As String
Dim sFile As String
sDir = "C:\Windows\Desktop\"
iValue = Range("A1&quot.Value
sFile = Cells(iValue, 3)
Workbooks.Open FileName:=sDir & sFile
End Sub

Change the variable sDir to the directory where your files are located. Change the variable iValue to point where your Cell Link is within your Combo Box. Change the Column reference, the 3 in sFile to the column where your list of files is located. Assign this macro to your Combo Box.

Please let me know if you would like me to E-mail an example to you.

Regards,

LoNeRaVeR

 

[Prion]

Hi LoNeRaVeR,

Thanks for the info, unfortunately I still have a problem. Could you please Email me the example?

Thanks!

 

[LoNeRaVeR]

Prion:

I apologize for my delayed response. Please go to this web site and you can download the file.

http://www.geocities.com/loneraver9/excel/

Once you have the file, create a few files called Book1.xls, Book2.xls, Book3.xls in the directory

C:\Windows\Desktop
The Combo Box will open these files. You can edit the code to change the directory and change the file names in the spreadsheet to open different files. Regards,

LoNeRaVeR