Prompt to select from a list in another workbook

[Jim G]

While creating a new service job, I would like users to have a list of jobs
to select from for input into the service registry. The job list resides in
another workbook.

Is it possible to prompt for an input in VBA selected from another sheet or
workbook?

 

[Paul]

Try this :


Sub Choose_from_List()

' myForm is your userform containing a list box to select the item
' myList is the list box

zWorkbook = "My Other List.xls" ' being the file containing the list
zListSheet = "My List Sheet" ' being the sheet on which the list is
placed
zListRange = "My List" ' being the list to use


nColumn =
Workbooks(zWorkbook).Worksheets(zListSheet).Range(zListRange).Column
' Ascertain the top row of items to populate the main list
nStart =
Workbooks(zWorkbook).Worksheets(zListSheet).Range(zListRange).Row
' Ascertain the number of items to populate the main list
nItems =
Workbooks(zWorkbook).Worksheets(zListSheet).Range(zListRange).Rows.Count
' Calculate the last row of items to populate the main list
nEnd = nStart + nItems - 1

' Define an array used to hold the items in the list so that it is big
enough to handle the number of items
ReDim myArray(nItems - 1, 2)

For nCount = nStart To nEnd
' Set the array to hold the code of each item in the list
myArray(nCount - nStart, 1) = nCount - nStart + 1
' Set the array to hold the description of each item in the list
myArray(nCount - nStart, 0) =
Workbooks(zWorkbook).Worksheets(zListSheet).Cells(nCount, nColumn)

Next
' Populate the dropdown box list with the available tables
myForm.myList.List = myArray

myForm.Show

End Sub

 

[Jim G]

Thanks Paul,
I tried this with a run time error ‘1004’. However, this may be more than I
need.
While creating a new maintenance job in my register I will prompt the user
for input to populate the row with data. EG: JobNo, Client Name, Address etc.

This information resides in ‘Job Schedule.xls’ and I would like to ensure
the correct Job No is used and any related information (Client Name etc) is
correctly related to the job and to reduce the amount of data the user has to
input where it is the same in the ‘Job Schedule’. New information such as
the nature of the problem will be input by the user via a prompt.

Is it possible to open ‘Job Schedule.xls’, showing the Job data where the
user selects or provides a job number (located at B4) and have it collect
data from column E (Client) and C (Site) and enter it in addresses in the
Maintenance Register?

Cheers

 

[Paul]

Jim

Sorry it's taken a couple of days to get back.

I have a Master Tables.xls file that does exactly that job - in fact it
allows you to select the item from ANY of the columns in the list at the
touch of one button.
However, the code behind it will take a bit of posting.

In the meantime, I've tried to make my original piece of code fail - and I
can't. Could you let me know where it fails, and what file, sheet and range
you were using, please ? It would help in case someone else wants to use it.

 

[Jim G]

It seems the notification feature doesn't work for me and missed your post.

I get a subscript out of range error at : nColumn =
Workbooks(zWorkbook).Worksheets(zListSheet).Range(zListRange).Column
..

However, I would like to persist and would probably need an example of
actual inputs to understand the context.

If it helps you can email me your example at jg.supreme@westnet.***.au
(substitute com).