Announcement

Collapse
No announcement yet.

Select Vs Activate: When to use which?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select Vs Activate: When to use which?

    1. What's the difference between:
    Code:
    Worksheets("Sheet1").Range("A5").Select
    and
    Code:
    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, 19:50.

  • #2
    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,
    Sub Macro1()

    Worksheets("Sheet1").Range("A5").Copy Worksheets("Sheet2").Range("A5")

    End Sub

    Cheers
    Andy

    Comment


    • #3
      Re: Select Vs Activate: When to use which?

      Thanks, Andy... Appreciate it

      Comment


      • #4
        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:
        Code:
        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:
        Code:
        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

        Comment


        • #5
          Re: Select Vs Activate: When to use which?

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

          Comment


          • #6
            Re: Select Vs Activate: When to use which?

            This is the best answer so far

            http://www.analytics-tools.com/2009/...elect-and.html

            Comment


            • #7
              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)!" ?

              Comment


              • #8
                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:

                Code:
                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:

                Code:
                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

                Comment


                • #9
                  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.
                  Code:
                  Sub test()
                      Range("a1,c1,g1").Select
                      MsgBox ActiveCell.Address
                      Range("g1").Activate
                      MsgBox ActiveCell.Address
                      Range("b3").Activate
                      MsgBox ActiveCell.Address
                  End Sub

                  Comment


                  • #10
                    Re: Select Vs Activate: When to use which?

                    No need to use Select or Activate methods.
                    No need to use Copy and Paste methods either.

                    Simply assign the value of Sheet1!$A$5 to cell Sheet2!$A$5, like this:

                    ThisWorkbook.Worksheets("Sheet2").Range("$A$5").Value = ThisWorkbook.Worksheets("Sheet1").Range("$A$5").Value

                    Note that if Sheet1!$A$5 changes in value, Sheet2!$A$5 will not reflect it since the above assignment is static. You'd have to execute this statement each time Sheet1!$A$5 changes in value.

                    If you *always* want Sheet2!$A$5 to have the same value as Sheet1!$A$5, then you can assign a formula to Sheet2!$A$5 interactively, or, using VBA, you would write:

                    ThisWorkbook.Worksheets("Sheet2").Range("$A$5").Formula = "=Sheet1!$A$5"

                    Comment

                    Working...
                    X