Re: Replace cells with selection from ComboBox

[g00glethis1]

This is what Dave has accomplished so far w/ the formula:

For the Combo box Drop down list In-put range: Data!$6:$6
The following Requirements for Combo Box HAVE BEEN MET:
1) Sort By: Name Ascending
2) Drop down list: Shows All Products/Names For Cell Range: Data!$6:$6 w/in Drop Down list line.

So the name of the products appear as needed in the combo box, So now there just needs to be an action once a product is selected.

Each Product Name owns its own column in Row 6 (i.e. Pepsi (A6), Cola (B6), Dr. Pepper (C6), Sprite (D6) & so on.) & acts as the key kinda of like a database program.

PART#1 WORKS PERFECT:

Name Ascending: Combo Box Drop Down List Menu: ->

Example:

Option1 Name = Cell F 6 (From Sheet 2 "Data")
Option2 Name = Cell G 6 (From Sheet 2 "Data")
Option3 Name = Cell C 6 (From Sheet 2 "Data")
Option4 Name = Cell A 6 (From Sheet 2 "Data")
Option5 Name = Cell * 6 (From Sheet 2 "Data")
Option6 Name = Cell D 6 (From Sheet 2 "Data")
Option7 Name = Cell H 6 (From Sheet 2 "Data")

 Private Sub CommandButton1_Click()

    Dim r
    Dim LastCol As Long

    Dim I As Long
    Dim j As Long
    Dim Temp As Variant


    LastCol = Worksheets("Sheet2").Cells(6, 255).End(xlToLeft).Column


    Worksheets("Sheet2").Cells(6, 1).Resize(1, LastCol).Name = "List"


    r = Sheets("Sheet2").Range("List").Value

    Me.ComboBox1.List = Application.Transpose(r)



    With ComboBox1
        For I = 0 To .ListCount - 1
            For j = I + 1 To .ListCount - 1
                If .List(I) > .List(j) Then
                    Temp = .List(j)
                    .List(j) = .List(I)
                    .List(I) = Temp
                End If
            Next j
        Next I
    End With

End Sub

PART#2: Now that Part#1 works perfect, we can proceed to Part2.

So what needs to happen when an "option" from the combo box is selected, it replaces the same selected cells from Sheet1 from the cells in the column where the "key" resides from Sheet2.

-----------------------------------------------------------

Option4 Selected:

Replace Cell C1 (From Sheet1) w/ Cell A6 (From Sheet 2 "Data") KEY
Replace Cell B1 (From Sheet1) w/ Cell A1 (From Sheet 2 "Data")
Replace Cell F1 (From Sheet1) w/ Cell A2 (From Sheet 2 "Data")
Replace Cell F2 (From Sheet1) w/ Cell A3 (From Sheet 2 "Data")
Replace Cell F3 (From Sheet1) w/ Cell A4 (From Sheet 2 "Data")
Replace Cell F4 (From Sheet1) w/ Cell A5 (From Sheet 2 "Data")

---------------------------------------------------

Option2 Selected:

Replace Cell C1 (From Sheet1) w/ Cell G6 (From Sheet 2 "Data") KEY
Replace Cell B1 (From Sheet1) w/ Cell G1 (From Sheet 2 "Data")
Replace Cell F1 (From Sheet1) w/ Cell G2 (From Sheet 2 "Data")
Replace Cell F2 (From Sheet1) w/ Cell G3 (From Sheet 2 "Data")
Replace Cell F3 (From Sheet1) w/ Cell G4 (From Sheet 2 "Data")
Replace Cell F4 (From Sheet1) w/ Cell G5 (From Sheet 2 "Data")

------------------------------------------------------------

Opt7 Selected:

Replace Cell C1 (From Sheet1) w/ Cell H6 (From Sheet 2 "Data") KEY
Replace Cell B1 (From Sheet1) w/ Cell H1 (From Sheet 2 "Data")
Replace Cell F1 (From Sheet1) w/ Cell H2 (From Sheet 2 "Data")
Replace Cell F2 (From Sheet1) w/ Cell H3 (From Sheet 2 "Data")
Replace Cell F3 (From Sheet1) w/ Cell H4 (From Sheet 2 "Data")
Replace Cell F4 (From Sheet1) w/ Cell H5 (From Sheet 2 "Data")

& so on.