Drop Down Data on external Excel Sheet

Can the form not be in the same workbook?

Then you could have anumber of dynamic range names to feed the drop down boxes.

Thanks
Rob H

Form.xlsm
Data.xlsx

Sorry with some very minor adjustments the two above attached documents should do what you want.

-SA

robhenson:
Unfortunately the data will be updated and the data will be based in a central location.

ScriptAddict:
I don’t seem to be able to get the form working. I've amended the path in the VBA code and I don’t get the sheet names to display.

Thank you
G.

Are macro's enabled on your PC?  I tested it before posting, and it was working perfectly on my PC.

Path is the directory of the data.lxsx file.  File name shouldn't be part of that path.

I'm assuming that the file name is data.xlsx.

It is currently set to refresh when you toggle to the form worksheet.

If you add this code it should also refresh when the dropdown button is clicked.

Private Sub ComboBox1_DropButtonClick()
Dim ws As Worksheet
Dim Path As String
Dim File As String
File = "Data.xlsx"
Path = "J:\John\Experts Exchange"
'   Make sure the file exists
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Dir(Path & File) = "" Then
        MsgBox "File Not Found"
        Exit Sub
    Else
        If Len(Application.Workbooks(File).Name) > 0 Then
        Else
        Workbooks.Open Filename:=Path & File
        End If
    End If
 
    ComboBox1.Clear
    
    For Each ws In Workbooks(File).Worksheets
        ComboBox1.AddItem ws.Name
    Next ws

End Sub

Select allOpen in new window

thanks for your prompt help.

When I change the sheet I error on the line below.

If Len(Application.Workbooks(File).Name) > 0 Then

Once you have selected the Supplier from the drop down does it enable you to select the items in that sheet and auto fill in the details?

Cheers
G

There are two places in the code where it assigns the file that it is looking for

well three with the new code block.  

you'll see File="Data.xlsx"    If the actual name of the file is not Data.xlsx you'll need to change those to the correct file name.  If it changes we might want to make it so it references a cell and can be easily changed.

If you open the datafile, I think you'll find it works perfectly, that code was to detect if if the file was open or not and must need to be tweeked.  I'll have something shortly.

Ok cool... I now have it working. What I was hoping rather than it apply the contents of the supplier that cells B10 and below would be drop down boxes and you could select the product and then the Product Ref and Unit price would be auto filled.

Ok,

I don't think I ever do anything to the data sheet, but here is the updated form sheet.  Let me know if you hit any additional snags! Form1.xlsm

Why not put a table datalink from Data.xlsx into Form.xlsx?
You can give the linked table a Name and use this for your dropdownlist.

Regards,
Davy

ScriptAddict
That seems to be working ok but I would like to have a little play if that is ok?

Davy2270:
This option sounds interesting as my VBA ability is not great. How would you propose I achieve this with the external linked data.

You need to go through 3 simple steps, but before we start it is important to have the Data.xlsx file in a fixed place on your PC or networkdrive.

Step1 - set up the connection:
Click Data tab on the ribbon - Connections - Browse for more...
Then select the Data.xlsx file
You will be prompted for the different sheets and Named ranges in the Data.xlsx workbook.
Choose the appropriate table or named range which you want to be linked.
Close the Workbook Connections dialog screen.

Step 2 - display the data in your workbook:
Click Existing Connections in the ribbon.
Select the connection you have just added. Click open.
In the import data dialog choose for Table and New Worksheet.

Now the data is linked.
Step 3 - Name the data for your dropdownlist
Select the Column which you need for the dropdownlist and give it a Name, by clicking Define Name in the Formula tab of the ribbon. You can then use this name to link the values to the dropdownlist.

You can set the Refresh behaviour of your linked table by selecting a cell in it and then click Properties in the ribbon.

The VBA syntax for refreshing the data is:
Application.RefreshAll

Regards,
Davy

Davy,

Does this approach work when he is using multiple worksheets?  If I understand correctly the number of worksheets could change as well.

Will he need a new sheet in the form document for each page in the other document?

-SA

You do not necessarily need a new sheet for each linked piece of data. At step 2 - display the data in your workbook, you can choose for New Worksheet or Existing Worksheet. When you choose for Existing Worksheet, you also need to assign a startcell address. Assign a celladdress which is outside the former linked table.

Regards,
Davy

I think the challenge here is that the supplier name, one of the items he want's to populate on is based on the worksheet(tab) names in the data file.  With each supplier having their own worksheet in the data file.  My guess is that the number of sheets can vary as each supplier is added or removed.

ScriptAddict:
That is correct the suppliers will come and go. Ideally I would not have to make changes to the form in order to facilitate the changes.

Cheers
G

The Form1 that I sent will automatically adjust for however many suppliers are in the workbook, but I think it will only list a set number of items.  To change the maxinum number of items a supplier can have you just need to edit the range that it pulls.  

If you find it isn't doing enough items just let me know it's easy to adjust, a mater of moments.

I think the default is 15 items.

Ok, that was not clear to me. Thanks for pointing that out.
In this case I suggest you put all suppliers in one table on one sheet. This will make life a lot easier.
Whenever you need to remove a supplier, you sort the table on suppliername and delete the rows.

Or do you have a good reason to keep the suppliers on separate sheets?

As a last piece of advice, I would recommend you to use MS Access for this kind of data administration, that's what it is built for. MS Excel is a 'calculator', a very enhanced one, but still not appropriate for data administration.

Regards,
Davy

Thank you both.

On the basis of the recent few posts I'll crack on with the VBA solution. If it is ok I'm out of the office until Tuesday when I'll be able to look into it, I have a few questions though:

Is it possible for the drop down to be located where the Product description is and only display the Product description  in the drop down as we have longer product descriptions with some suppliers?

Is it possible for the drop downs to default to blank?

Let me know if I'm asking for too much as effectively the question is answered I’m just requesting a few tweaks.

Many thanks,
Glyn

Just to confirm I have a supplier with 140 products but I don’t expect any to go over 150.

If we could fit about 30 items on the page so it can print on an A4 Sheet that would be great. Obviously I can do the shuffling around with the columns and row widths.

Fantastic, everything I needed and more.

Thank you.