what code and how do i write said code for changing an indirect drop down box to a multi choice drop down box?
Please explain your request a little further. Do you have a Data Validation Drop Down with a formula like
=INDIRECT($A$1) and you need =A1:A11?
You usually don't need VBA to change that, so I guess we need a little more info.
yes i did use data validation and the indirect command =indirect() but i want to be able to all of the choices from that indirect command to move forward to the next page in my workbookor a few of the choices from that drop down to move forward to the next page of my workbook.
I'm sorry, but I still don't understand what you are trying to do. I'd like to help, so please try explaining it in a different way and maybe I'll get it.
re: i want to be able to all of the choices from that indirect command to move forward to the next page in my workbook
Please explain this further.
Thanks!
ok here goes in the first drop down box i have a selection of engine sizes depending on what size engine the user chooses the parts to that engine are automatically displayed in the second drop down box. In most cases all of these parts are required for a build. However in some cases only a few are required. I am trying to make it so that the parts chosen (more than one) in the second drop down are then displayed on the next page of the workbook which is our build sheet. The problem I am running into is that the user can only choose one item from the second drop down and that is messing with my formula moving the coices to the build page. because all the choices are actually coming from the same cell. And the owner want to have only drop down boxes for the users to choose from no lists. Therefor no mistakes on part numbers. then once i get this figured out I will move the data into quickbooks for invoicing ect..
And thank you DerbyDad for helping all of us out I appreciate you!
re: And the owner want to have only drop down boxes for the users to choose from no lists. Therefor no mistakes on part numbers. Would the owner settle for a Drop Down to choose the Engine size and then have the parts for that engine listed in a "protected range"? The users could choose multiple parts but they wouldn't be able to change the parts in that list.
For example, with a Drop Down in A1, the list of parts for that engine would be placed in B1:B(whatever it takes).
With B1:B(whatever it takes) protected, the user could see all the parts and select multiple cells with the Ctrl key but not change anything.
When a different engine was chosen in A1, the new parts list would appear in B1: B1:B(whatever it takes).
BTW, if you did use Drop Down, how did you plan to move the multiple selections to the other sheet?
Hi, I am not sure if this helps, but you could use a drop down list and use the Multi-Select option.
The Drop-down is a List Box and in Properties Change 'MultiSelect' to option 2 'fmMultiSelectExtended'
The list box has 'ListFillRange' set to a named range which contains the parts list relevant to the engine.The user can select from the list using the usual selection procedures, such as holding down Ctrl key to make multiple selections, or using shift to select a continuous range of items in the list.
The following code retrieves the selected items and for this demo displays them in a message box.
The message box is displayed when the List box loses focus, e.g., when the user selects a cell or another object on the worksheet.
The List box was named ListBox1, change the code if the ListBox was named something else.
The code can be used to transfer the list into cells on another worksheet rather than creating a text string for the display.
Private Sub ListBox1_LostFocus() Dim strList As String Dim strSngMult As String Dim strResp As String Dim intSelCnt As Integer Dim n As Integer With ListBox1 'counter to count selected items intSelCnt = 0 'get selected items For n = 0 To .ListCount - 1 If .Selected(n) = True Then strList = strList & .List(n) & vbCrLf intSelCnt = intSelCnt + 1 End If Next n End With 'set message single or multiple If intSelCnt = 1 Then strSngMult = "This item was selected:" Else strSngMult = "These items were selected:" End If 'display selected items strResp = MsgBox(strSngMult & vbCrLf & strList, vbOKOnly, _ "Selected Items in Text Box 1") End SubRegards