MS Excel to auto populate a drop down list based on parsing another drop down list

If you are wanting to parse the first two letters like you have show here:

If AA-1234 is selected, the box is populated with AA, then use this in place of the second validation box:

=LEFT(cell, 2)

where cell is the location of your first validation list.


If you have many values that need to be populated, take a look at the attached workbook.
MatchingLists.xlsx

I am already successfully parsing the first column with "AA", if "AA-1234" is selected from the first column drop down and putting the "AA" value in column #2, but #2 is not now a drop down list.

I am now required to make the the second column a drop down list.

I need "AA" to be auto-selected in the drop down list in column #2,  if "AA-1234" is selected in the drop down list from column #1.

The column #1 drop down selection determines which choice is selected in drop down list #2.

To make sure I am clear:

If AA - 1234 is selected, there are multiple choices populated in the next dropdown box, such as: AA, BB, CC, etc.

If this is the case, see the attached workbook that I provided.

Hi,

I think I understand what you are trying to do now.

You wish to loop through every entry in the second list (that will be a drop-down listbox) & automatically select a value derived from the previously selected entry in the first listbox.

I am (still) unsure if you are using Data Validation, or not, though.

The attached workbook demonstrates Data Validation (in cells [A1] & [B1] of the worksheet [Q_28256835]) & drop-down (Form) combobox controls in cells [E1] & [F1].

The Visual Basic for Applications code within the code module for the [Q_28256835] worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

  On Error Resume Next

  If Target.Address = [A1].Address Then
     [B1] = Trim$(Left$(Target, InStr(Target & "-", "-") - 1))
  End If
  
End Sub

Select allOpen in new window

The code from the "basQ_28256835" code module is as follows...

Option Explicit
Public Sub cboFirst_Change()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28256835.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28256835
' Question Title:   MS Excel to auto populate a drop down list based on parsing another drop down list
' Question Asker:   LM_GROUP                                  [ http://www.experts-exchange.com/M_4229627.html ]
' Question Dated:   2013-10-03 at 13:11:12
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim lngIndex                                          As Long
  Dim strValue                                          As String
  Dim vntValue                                          As Variant
  
  On Error GoTo Err_cboFirst_Change
  
  strValue = ActiveSheet.Shapes("cboFirst").OLEFormat.Object.List(ActiveSheet.Shapes("cboFirst").OLEFormat.Object.Value)
  strValue = Trim$(Left$(strValue, InStr(strValue & "-", "-") - 1))
  
  ActiveSheet.Shapes("cboSecond").OLEFormat.Object.ListIndex = 0&
  
  If Len(Trim$(strValue)) > 0 Then
     lngIndex = 0&
  
     For Each vntValue In ActiveSheet.Shapes("cboSecond").OLEFormat.Object.List
  
         lngIndex = lngIndex + 1&
      
         If CStr(vntValue) = strValue Then
            ActiveSheet.Shapes("cboSecond").OLEFormat.Object.ListIndex = lngIndex
            Exit For
         End If ' If CStr(vntValue) = strFind_Value Then

     Next vntValue
  End If ' If Len(Trim$(strValue)) > 0 Then

Exit_cboFirst_Change:

  On Error Resume Next
  
  Exit Sub

Err_cboFirst_Change:

  On Error Resume Next
  
  Resume Exit_cboFirst_Change
  
End Sub

Select allOpen in new window

Please look at the two options (cells [A1] & [B1], and cells [E1] & [F1]), & let me know if either approach is what you intended.

Thank you.

BFN,

fp.
Q-28256835.xls

Sorry for not stating this earlier, but because of server security restrictions, that I have no control over, uploaded files that contain macros are blocked, therefore I cannot use your code, thanks.
I provide a spreadsheet that the user downloads from our server to theirs, fills in and the uploads back to our server, their server blocks macros
("and that's  all I have to say about that" - Forrest Gump).

...humour me a little, in case there is something we can do for you;

What data entry method are using using...

Data Validation (i.e. cells [A1] & [B1] in my attachment above), or
Form Controls (i.e. cells [E1] & [F1] in the same attachment)?

I can only tell you that the entity to which I provide support prohibits file uploads that contain embedded code, it is seen as a viral attack and blocked, neither option can be used. I am limited to only using formulas. Thank You for your help

Signed,
Working with both hands tied behind my back (but still working)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.