Home > Excel Tutorial > Excel Unhide Listbox When Range Is Selected

Excel Unhide Listbox When Range Is Selected

When any cell is selected in column A, the hidden listbox can be viewed. Data received from other sheet (List sheet) is sorted on the listbox.
The data selected from listbox is easily entered to the active cell.

Codes Of Tutorial :

“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 “
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: