I've got code that opens up sheet2 and searches for specific cells. When that happens you can briefly see it open up sheet2... Granted it's very fast, I'd rather have it stay on Sheet1 and just do the work in the background. Here's my code, how can I do this?

Pick = ComboBox1.Value

Sheet2.Select
Sheet2.Range("B1").Select
Sheet1.Select

While ActiveCell.Value < Pick
Sheet2.Select
Sheet2.Cells(ActiveCell.Row + 1, 2).Select
' TODO: Move the cursor over to the LEFT once
Wend

Sheet2.Cells(ActiveCell.Row, 1).Select
Pick2 = ActiveCell.Value

Sheet1.Select
Sheet1.Range("G5").Select
ActiveCell.Value = Pick2

Hi Gary,

The answer to your question is Application.ScreenUpdating = False.

However there is no reason to use .Select at all. Try something like
(untested I'm afraid)

Sub A()
Dim R2 As Range

Set R2 = Sheet2.Range("B1")

While R2.Value < Pick
Set R2 = R2.Offset(1,0)
Wend

Pick2 = R2.Value
End Sub

HTH

Peter Beach

"Gary Phillips" wrote in message
...
I've got code that opens up sheet2 and searches for specific cells. When
that happens you can briefly see it open up sheet2... Granted it's very
fast, I'd rather have it stay on Sheet1 and just do the work in the
background. Here's my code, how can I do this?

Pick = ComboBox1.Value

Sheet2.Select
Sheet2.Range("B1").Select
Sheet1.Select

While ActiveCell.Value < Pick
Sheet2.Select
Sheet2.Cells(ActiveCell.Row + 1, 2).Select
' TODO: Move the cursor over to the LEFT once
Wend

Sheet2.Cells(ActiveCell.Row, 1).Select
Pick2 = ActiveCell.Value

Sheet1.Select
Sheet1.Range("G5").Select
ActiveCell.Value = Pick2

It is entirely possible to work with the contents of sheet 2 withou
EVER activating or selecting it.

you can use pieces of code such as "Pick2 = Sheet2.Cells(x, 1).Value"

Try replacing ALL of what you posted with ALL of the following:


Code
-------------------

Pick = ComboBox1.Value

x = 1
'by using the x variable to specify the row,
'you can make the While-Loop begin at B1
'automatically, without selecting it

While Sheet2.Cells(x, 2) < Pick
x = x + 1
' TODO: Move the cursor over to the LEFT once
Wend

'the cell in column B which is finally found to
'contain the value of Pick will be Sheet2.Cells(x,2)
'with x being whatever it is when the while-loop ends

Pick2 = Sheet2.Cells(x, 1).Value


Sheet1.Select
Sheet1.Range("G5").Select
ActiveCell.Value = Pick2

-------------------



If you replace your first posted version with the new version above
you should still get the result you are looking for.

I am about 99% confident with the replacement that I am posting. I a
no 100% sure because I don't have a clear vision of EVERYTHING you ar
doing with your entire program. That is, I assume that these ten t
twenty lines of code are but a small part of your overall project. S
I would have to know more about the whole thing in order to "guarantee
what I'm proposing.

Nonetheless...

try it!

I think you'll like it

(by the way, you could formulate a similar "fix" in order to enter
value into G5 of sheet 1 without selecting that sheet either.

--
Message posted from http://www.ExcelForum.com