Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Select Vs Activate: When to use which?

  1. #1
    Join Date
    28th February 2005
    Posts
    95

    Select Vs Activate: When to use which?

    1. What's the difference between:
    VB:
    Worksheets("Sheet1").Range("A5").Select 
    
    
    and
    VB:
    Worksheets("Sheet1").Range("A5").Activate 
    
    
    ?

    2. Can I use a Selection. method with an object 'selected' using .Activate in the preceding code line?

    3. Say I am currently on Sheet2, Cell A5. When I click the macro button, I want Excel to copy the value from Sheet1, Cell A5 and paste it into Sheet2, Cell A5. But I do NOT want the screen display to actually jump from Sheet2 to Sheet1, and then back to Sheet2. So do I need to use .Activate, or .Select, to copy the value of Sheet1, Cell A5?
    Last edited by Om Avataar; April 6th, 2007 at 20:50.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Select Vs Activate: When to use which?

    in terms of cells help says

    Remarks
    To select a cell or a range of cells, use the Select method. To make a single cell the active cell, use the Activate method.
    But normal selection is not required. So in answer to question 3,
    VB:
    Sub Macro1() 
         
        Worksheets("Sheet1").Range("A5").Copy Worksheets("Sheet2").Range("A5") 
         
    End Sub 
    
    

    Cheers
    Andy


  3. #3
    Join Date
    28th February 2005
    Posts
    95

    Re: Select Vs Activate: When to use which?

    Thanks, Andy... Appreciate it

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: Select Vs Activate: When to use which?

    Hi,

    Have you looked at the VBE Help for details on each of the methods?

    Having said that, you will generally find that your code runs much better by using neither method. It is rarely necessary to either Select or Activate an object in order to work with it. In fact, doing so just makes your code slower.

    For example, using your objective of copying, if we used the Macro Recorder we would get the following code:
    VB:
    Sub Macro1() 
         '
         ' Macro1 Macro
         ' Macro recorded 06/04/2007 by Richie
         '
         
         '
        Sheets("Sheet1").Select 
        Range("A5").Select 
        Selection.Copy 
        Sheets("Sheet2").Select 
        ActiveSheet.Paste 
    End Sub 
    
    
    However, we could obtain the same objective by removing the Select elements of the code (and specifying the destination argument). Like this:
    VB:
    Sub Macro2() 
        Sheets("Sheet1").Range("A5").Copy Destination:=Sheets("Sheet2").Range("A5") 
    End Sub 
    
    
    Does that help?
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  5. #5
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Select Vs Activate: When to use which?

    Don't use either - they aren't normally needed.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th July 2012
    Posts
    1

    Re: Select Vs Activate: When to use which?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th December 2008
    Posts
    3

    Re: Select Vs Activate: When to use which?

    I'm still a bit puzzled. A followup question or two, please...

    Qa. If I use

    Sheet(3).Select

    then have I implicitly activated Sheet(3)?

    Qb. If I use

    Sheet(3).Activate

    then have I implicitly selected Sheet(3)? (I think the answer is "no", based on other posts.)


    Qc. If I give command (use the statement)

    Sheet(3).Activate

    then have I told Excel "I want to look at Sheet(3)!" ?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th November 2013
    Location
    UK
    Posts
    962

    Re: Select Vs Activate: When to use which?

    Do this:

    1) Manually select the range A1:A10

    The Selection is A1:A10

    2) Start pressing the Enter key on your keyboard - notice that one cell that turns white and moves down? that's the Active cell.

    So to summarize - in code you could do this:

    VB:
    Range("A1:A10").Select 'Select range
    Range("A5").Activate 'A1:A10 is still selected, but A5 is now the active cell.
    
    
    The same logic can be applied with sheets:

    VB:
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select 
    Sheets("Sheet2").Activate 
    
    
    Hope that explains a little better

    Generally speaking, there is very rarely any need to "Select" anything in VBA, but that will come naturally as you learn more of the language and how to use objects and their properties directly. Until then - there's always OzGrid! :D
    Have-A-Read!
    ----------------------------------------------
    # WHAT HAVE YOU TRIED?
    # Excel/VBA Golden Rules [by ​Dave Hawley]
    # Excel Best Practices
    # Forum Rules
    ----------------------------------------------
    Wilde XL Solutions
    Free VBA -> HTML web formatter
    Free VBA Code snippets
    Test your regular expressions*
    ​*uses PHP RegEx - some features not supported in VBScript.RegExp


    An SQL statement walks up to two tables in a bar and asks "May I join you?"

  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,570

    Re: Select Vs Activate: When to use which?

    If the objects, such as worksheet/range/cells etc., refers to Single object, Select/Activate means the same.

    However, if multiple, Select/Activate becomes different.

    Select can select multiple objects within the collection, but Activate should select single object.

    e.g.
    VB:
    Sub test() 
        Range("a1,c1,g1").Select 
        MsgBox ActiveCell.Address 
        Range("g1").Activate 
        MsgBox ActiveCell.Address 
        Range("b3").Activate 
        MsgBox ActiveCell.Address 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Calendar Control To Activate/Select Sheets
    By lancemmx in forum EXCEL HELP
    Replies: 4
    Last Post: August 17th, 2008, 17:41
  2. Select/Activate Sheet Where Name Resides In Cell
    By Sagaram in forum EXCEL HELP
    Replies: 5
    Last Post: April 27th, 2008, 12:37
  3. Replies: 5
    Last Post: March 24th, 2006, 07:06
  4. Sheet1.Activate vs Sheet1.Select
    By mikeburg in forum EXCEL HELP
    Replies: 5
    Last Post: October 13th, 2005, 12:29
  5. Select and activate first blank cell
    By rirstress in forum EXCEL HELP
    Replies: 3
    Last Post: May 30th, 2003, 10:25

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno