copying dropdowns boxes

[cornepiek]

hi all

i have a list of clients. my users needs to select the stage the client is in manually.

these selections needs to be guaranteed to be uniform, as i will need to run reports and searches in this value.
to accomplish this, i am using a dropdown box next to each client.

i am using a combo box for this purpose
here is the details of the dropdown box:

input range: I2:I10
Output Range: K2


i need to copy this box to all the cells in column C starting from C2.
the input range must stay static, but the output range needs to update to K3, K4, K5 and such as i copy the boxes.

is this possible, or do i need to manually update these boxes??

 

[Norie]

How about using Data>Validation...?

Select all the cells you need in column K, goto Data>Validation..., select Allow List and enter this in the Source box:

=$I$2:$I$10

 

[Sektor]

Try this. Double-click any cell in columns C. Drop-down will appear. It will show values from "I2:I10". As you select item, this item appears in column K in the same row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Columns("C:C")) Then
        Call AddDropDown
        Cancel = True
    End If
End Sub

Sub AddDropDown()

    Dim dd As DropDown, arr As Variant, i As Integer
    
    arr = Range("I2:I10")
    
    With ActiveCell
        Set dd = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
    End With
    
    With dd
        .OnAction = "SetItem"
        For i = 1 To UBound(arr, 1)
            .AddItem arr(i, 1)
        Next
    End With

End Sub

Sub SetItem()
    
    With ActiveSheet.DropDowns(Application.Caller)
        Range("K" & .TopLeftCell.Row) = .List(.ListIndex)
        .Delete
    End With
    
End Sub

 

[Norie]

Oops, ignore my post.

I thought C/K was a typo and you just meant K.

 

[cornepiek]

thanks a mil

the data validation did the trick.

i have use for this macro as well, this answered my next question.

thanks