I pick B1, B2 etc fom a combobox and populate a listbox with the correct
items from column B.
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

1) Move your Application.ScreenUpdating = True line AFTER listIndex lines
2) You don't need 2 ListIndex lines, leave only the last one:

ListBox1.ListIndex = 0

because only one value can be selected, your code selects 12th value, and
then immediately selects the first. It does not make sense.

3) macro1 problem:
delete first 2 lines:

Sheets("Recipes").Select
Cells.Select

it should work now.

3) in general, you can greatly improve your code if you stop using Select.
You can get the same result in most cases by directly referring to cells and
ranges (spend some time reading help or a book or this forum on how to use
object Range, it's a very worthy investment of your time. a good source is
he http://www.j-walk.com/ss/excel/tips/tip20.htm, explore it). For
example, this code:
Sheets("Main").Select
Range("c2").Select ' combobox populates c2
MyData = ActiveCell.Text

can be written as:

MyData=Sheets("Main").Range("c2").Text

it's not only shorter, but also much faster, which is a big deal if you put
it in the loop.

Cheers -

RADO



"JSnader" wrote in message
k.net...
I pick B1, B2 etc fom a combobox and populate a listbox with the correct
items from column B.
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

thanks for your quick reply!!
If I put screenupdate at the end, my box does not update, but if I scroll
down and then back up, the list is updated. That is what my code does. I
think there must be a better way.

Your other suggestions are very practical. I usually start with the
recorder and when things work well I condense it.



"RADO" wrote in message
...
1) Move your Application.ScreenUpdating = True line AFTER listIndex lines
2) You don't need 2 ListIndex lines, leave only the last one:

ListBox1.ListIndex = 0

because only one value can be selected, your code selects 12th value, and
then immediately selects the first. It does not make sense.

3) macro1 problem:
delete first 2 lines:

Sheets("Recipes").Select
Cells.Select

it should work now.

3) in general, you can greatly improve your code if you stop using Select.
You can get the same result in most cases by directly referring to cells
and
ranges (spend some time reading help or a book or this forum on how to use
object Range, it's a very worthy investment of your time. a good source is
he http://www.j-walk.com/ss/excel/tips/tip20.htm, explore it). For
example, this code:
Sheets("Main").Select
Range("c2").Select ' combobox populates c2
MyData = ActiveCell.Text

can be written as:

MyData=Sheets("Main").Range("c2").Text

it's not only shorter, but also much faster, which is a big deal if you
put
it in the loop.

Cheers -

RADO



"JSnader" wrote in message
k.net...
I pick B1, B2 etc fom a combobox and populate a listbox with the correct
items from column B.
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