Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 24, 11:19*pm, RJQMAN wrote:
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?sourcei...+vs+acti vate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 25, 8:20*am, Don Guillett Excel MVP
wrote: On Oct 24, 11:19*pm, RJQMAN wrote: 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 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 26, 10:58*am, norie wrote:
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Tue, 26 Oct 2010 21:53:27 -0700 (PDT), RJQMAN
wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 27, 1:18*pm, Ron Rosenfeld wrote:
On Tue, 26 Oct 2010 21:53:27 -0700 (PDT), RJQMAN wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Thu, 28 Oct 2010 18:54:48 -0700 (PDT), RJQMAN
wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 28, 10:17*pm, Ron Rosenfeld wrote:
On Thu, 28 Oct 2010 18:54:48 -0700 (PDT), RJQMAN wrote: Many times I just want to display a particularworksheetso 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 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Thu, 28 Oct 2010 20:52:07 -0700 (PDT), Pat Quatman
wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 29, 7:23*am, Ron Rosenfeld wrote:
On Thu, 28 Oct 2010 20:52:07 -0700 (PDT), Pat Quatman wrote: 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.- 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 29, 12:23*pm, Ron Rosenfeld wrote:
On Thu, 28 Oct 2010 20:52:07 -0700 (PDT), Pat Quatman wrote: 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 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the difference between 'Select' a sheet and 'Activate' a sheet
On Oct 29, 6:47*pm, norie wrote:
On Oct 29, 12:23*pm, Ron Rosenfeld wrote: On Thu, 28 Oct 2010 20:52:07 -0700 (PDT), Pat Quatman wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select / Activate last Sheet | Excel Programming | |||
Activate vs Select a sheet | Excel Programming | |||
Select a sheet and activate a Window | Excel Programming | |||
Difference between Select and Activate | Excel Programming | |||
Difference between .Activate and .Select | Excel Programming |