Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to select from a list in another workbook
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? -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to select from a list in another workbook
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 -- If the post is helpful, please consider donating something to an animal charity on my behalf. "Jim G" wrote: 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? -- Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to select from a list in another workbook
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 -- Jim "Paul" wrote: 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 -- If the post is helpful, please consider donating something to an animal charity on my behalf. "Jim G" wrote: 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? -- Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to select from a list in another workbook
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. -- If the post is helpful, please consider donating something to an animal charity on my behalf. "Jim G" wrote: 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 -- Jim "Paul" wrote: 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 -- If the post is helpful, please consider donating something to an animal charity on my behalf. "Jim G" wrote: 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? -- Jim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt to select from a list in another workbook
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt User to Select Macro to Run? | Excel Programming | |||
How to Prompt a user to select a Folder. | Excel Programming | |||
Prompt to Select Cell | Excel Programming | |||
Prompt user to select directory | Excel Programming | |||
how do I prompt the user to select a cell? | Excel Programming |