Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Selecting items from another worksheet withoutactivating it
I have a question. I have several macros that I have written that
requires them to look through (copy, select, validate, etc) other worksheets for data. Is there away to go through these worksheets without the end-user actually seeing the macro run through them For Example: Lets say we have something like this Dim cname as string Dim searchName as string cname = Range("A1").Value Sheets("Sheet2").Select Range("A1").Select searchName = Activecell.value Do While Activecell.value < "" If searchName = cname then msgbox "Sheet2 has this name" & searchName End If Loop Granted this is a simple example, but it does demostrated that a user will see Excel switch between sheets Is there away around this?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Selecting items from another worksheet without activating it
try
Application.screenupdating=false your code Application.screenupdating=true Regards Neil wrote in message ... I have a question. I have several macros that I have written that requires them to look through (copy, select, validate, etc) other worksheets for data. Is there away to go through these worksheets without the end-user actually seeing the macro run through them For Example: Lets say we have something like this Dim cname as string Dim searchName as string cname = Range("A1").Value Sheets("Sheet2").Select Range("A1").Select searchName = Activecell.value Do While Activecell.value < "" If searchName = cname then msgbox "Sheet2 has this name" & searchName End If Loop Granted this is a simple example, but it does demostrated that a user will see Excel switch between sheets Is there away around this?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Selecting items from another worksheet without activ
Most of the time, you can avoid the flicker and flash by eliminating the
select and activate method of writing the code. Here is an example of the sample you posted converted to a more direct method. cname = ActiveSheet.Range("A1").Value Set c = Sheets("Sheet2").Cells(Find, What:=Range("A1").Value) If Not c Is Nothing Then If c.Value = cname Then MsgBox "Sheet2 has this name " & c.Value End If End If End Sub It uses the Find method rather than stepping through each cell in a loop. It is much faster and does not have to select or activate sheet 2. You just have to make sure that you qualify your command lines with the correct sheet object so it knows where to look and where to execute. Otherwise, it thinks all commands are for the active sheet. Just to be sure, it never hurts to include the command lines that Neil suggested also. " wrote: I have a question. I have several macros that I have written that requires them to look through (copy, select, validate, etc) other worksheets for data. Is there away to go through these worksheets without the end-user actually seeing the macro run through them For Example: Lets say we have something like this Dim cname as string Dim searchName as string cname = Range("A1").Value Sheets("Sheet2").Select Range("A1").Select searchName = Activecell.value Do While Activecell.value < "" If searchName = cname then msgbox "Sheet2 has this name" & searchName End If Loop Granted this is a simple example, but it does demostrated that a user will see Excel switch between sheets Is there away around this?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple items in an excel drop down list | Excel Discussion (Misc queries) | |||
Macro selecting multiple items in list box | Excel Programming | |||
Selecting items from a list. To transfer to another worksheet? | Excel Discussion (Misc queries) | |||
USE MACRO TO SAVE WORKSHEET SELECTING FILENAME FROM CELLS | Excel Programming | |||
Selecting a worksheet from a macro in a different workbook | Excel Programming |