Excel Drop Down List Problem

Tags:
excel
problems
February 26, 2010 at 12:17:29
Specs: Windows 23.02.2010
what code and how do i write said code for changing an indirect drop down box to a multi choice drop down box?

See More: Excel Drop Down List Problem

Report •

#1
February 26, 2010 at 12:26:01
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.


Report •

#2
February 26, 2010 at 12:31:30
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.

Report •

#3
February 26, 2010 at 12:55:38
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!


Report •

Related Solutions
See More ↓

#4
February 26, 2010 at 13:11:07
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..

Report •

#5
February 26, 2010 at 14:04:03
And thank you DerbyDad for helping all of us out I appreciate you!

Report •

#6
February 26, 2010 at 16:55:36
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?


Report •

#7
February 27, 2010 at 05:17:29
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 Sub

Regards


Report •
Start a discussion

Ask Question