Excel Forum

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lb As Object, i As Integer, lsrng As Range
    Set lb = ActiveSheet.ListBox1
    Set c = Target
    Set lsrng = Range("k2:k4")
    If Not Intersect(Target, Range("j13:j" & Rows.Count)) Is Nothing Then
        With lb
            For i = .ListCount - 1 To 0 Step -1
                .RemoveItem (i)
            Next i
            For i = 0 To lsrng.Count - 1
                .AddItem lsrng(i + 1).Value
            Next i
            .Left = Target.Offset(0, 1).Left
            .Top = Target.Offset(0, 1).Top
            .Visible = True
        End With
    Else
        lb.Visible = False
    End If
End Sub

Code: Select all

Public c As Range

Sub Bevel1_Click()
    Dim i As Integer, t As String
    Set lb = Sheets(1).ListBox1
    With lb
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                t = t & ";" & .List(i)
            End If
        Next i
    End With
    c.Value = Mid(t, 2)
End Sub

Code: Select all

'Other Code...
    If Not Intersect(c, Range("j13:j" & Rows.Count)) Is Nothing Then
        c.Value = Mid(t, 2)
    End If
End Sub

Code: Select all

'Other code...
With lb
    .ListFillRange = ""
    For i = .ListCount - 1 To 0 Step -1
        .RemoveItem (i)
    Next i
    For i = 0 To lsrng.Count - 1
        .AddItem lsrng(i + 1).Value
    Next i
    .Left = Target.Offset(0, 1).Left
    .Top = Target.Offset(0, 1).Top
    .Visible = True
End With
'Other code...

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lb As Object, i As Integer, lsrng As Range
    Set lb = ActiveSheet.ListBox1
    Set c = Target
    If Target.Column = Columns("j").Column Then
        Set lsrng = Range("j2:j4")
    ElseIf Target.Column = Columns("k").Column Then
        Set lsrng = Range("k2:k4")
    End If
    If Not Intersect(Target, Range("j9:k" & Rows.Count)) Is Nothing Then
        With lb
            .ListFillRange = ""
            For i = .ListCount - 1 To 0 Step -1
                .RemoveItem (i)
            Next i
            For i = 0 To lsrng.Count - 1
                .AddItem lsrng(i + 1).Value
            Next i
                .Left = Target.Offset(0, 1).Left
                .Top = Target.Offset(0, 1).Top
                .Visible = True
        End With
    Else
        lb.Visible = False
    End If
End Sub

Code: Select all

Public c As Range

Sub Bevel1_Click()
    Dim i As Integer, t As String
    Set lb = Sheets(1).ListBox1
    With lb
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                t = t & "; " & .List(i)
            End If
        Next i
    End With
        If Not Intersect(c, Range("j9:k" & Rows.Count)) Is Nothing Then
            c.Value = Mid(t, 2)
        End If
End Sub