Worksheets Active Cell

[DocAElstein]

Hi
This is possibly a bit fundamental, but it's niggling me a bit. It has done in the past, and it caught me out again recently so I thought I would chuck it out and see if anyone has any thoughts on it.
I think perhaps a simple answer might be just that there is no worksheets active cell. That might stand up in a court of law but I don’t buy it. We are left to figure things out ourselves sometimes with Microsoft, and can sometimes, even when we are not supposed to be able to…

So I think that Excel knows about an active cell in all its worksheets, because of something like this little demo…. Take the uploaded workbook, MeActiveStuff.xls, open it and run this first macro which is in it. I am suggesting that the macro "sort of" makes the active cell of the two worksheets.

Code: Select all

 ' first macro                                                                                             https://eileenslounge.com/viewtopic.php?f=30&t=40560
Sub MesActiveCell()
ThisWorkbook.Worksheets.Item(1).Activate
ActiveSheet.Range("B2").Activate ' Make the active cell in the first worksheet   B2
ThisWorkbook.Worksheets.Item(2).Activate
ActiveSheet.Range("A2").Activate ' Make the active cell in the second worksheet  A2

' This next code line would error moaning that the worksheet does not have an ActiveCell Method
'Debug.Print ThisWorkbook.Worksheets.Item(1).ActiveCell.Address

Debug.Print Application.ActiveCell.Address ' Returns $A$2, the address of the active cell in the second worksheet    $A$2
ThisWorkbook.Save
End Sub

Now close the workbook, then re open it, important: try not to select any worksheet or cell, and run this second macro below that is in it

Code: Select all

 ' second macro
Sub DoYaKnowWhatYaActiveCellsR()
ThisWorkbook.Worksheets.Item(1).Activate
Debug.Print Application.ActiveCell.Address ' Returns $B$2   (Ctrl+G from VB Editor to see Immediate window)
ThisWorkbook.Worksheets.Item(2).Activate
Debug.Print Application.ActiveCell.Address ' Returns $A$2
End Sub

That second macro tells me, sort of, what the active cell is of both worksheets, and it's what I think I made them to be, sort of.

What niggles me a bit, only a bit, I won't lose sleep over it, but just a personal preference is that I like to be explicit in coding as it helps me more to understand and remember what's going on.
I am thinking there is no real thing as an ActiveCell. There is an Application.ActiveCell which is taken if you are in a normal code module and are a bit un explicit and use ActiveCell. In such a case, ActiveCell defaults to the Application.Activecell cell which is equivalent to the active window active cell, in other words the one you are looking at. I am OK with that. It all ties up with what I think I know about Excel VBA.
I am thinking that if I am in a worksheets code module and use ActiveCell, then to follow the pattern of things in Excel VBA it should either error or default to the active cell of the worksheet. It does not error but it appears to default to the Application.ActiveSheet

ActiveCell is that cell that is active and can be seen, if there is one…??.
I am not too happy with that explanation that ActiveCell is generally to do with a cell actively seen to be, active, if there is one. For one reason because I never saw anything written to that effect, (well not until I just did write it). It sounds nice. I might even say it to someone like my ex-Mother in Law’s Brother sometime. (He’s mad anyway.) But I don’t think it's true. (Not that my ex-Mother in Law’s Brother is mad, he is. The statement that ActiveCell is that cell that is active and can be seen, if there is one, is not true, IMHO)
For example, consider the other uploaded workbook, Mappe1.xls
Another demo, if you feel so inclined: Open it, but be careful not to do anything at all with it, - don’t click anywhere on/ in it or move it or anything. Instead, activate the existing workbook MeActiveStuff.xls or any other workbook you happen to have open and if necessary move that workbook about or resize it etc. so that you cannot even see anything of Mappe1.xls . Make sure you activate / select any cell in MeActiveStuff.xls or any other workbook but not Mappe1.xls
Now run this third macro in MeActiveStuff.xls

Code: Select all

' third macro
Sub GetLaActiveCellThatAintAndCantBeSeen()
 MsgBox prompt:=Windows("Mappe1.xls").ActiveCell.Value
End Sub

That should tell you what the last cell was that I activated in workbook Mappe1.xls.

_.___________-

Not a major problem, - where it cropped up currently was where I had a coding in a worksheets code module which I was trying to tidy up a bit. Some range definitions I liked to tidy up by changing like Range("x__") to Me.Range("x__") and I wanted to similarly change some ActiveCell____ to Me.ActiveCell____

I am happy for now to define a range object variable early on with something like this
Me.Activate: Dim MeActiveCell As Range: Set MeActiveCell = ActiveCell
, then use that in place of any ActiveCells (by the way I am not a fan of doing the : colon thing to put everything on the same physical line, but I do it in some cases like this where I have niggly things I maybe did not want to, or things I think I should not have to do)

Any thoughts/ opinions on this one?


Alan

[snb]

Code: Select all

Sub M_snb()
   MsgBox Selection.Address(, , , True)
End Sub

instead of:

'Debug.Print ThisWorkbook.Worksheets.Item(1).ActiveCell.Address

[HansV]

As you have found, ActiveCell is not a property of the Worksheet object, nor is Selection.
Both are properties of the Window object, and of the Application object.

You can open multiple windows on a workbook, display a different worksheet in each window, and select a different range in each window.
And you can open multiple workbooks, each with its own collection of windows.

Each of these windows will have its own Selection and its own ActiveCell

Selection (= Application.Selection = ActiveWindow.Selection = Application.ActiveWindow.Selection) and ActiveCell (= Application.ActiveCell = ActiveWindow.ActiveCell = Application.ActiveWindow.ActiveCell) always refer to whichever window happens to be the the foremost window of Excel (which might be hidden behind other applications).

[DocAElstein]

Thanks Both, that’s given me some food for thoughts, …. that slightly (characteristically, Lol), snb open answer sent me into some thoughts in the Selection / Active direction and I almost replied, but Hans reply has helped me get into it a bit more easily, and answered some things I was going to ask, or search for, (and possibly helped me not go off in a few wrong wild directions) , so I am thinking again….

[DocAElstein]

Hi,
Some more thoughts…..

Code: Select all

Sub M_snb()
   MsgBox Selection.Address(, , , True)
End Sub

instead of:
'Debug.Print ThisWorkbook.Worksheets.Item(1).ActiveCell.Address

In terms of what is being discussed / thought about, at least by me**, in this Thread, MsgBox Selection.Address is something like MsgBox ActiveCell.Address
( The (, , , True) is similar to doing something like ThisWorkbook.Worksheets.Item(1) ) , but its only very very vaguely, related to what I was concerned with, probably just enough to mislead/ confuse me a bit more, Lol. It has something to do with specifying a place a bit more explicitly.
MsgBox ActiveCell.Address or MsgBox ActiveCell.Address(, , , True) are similar to doing Application.ActiveCell.Address or ActiveCell.Address in a worksheets code module, or anywhere else.
**I possibly did not explain so well what I was wanting.
In my first macro, MsgBox ActiveCell.Address(, , , True) won’t error, just as my Application.ActiveCell.Address did not error. It gets me no closer to getting at a Worksheets Active Cell, because….. )

…….Me.Selection won’t work anymore than Me.ActiveCell in a worksheet code module, because…. ActiveCell is not a property of the Worksheet object, nor is Selection.
Both are properties of the Window object, and of the Application object…..

_._________________________________


OK, I think I got it, or very close. (Doesn’t happen very often, Lol, at least not usually so quickly, :) )

I am happy with this, which comes very close to telling me everything I wanted to know, it's mostly what Hans said:

_.___________________

ActiveCell is not a property of the Worksheet object, nor is Selection.
Both are properties of the Window object, and of the Application object.
Selection returns the currently selected object. ActiveCell returns a Range object of the Selection if it can or errors if it can’t. (It takes the top left for a multi cell Selection, because it can, )
You can open multiple windows on a workbook, display a different worksheet in each window, and select a different range in each window, and you can open multiple workbooks, each with its own collection of windows. Each of these windows will have its own Selection and its own ActiveCell

Selection is a bit un explicit, it's really Application.Selection = ActiveWindow.Selection = Application.ActiveWindow.Selection, and ActiveCell is just as un explicit, its really Application.ActiveCell = ActiveWindow.ActiveCell = Application.ActiveWindow.ActiveCell. They both refer to whichever window happens to be the foremost window of Excel (which might be hidden behind other applications. )
You can refer to a specific window, by its Caption Property, which is what I was doing in my third macro.

_._______________


Coming to the specific niggle related to ActiveCell that annoyed me recently. - It was wanting to do something along the lines of Me.ActiveCell rather than leave it less explicit at ActiveCell in a worksheets code module. These would be one way to do it less un explicitly.
Application.Windows("Book.xls").ActiveCell
Apllication.Workbooks("Book.xls").Windows("Books.xls").ActiveCell
Because I am in Excel, the Application I am interested in, I am happy to do this instead
Windows("Book.xls").ActiveCell
Workbooks("Book.xls").Windows("Books.xls").ActiveCell

However, I am not happy with that. Because it's not getting me tied into the worksheet with that reference. The window is referring to the workbook, not any worksheet in particular.
I am almost back to my original problem. The information I want is there, that information being the last cell that was selected in a worksheet. But I am not going to get it via something before ActiveCell like this
SomethingBefore.ActiveCell since ….. that is going to get me ….. whichever window happens to be the foremost window of Excel

But I am more happy now that I know much better what is going on.
:)

Maybe,for now, instead of this
Me.Activate: Dim MeActiveCell As Range: Set MeActiveCell = ActiveCell
, I will do this

Code: Select all

Me.Activate: Dim MeActiveCell As Range: Set MeActiveCell = Workbooks(Me.Parent.Name).Windows(Me.Parent.Name).ActiveCell ' https://eileenslounge.com/viewtopic.php?p=313746#p313746 

, then everything is crystal clear, :) ,




_._____________

I always was a bit suspect of the ActiveCell, at least in the way I mostly seen it used. I expect a lot of people using it are, like I was until today, thinking it's more directly to do with and part of Excel then it really is.
A better way for a Layman or beginner to be told about it is perhaps to say it’s something to do with getting at an Excel thing from something else. It will have its uses, but it’s probably best to avoid it initially, if you can.

It is convenient to use it get a quick way of telling a macro where you might want somethhing done. That sort of lures you into both using it and into thinking mistakingly it's a property of a worksheet.


Perhaps I was mistaking an ActiveCell for each worksheet with some record Excel keeps in a workbook file about the last selection used in every worksheet, and as far as I know, we don’t have access to that information other than in the indirect way my second macro was getting at that information: Naively I was thinking I could get at it via Me.ActiveCell
Perhaps if it had crossed the mind of someone at Microsoft at the time they might have done something along those lines, - something of the form of a Workbooks method to get the last selection of a worksheet.

[HansV]

If you have opened only one window on the workbook, you can also use

Workbooks("Book.xls").Windows(1).ActiveCell

[DocAElstein]

< ...You can open multiple windows on a workbook.....
........ If you have opened only one window on the workbook, you can also use
Workbooks("Book.xls").Windows(1).ActiveCellInteresting. > - Interesting....
I had a feeling there where multiple window possibilities around, and it scared me a bit for a couple of reasons:
_ one being I figured it would be something that I would easily get in a muddle with;
_ the other reason being that how things can or do get displayed in multiple windows or multiple instances is a bit controversial I think

Nevertheless, I took the plunge, and did try for the first time in my life to get a multiple workbook window. It was quite easy, (I did it manually, - there are plenty of simple tutorials on the internet to show you how), for example, with my first sample file, MeActiveStuff.xls , I ended up with these 2 windows after a couple of clicks
MeActiveStuff.xls:1
and
MeActiveStuff.xls:2
The results because of window instance politics can look a bit different

XL2010-XL2013 Workbook Windows.JPG

I took a closer look with some coding , the important TLDR Results are that the window Item number is reflecting the order of a ActiveCell being made, - you can see the order has swapped around in those results after I did things in a different order. That can perhaps be related vaguely to Item number order in worksheets: If you remade the first worksheet or swapped around physically the tabs, you see a similar change in the Item number.
In other words, the Window with the caption name and the ActiveCell is fixed, but the window item number can change: The item number is somehow related to the order of the things, the item number is not a number like a serial number/ string name using number characters which is tied to the window in the way that the window caption name and the ActiveCell is

So finally I think it is a bit too confusing for me to want to dabble very often with more than one window on the workbook. But at the same time it has helped me to get the thing a bit clearer, and so I think I will then go for this

Code: Select all

Me.Activate: Dim MeActiveCell As Range: Set MeActiveCell = Workbooks(Me.Parent.Name).Windows.Item(1).ActiveCell ' https://eileenslounge.com/viewtopic.php?p=313747#p313747

Somehow that does look a little better and rolls off my tongue that little bit subtly better than the last one and I think in my brain memory system will help me remember what it’s all about. Then I am happy I know about it enough not for it to annoy me quite so much. I gots a nice understanding now on this one, :)

Instead of saying in future
"click on a cell in the worksheet" ,
I will say something like,
"make the window showing your workbook active, and if necessary activate the worksheet we are interested in, so as to see it. Now, in that window, select the cell top left, ( or the full rectangular area of cells of**), where you would like the results to end up in the worksheet. That way we can conveniently use that window's ActiveCell in our coding which will give VBA the range object of the top left cell so it knows from where to start putting our results.
(** If you choose to make a multicell selection in the workbooks window, make sure you start at where you would like the top left of the results , since the start point of your selection will be thereafter that window's ActiveCell ) "

[snb]

You can select several ranges in separate worksheets and show them with the code I posted.
So you can see all 'activecells' in separate workbooks, that's where , , , True comes in.

[DocAElstein]

I still haven't a clue what you're trying to say, or trying to not say,
Is it that Rätsel game again, - Guess what snb’s on about in 20 posts? ,
What’s the next clue?