Friday, November 4, 2016

Excel Automatically Displaying Listbox When Cell Is Selected

           When any cell is selected in column A, the hidden listbox appears.The data received from other sheet (List sheet) is sorted on the listbox.
The data selected from this listbox is easily entered into the active cell.


The used codes :
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
If Not Intersect(Range("A:A"), Target) Is Nothing And Target.Count = 1 And Target.Address(False, False) <> "A1" Then
If ActiveCell.Row >= 9 Then
ActiveWindow.ScrollRow = ActiveCell.Row - 8
End If
         Me.ListBox1.MultiSelect = fmMultiSelectMulti
         Me.ListBox1.List = Sheets("List").Range("A2:A" & Sheets("List").Cells(Rows.Count, 1).End(xlUp).Row).Value
      
      For i = 0 To Me.ListBox1.ListCount - 1
      If Target.Value <> Empty And Me.ListBox1.List(i, 0) = Target.Value Then
      Me.ListBox1.Selected(i) = True
      End If
      Next i
        
        Me.ListBox1.Top = Target.Top
        Me.ListBox1.Left = Target.Left + Target.Width
        Me.ListBox1.Visible = True
            Else
        Me.ListBox1.Visible = False
      
    End If
i = Empty
End Sub
Private Sub ListBox1_Change()
Dim yaz As String
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
            yaz = yaz & Me.ListBox1.List(i) & " "
        End If
    Next i
    ActiveCell.Value = Trim(yaz)
End Sub "

No comments:

Post a Comment