What is the difference between 'Select' a sheet and 'Activate' a sheet

[RJQMAN]

I have a complex program that has been running fine on Excel 2003,
2007, and 2010. Today when running the program on Excel 2003 I got an
error messages and the program hung up on this statement;

ThisWorkbook.Sheets("Recap").Select

I think the error said something like 'Method Not Supported,' but I do
not remember exactly what it said.

I tried various things and could not figure out why it crashed. Then
II replaced the statement with

ThisWorkbook.Sheets("Recap").Activate

and it ran without any problems.

I have run the program about a dozen times since the change, and it is
doing fine. I just don't understand why it crashed in the first
place??

In my program I had been using the two terms (select and activate)
interchangeably with respect to sheets. I guess that was a mistake,
and they really are not interchangeable. So I went through my program
and changed all the 'Selects' for sheets to 'Activate' just in case.
But I don't understand why this change worked?

The program was just starting up when it crashed, by the way, if that
helps.

Thanks in advance to anyone who could help me understand this. I
really appreciate it.

 

[Don Guillett Excel MVP]

I have a complex program that has been running fine on Excel 2003,
2007, and 2010. Today when running the program on Excel 2003 I got an
error messages and the program hung up on this statement;

ThisWorkbook.Sheets("Recap").Select

I think the error said something like 'Method Not Supported,' but I do
not remember exactly what it said.

I tried various things and could not figure out why it crashed.  Then
II replaced the statement with

ThisWorkbook.Sheets("Recap").Activate

and it ran without any problems.

I have run the program about a dozen times since the change, and it is
doing fine.  I just don't understand why it crashed in the first
place??

In my program I had been using the two terms (select and activate)
interchangeably with respect to sheets.  I guess that was a mistake,
and they really are not interchangeable.  So I went through my program
and changed all the 'Selects' for sheets to 'Activate' just in case.
But I don't understand why this change worked?

The program was just starting up when it crashed, by the way, if that
helps.

Thanks in advance to anyone who could help me understand this.  I
really appreciate it.

Google is your friend
http://www.google.com/search?source...T4GFRD_en___US219&q=excel:+select+vs+activate

 

[RJQMAN]

Google is your friendhttp://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GFRD_...- Hide quoted text -

- Show quoted text -

Hello Don, and thanks.

My problem is very similar to ones I read about from your reference.
So I am sort of 'interpolating' to understand the solution. After
reading multiple references on a dozen or so sites, the problem may
have occurred because I had another workbook open at the time. I am
not clear as to how the other workbook could have been 'active' when
the macro was triggered from a button in the workbook that had the
problem, though. But perhaps somehow it is possible.

What I have read says that I should 'activate' the workbook first in
VBA, but I do not know how to do that since the user(s) - including me
when I had the problem - will most likely have multiple copies of the
workbook on their computer under slightly different names, as they
save it during use. So the workbook name changes constantly.

If I understand it, though, if I use the 'activate' command for
sheets, I will be out of trouble no matter if another workbook is open
or not on the user's computer. I am self-taught and have no one else
to ask when I have a question except this (or perhaps some other)
usergroup. Without the help of this group, I would never have been
able to write the program in the first place. I really do appreciate
your help very, very much.

My question is, is my understanding correct? Will using the
'activate' command where I had been using the 'select' command trying
to move to a new sheet be problem free? Thanks again...

 

[norie]

Actually the best way to avoid problems like you describe is not to
use Select/Activate if you can.

It's almost never needed to use them.

Instead of them try creating references to the worksheets and
workbooks you are working with.

eg

Dim wbThis As Workbook
Dim wbOpen As Workbook

Dim wsDst As Worksheet
Dim wsSrc As Worksheet

Set wbThis = ThisWorkbook ' create reference to the workbook the code
is in

set wsDst = wbThis.Worksheets("Consolidation") ' create a reference to
the worksheet 'Consolidation' in the workbook the code is in

Set wbOpen = Workbooks.Open("C:\AWorkbook.xls") ' open a workbook and
create a reference to it

Set wsSrc = wbOpen.Worksheets("Data") ' create a reference to the
workshee 'data' in the workbook that's been opened

You can now use these references in later code, and they will refer to
the worksheets/workbooks you've set them too regardless of what
worksheet/workbook is active.

 

[RJQMAN]

Actually the best way to avoid problems like you describe is not to
use Select/Activate if you can.

It's almost never needed to use them.

Instead of them try creating references to the worksheets and
workbooks you are working with.

eg

Dim wbThis As Workbook
Dim wbOpen As Workbook

Dim wsDst As Worksheet
Dim wsSrc As Worksheet

Set wbThis = ThisWorkbook ' create reference to the workbook the code
is in

set wsDst = wbThis.Worksheets("Consolidation") ' create a reference to
the worksheet 'Consolidation' in the workbook the code is in

Set wbOpen = Workbooks.Open("C:\AWorkbook.xls") ' open a workbook and
create a reference to it

Set wsSrc = wbOpen.Worksheets("Data") ' create a reference to the
workshee 'data' in the workbook that's been opened

You can now use these references in later code, and they will refer to
the worksheets/workbooks you've set them too regardless of what
worksheet/workbook is active.

Thank your for your help. I am not ignoring your answer, but I am
going to need to study your suggestion for a bit to try to understand
it. Once I grasp the concept, I will give it a try. I appreciate it.

 

[Ron Rosenfeld]

Thank your for your help. I am not ignoring your answer, but I am
going to need to study your suggestion for a bit to try to understand
it. Once I grasp the concept, I will give it a try. I appreciate it.

A question to ask yourself, which may help in understanding the
concept, is to consider "why" you wish to select or activate a
particular cell or worksheet. If it is to either obtain data from, or
write data to a cell, there is no reason to either activate or select.

 

[RJQMAN]

A question to ask yourself, which may help in understanding the
concept, is to consider "why" you wish to select or activate a
particular cell or worksheet.  If it is to either obtain data from, or
write data to a cell, there is no reason to either activate or select.

Many times I just want to display a particular worksheet so that the
user can enter data on that sheet or read the results that are
displayed on that sheet. I am not sure how to structure a phrase
which says - take the user to the sheet marked 'Recap' and display
that sheet without using the 'select' or 'activate' structure. But I
am experimenting, and trying to find the answer. Perhaps you can save
me a few hours if you can tell me how to do this. I am experimenting
now trying to see how to follow the suggestion.

Other times, of course, I want to access a cell on a sheets or perform
some other action with data on that sheet. I think I can figure that
out.

 

[Ron Rosenfeld]

Many times I just want to display a particular worksheet so that the
user can enter data on that sheet or read the results that are
displayed on that sheet.

That is a case where I would use Activate.

I would use Activate rather than Select so as to specify the active
cell, and have that in view and ready for input.

e.g.

Worksheets("Sheet1").Range("z9").Activate

 

[Pat Quatman]

That is a case where I would use Activate.

I would use Activate rather thanSelectso as to specify the active
cell, and have that in view and ready for input.

e.g.

Worksheets("Sheet1").Range("z9").Activate

Thanks. I could not figure out any other way to do it. But should I
not use the reference to the sheet that I named to minimize risk of
problems? Based on the example norie was kind enough to prepare for
me, my code statement that opens the sheet for data entry should then
look like this? Or does it not matter...

Public WBThis as Workbook
Public wsDst as WorkSheet
_______________________________

Sub OpenWorkbook() ' That runs when the workbook opens

Set WBThis=This Workbook
Set WsDst = wbThis.Worksheets("Consolidation")

End Sub

' This is just a sample for explanation - this concept is that this
runs when the user clicks on a Button

Sub PickAPage ()

WbThis.Worksheets("WsDst").Range("z9").Activate

End Sub

' -------------------------

Rather than

' This is what I had been doing that seemed to cause the problem - a
snippet for sample purposes of course.

Sub PickAPage ()

ThisWorkbook.Worksheets("Consolidation").Select
Range("z9").Activate

End Sub

' -------------------------
or

This was my first attempt at a solution, which seemed to work at the
moment anyway - not sure about the long term though

Sub PickAPage ()

ThisWorkbook.Worksheets("Consolidation").Activate

Range("z9").Activate

End Sub

Do I understand correctly? I don't want to mess up again. I really
appreciate the help more than I can say.

 

[Ron Rosenfeld]

Rather than

' This is what I had been doing that seemed to cause the problem - a
snippet for sample purposes of course.

Sub PickAPage ()

ThisWorkbook.Worksheets("Consolidation").Select
Range("z9").Activate

End Sub

' -------------------------
or

This was my first attempt at a solution, which seemed to work at the
moment anyway - not sure about the long term though

Sub PickAPage ()

ThisWorkbook.Worksheets("Consolidation").Activate

Range("z9").Activate

End Sub

I don't understand why your first example should have been causing
problems, unless perhaps the worksheet was not a a worksheet, but
rather a chart sheet.

For data input, I would
select the proper worksheet
activate the cell
scroll the cell to the top left corner.

e.g:

With Worksheets("sheet2")
.Select
.Range("z9").Activate
End With
Application.Goto ActiveCell, scroll:=True

But if this is for general usage, and not just personal usage, you
might find you have better control by having the data input on a user
form instead of directly on the sheet. You can probably make it look
better, also.

 

[FMBC Technical Director]

I don't understand why your first example should have been causing
problems, unless perhaps the worksheet was not a a worksheet, but
rather a chart sheet.

For data input, I would
        select the proper worksheet
        activate the cell
        scroll the cell to the top left corner.

e.g:

 With Worksheets("sheet2")
        .Select
        .Range("z9").Activate
    End With
    Application.Goto ActiveCell, scroll:=True

But if this is for general usage, and not just personal usage, you
might find you have better control by having the data input on a user
form instead of directly on the sheet.  You can probably make it look
better, also.- Hide quoted text -

- Show quoted text -

Thanks for the suggestion, although that might not be practical.
Perhaps I should explain a little more - although I think the problem
may be resolved now. I sure hope so.

The user, who is the tabulator at a competitive event, inputs up to
ninety rows of data, which are scoring details from a marching band
conest - their input is the scores of the judges, and there are up to
12 judges at a contest, and our largest contests will have over 90
bands involved at four sites.

the bands that compete can compete in various classes (up to 10
classes, with a maximum of 20 - 30 bands in each class, depending on
the class). Therefore there are hundreds of lines for potential data
entry. The user tells the program which classes are active in their
contest, and how many bands in that class, and the program hides the
unused lines from the display.

The tabluator enters the scores over an 8-12 hour period as bands
compete. The program then calculates winners of individual captions,
ratings of bands in individual captions, class winners, individual
winners, and overall winners. At multi-stage events, it provides
printable output giving the names of the bands that move on to the
next stage of competition as well, in a quasi-narrative format. It
also outputs tables showing winners, rankings and ratings in
subcaptions, etc., etc. I had been developing it and refining it
since 2001, and had just completed it when Microsoft redesigned Excel.
I had to do a total rewrite, as I had been using drop-down menus that
would not have been visible to the casual user with Excel 2007. The
rewrite had a few bugs that I resolved in 2008-2009.

At this point it is about 10 megs in size, and seemed to be working
fine. Once again, I thought I was done until this strange behaviour
started about ten days ago.

We are in the heart of our season, which concludes just before
Thanksgiving, with multiple contests using the program every weekend.
It is critical that it not crash at a competitive event, as the
tabulator would have to spend a great deal of time with manual
calculations at a critical time at the event.

In response to your comment/question about why it failed, I hope it
does not fail with example 1 - I just put that into place last week
and the first test of the program with that configuration will be
tomorrow, Saturday, at several locations in Florida USA. The failure
was with example 2, which used the Select command - example 1 was my
attempt at a fix, a fix that I was not certain would work.

I am afraid a userform approach would not be appropriate because the
users like to look at their entries in a traditional table form as
they enter them. The way it is set up now works very well, when it
works, and it is traditional as far as the user can see entering
data. So my only question was if it would be less likely to crash by
using the references...we will see what the weekend brings.

Thank you so much for your help.

 

[norie]

I don't understand why your first example should have been causing
problems, unless perhaps the worksheet was not a a worksheet, but
rather a chart sheet.

For data input, I would
        select the proper worksheet
        activate the cell
        scroll the cell to the top left corner.

e.g:

 With Worksheets("sheet2")
        .Select
        .Range("z9").Activate
    End With
    Application.Goto ActiveCell, scroll:=True

Why not just use this?

Application.Goto Worksheets("Sheet2").Range("Z9"), Scroll:= True

A workbook reference can be added too in case there is more than
workbook open.

 

[RJQMAN]

Why not just use this?

Application.Goto Worksheets("Sheet2").Range("Z9"), Scroll:= True

A workbook reference can be added too in case there is more than
workbook open.- Hide quoted text -

- Show quoted text -

I am self-taught, and I did not know I could do that. I have never
used the GoTo function. I will give it a try. Thanks very much.