LisBox List

  • Thread starter Thread starter Abdul
  • Start date Start date
A

Abdul

Hi,


I am using a user form with a combobox and list box to
get certain specific data.

Suppose that I am activating the user form from sheet1
and my data in sheet2 what i am doing is..

When i select an item from combobox1 all data related to
that item is listed in listbox1.

in that process my sheet2 is activated and alldata in it
can be seen.

is it possible to do this without activating sheet2?

From sheet1 the userfom is activated and based on
combobox1 value items listed in listbox from sheet2.

thanks in advance

Abdul
 
you can refer to sheet2 with

Worksheets("Sheet2").Range("A1:A100")

for example. This does not activate sheet 2.

Avoid code like

Sheet2.Activate
or
Worksheets("Sheet2").Activate
Range("A1:A100").Select

Or if you can't avoid that, then

Application.ScreenUpdating = false
' recorder style code here
Worksheets("Sheet2").Select
' more code
Worksheets("Sheet1").Select
Application.ScreenUpdating = True
 
Abdul

What is the code that you are using to fill listbox1? It should look
something like this

Private Sub ComboBox1_Change()

Dim sh As Worksheet
Dim cell As Range

Me.ListBox1.Clear

Set sh = Sheets("sheet2")

For Each cell In sh.Range("a2:a5")
If cell.Value = Me.ComboBox1.Value Then
Me.ListBox1.AddItem cell.Offset(0, 1).Value
End If
Next cell

End Sub

And sheet2 will not be shown.
 
Back
Top