Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding the sheets my macro works with...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding the sheets my macro works with...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding the sheets my macro works with...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function works on some sheets but not others | Excel Worksheet Functions | |||
Hiding and Showing Sheets using Macro | Excel Discussion (Misc queries) | |||
hiding zero values on all sheets & by default on new sheets | Excel Worksheet Functions | |||
Locking Sheets / Hiding Sheets | Excel Worksheet Functions | |||
Hiding sheets with macro disabled - with a big twist! | Excel Programming |