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