listbox will not refresh
 

I pick B1, B2 etc fom a combobox and populate a listbox with the correct
items from column B. (see below)
My problem is that the listbox will not update without these 3 lines at the
end.
I don't like the blinks and just putting ScreenUpdating at the end does not
work.

Application.ScreenUpdating = True
ListBox1.ListIndex = 12
ListBox1.ListIndex = 0

I'd appreciate any suggestions to improve the code below!!
THIA
James


A B
B1 H1
B1 H2
B1 H3
B1 H4
B2 MC1
B2 MC2
B2 MC3
B2 MC4






Sub PickAFarmer()
'Application.ScreenUpdating = False
Sheets("Main").Select
Range("c2").Select ' combobox populates c2
MyData = ActiveCell.Text
Columns("aa:cz").ClearContents

Sheets("Recipes").Select
ActiveSheet.Range("a1").Select
ActiveCell.CurrentRegion.Select
Macro1 ' when I insert Macro 1 here I get an error so it
is listed below

ActiveSheet.Range("A1").Select

Do While ActiveCell.Text < MyData
ActiveCell.Offset(1, 0).Select
Loop

Set first = ActiveCell

Do While ActiveCell.Text = MyData
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 60).Select
Set last = ActiveCell

ActiveSheet.Range(first, last).Select
Selection.Copy
Sheets("Main").Select

Range("aa2").Select
ActiveSheet.Paste

Range("AB2").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste

Range("G2").Select
Application.ScreenUpdating = True

ListBox1.ListIndex = 12
ListBox1.ListIndex = 0

End Sub


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/3/03 by James Snader

Sheets("Recipes").Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

listbox will not refresh
 

JSnander

I have cleaned upyour code by combining several instructions into one
instruction

I don't play around much with list boxes so I am not sure what your
problem is exactly but I have included a command of repaint which
refreshes a userform on screen


Is this sub on a userform?

Sub PickAFarmer()
'Application.ScreenUpdating = False

MyData = Sheets("Main").Range("c2").Text ' combobox populates c2
Sheets("Main").Columns("aa:cz").ClearContents


Sheets("Recipes").Activate
cell.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A1").Activate


Do While ActiveCell.Text < MyData
ActiveCell.Offset(1, 0).Activate
Loop

Set first = ActiveCell

Do While ActiveCell.Text = MyData
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 60).Select
Set last = ActiveCell

Range(first, last).Copy

Sheets("Main").Select
Range("aa2").Paste

Range("AB2").Copy
Range("G2").Paste

Range("G2").Select

Application.ScreenUpdating = True

Repaint


End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements