List boxes+tick boxes problem

[afranks1987]

Hi all,

I'm looking to create a system where a user selects a tick box and three cells on the same row as the tick box are copied to a list box (or suitable alternative) which is on a separate sheet.

When the tick box is deselected, the entry should be removed from the list box.

Is this possible?

Thanks a lot,

Adam

 

[Oorang]

Hello,
Welcome to the board You will want to add an activex checkbox and listbox to a worksheet. (XL2003 View>ToolBars>Control ToolBox;XL2007 Developer>Controls>Insert) Then put this code in the worksheet module:

Private Sub CheckBox1_Change()
    Dim cll As Excel.Range
    Dim lngIndx As Long
    If Me.CheckBox1.Value Then
        For Each cll In Me.Range("B5:D5").Cells
            Me.ListBox1.AddItem cll.Value
        Next
    Else
        For Each cll In Me.Range("B5:D5").Cells
            For lngIndx = 0 To Me.ListBox1.ListCount - 1
                If Me.ListBox1.List(lngIndx) = CStr(cll.Value) Then
                    Me.ListBox1.RemoveItem lngIndx
                    Exit For
                End If
            Next
        Next
    End If
End Sub

 

[afranks1987]

Thank you very much Oorang. That all seems to make sense. I'll let you know when I've got it working.

Thanks again.