• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dependent comboboxes

H

HanSam

Member
Hi,

I need help on the attached file. I can't seem to make it work, I've tried using select case and other codes I've seen, I can't seem to understand and they are being done on user forms. This I want on sheets.
 

Attachments

  • Dependent Combobox.xlsx
    15.9 KB · Views: 7
Same principle. Using different set up utilizing Combobox.
http://www.contextures.com/xlDataVal11.html

See sample project I made a while back based on method detailed in the link. (Double click on Market, Brand, or Station and you will see combobox instead of data validation).

Station is dependent on Brand in this instance.
 

Attachments

  • Example.xlsb
    183.4 KB · Views: 7
@Chihiro wow I didn't know that can be done.
my problem though is that I need the arrows always showing up because you know (certain users complaints) that is why I am going for combo box instead.
Below is the code I wrote, it is working fine to what I want them to happen, but it just seemed error prone because of repetitive coding and manual adding of item on the lists.

Code: 
Option Explicit

Private Sub cboProcess_Change()

With cboMatrix
    .Clear
    .AddItem "Volume"
    .AddItem "Timeliness"
    .AddItem "Quality"
End With

End Sub

Private Sub cboWorkstream_Change()

cboProcess.Clear
cboMatrix.Clear

Select Case cboLocation.Value & cboWorkstream.Value
    Case Is = "Global" & "All Workstreams"
        With cboProcess
            .AddItem "All Processes"
            .AddItem "e-Auction"
            .AddItem "e-Sourcing"
            .AddItem "PIR Creation"
            .AddItem "PIR Update"
            .AddItem "PO Creation"
            .AddItem "Price Check"
            .AddItem "LCM"
            .AddItem "Supplier Delisting"
            .AddItem "Supplier On-Boarding"
            .AddItem "Vendor Creation"
            .AddItem "Vendor Update"
            .AddItem "Global"
            .AddItem "Regional"
            .AddItem "Country"
        End With
    Case Is = "Global" & "Sourcing"
        With cboProcess
            .AddItem "e-Auction"
            .AddItem "e-Tender"
        End With
    Case Is = "Global" & "Transactional"
        With cboProcess
            .AddItem "PIR Creation"
            .AddItem "PIR Update"
            .AddItem "PO Creation"
            .AddItem "Price Check"
            .AddItem "LCM"
            .AddItem "Supplier Delisting"
            .AddItem "Supplier On-Boarding"
            .AddItem "Vendor Creation"
            .AddItem "Vendor Update"
        End With
    Case Is = "Global" & "Reporting"
        With cboProcess
            .AddItem "Global"
            .AddItem "Regional"
            .AddItem "Country"
        End With
 
    Case Is = "Bratislava" & "Sourcing"
        With cboProcess
            .AddItem "e-Auction"
            .AddItem "e-Tender"
        End With
    Case Is = "Bratislava" & "Transactional"
        With cboProcess
            .AddItem "PIR Creation"
            .AddItem "PIR Update"
            .AddItem "PO Creation"
            .AddItem "Price Check"
        End With
    Case Is = "Cairo" & "Sourcing"
        With cboProcess
            .AddItem "e-Auction"
            .AddItem "e-Tender"
        End With
    Case Is = "Cairo" & "Transactional"
        With cboProcess
            .AddItem "PO Creation"
        End With
    Case Is = "Manila" & "Sourcing"
        With cboProcess
            .AddItem "e-Auction"
            .AddItem "e-Tender"
        End With
    Case Is = "Manila" & "Transactional"
        With cboProcess
            .AddItem "LCM"
            .AddItem "PIR Creation"
            .AddItem "PIR Update"
            .AddItem "PO Creation"
            .AddItem "Supplier Delisting"
            .AddItem "Supplier On-Boarding"
            .AddItem "Vendor Creation"
            .AddItem "Vendor Update"
        End With
    Case Is = "Manila" & "Reporting"
        With cboProcess
            .AddItem "Global"
            .AddItem "Regional"
            .AddItem "Country"
        End With
    Case Is = "Mexico" & "Sourcing"
        With cboProcess
            .AddItem "e-Auction"
            .AddItem "e-Tender"
        End With
    Case Is = "Mexico" & "Transactional"
        With cboProcess
            .AddItem "PO Creation"
        End With
    End Select

End Sub

Private Sub Worksheet_Activate()

cboLocation.Clear
cboWorkstream.Clear
cboProcess.Clear
cboMatrix.Clear

With cboLocation
    .AddItem "Global"
    .AddItem "Bratislava"
    .AddItem "Cairo"
    .AddItem "Manila"
    .AddItem "Mexico"
End With

End Sub

Private Sub cboLocation_Change()

cboWorkstream.Clear
cboProcess.Clear
cboMatrix.Clear

Select Case cboLocation.Value
    Case Is = "Global"
 
        With cboWorkstream
            .AddItem "All Workstreams"
            .AddItem "Sourcing"
            .AddItem "Transactional"
            .AddItem "Reporting"
        End With
     
    Case Is = "Bratislava"
        With cboWorkstream
            .AddItem "Sourcing"
            .AddItem "Transactional"
        End With
     
    Case Is = "Cairo"
        With cboWorkstream
            .AddItem "Transactional"
        End With
     
    Case Is = "Manila"
        With cboWorkstream
            .AddItem "Sourcing"
            .AddItem "Transactional"
            .AddItem "Reporting"
        End With
     
    Case Is = "Mexico"
        With cboWorkstream
            .AddItem "Sourcing"
            .AddItem "Transactional"
        End With
    End Select

End Sub

Chihiro said:
Same principle. Using different set up utilizing Combobox.
http://www.contextures.com/xlDataVal11.html

See sample project I made a while back based on method detailed in the link. (Double click on Market, Brand, or Station and you will see combobox instead of data validation).

Station is dependent on Brand in this instance.
Click to expand...
 
You must log in or register to reply here.
Back
Top