I want to create a drop down list that is sourced from a linked network file. What I want to do is give people the Word doc and have it link back for its drop down to a Word or Excel file on our shared network. This will allow for me to update the list box without sending them a new Word doc.
Thanks for the help!!!
Re: Word List box from external network file
Hi bamsch,
The following is something I've used on a number of occassions. I've made it generic so you just need to change the location/file name etc.
'******************************************************************
' MasterLocation = Your network drive
' Folder = Name of folder on network drive where file is located
' File.txt = File with the info you want to import into your drop down list
' cmbYourData = Name of drop down list
Dim X as Integer
Dim YourData as String
Open MasterLocation & "Folder\File.txt" For Input Shared As #FNum
X = 1
Do While Not EOF(FNum)
Line Input #FNum, YourData ' Reads in each line of data
cmbYourData.AddItem YourData ' Adds data to drop down list
If X = 1 Then
cmbYourData.Text = Yourdata 'makes first item appear in list
End If
X = X + 1
Loop
Close #FNumRe: Word List box from external network file
I perhaps should have mentioned that File.txt is a simple text file (use Notepad) with each item you want to import on a new line e.g.
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
This would read each day of the week in to the list and make Monday appear in the box
Re: Word List box from external network file
Thanks. I assume FNum is replaced by an integer. Can you confirm?
Thanks again
Re: Word List box from external network file
Sorry but I missed a couple of lines (see below). The FreeFile() returns an integer for the next file number available for use by the Open statement
'******************************************************************
' MasterLocation = Your network drive
' Folder = Name of folder on network drive where file is located
' File.txt = File with the info you want to import into your drop down list
' cmbYourData = Name of drop down list
Dim X As Integer
Dim YourData As String
Dim FNum as Integer
FNum = FreeFile()
Open MasterLocation & "Folder\File.txt" For Input Shared As #FNum
X = 1
Do While Not EOF(FNum)
Line Input #FNum, YourData ' Reads in each line of data
cmbYourData.AddItem YourData ' Adds data to drop down list
If X = 1 Then
cmbYourData.Text = Yourdata 'makes first item appear in list
End If
X = X + 1
Loop
Close #FNumDon’t have an account yet? Register yourself now and be a part of our community!