ActiveCell on inactive sheet

[witek]

is there a way to find out which cell will be ActiveCell when sheet is
selected without selecting it?

Each sheet remebers last position of active cell so when you return to
that sheet the same cells becomes active.
I need to know the address of last active cell on each sheet.


it is probably even more complicated because as I see each window of
the same document can have different activecell on the same sheet.

 

[isabelle]

hi,

this isn't possible, but it is possible to select a sheet without that
being visible

Sub Macro1()
Dim sh As String
sh = ActiveSheet.Name
Application.ScreenUpdating = False
Sheets("Sheet2").Select
x = ActiveCell.Address
Sheets(sh).Select
Application.ScreenUpdating = True
MsgBox x
End Sub

isabelle

Le 2013-09-19 15:13, witek a écrit :

 

[GS]

is there a way to find out which cell will be ActiveCell when sheet

is selected without selecting it?

Each sheet remebers last position of active cell so when you return
to that sheet the same cells becomes active.
I need to know the address of last active cell on each sheet.


it is probably even more complicated because as I see each window of
the same document can have different activecell on the same sheet.

ActiveCell is a property of the worksheet and so can be accessed
without selection same as any other property...

Dim rngActive As Range, sAddress$
Set rngActive = Sheets("Sheet1").ActiveCell
sAddress = rngActive.Address
or
sAddress = Sheets("Sheet1").ActiveCell.Address

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops! Wrong again. I'm confusing Excel behavior with my ActiveX
spreadsheet component yet again! Sorry about that...

In Excel, ActiveCell is a member of Window and so must be the active
sheet to use ActiveCell as I suggested.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi,

Am Thu, 19 Sep 2013 14:13:17 -0500 schrieb witek:

is there a way to find out which cell will be ActiveCell when sheet is
selected without selecting it?

no, but you can screenupdating to false

Sub Addresses()
Dim i As Integer
Dim varAdr() As String
Dim shName As String

shName = ActiveSheet.Name
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
ReDim Preserve varAdr(Sheets.Count - 1)
Sheets(i).Select
varAdr(i - 1) = ActiveCell.Address(0, 0)
Next
Sheets(shName).Select
Application.ScreenUpdating = True
For i = LBound(varAdr) To UBound(varAdr)
MsgBox varAdr(i)
Next
End Sub


Regards
Claus B.

 

[witek]

Oops! Wrong again. I'm confusing Excel behavior with my ActiveX
spreadsheet component yet again! Sorry about that...

In Excel, ActiveCell is a member of Window and so must be the active
sheet to use ActiveCell as I suggested.

Yes, but excel somehow remembers what cell is (was) active on each sheet.
I thought that it is accessible.

Thanks anyway.

 

[GS]

Yes, but excel somehow remembers what cell is (was) active on each
sheet.
I thought that it is accessible.

Thanks anyway.

IMO, it should be available whether it's the active sheet or not. I
guess there's gonna be lots of things about Excel that the MS team
isn't going to fix (not that this is actually broken), but ActiveCell
belongs to the sheet (technically speaking) and so I don't see why we
can't access it like any other range!!!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[witek]

IMO, it should be available whether it's the active sheet or not. I
guess there's gonna be lots of things about Excel that the MS team isn't
going to fix (not that this is actually broken), but ActiveCell belongs
to the sheet (technically speaking) and so I don't see why we can't
access it like any other range!!!<g>

MS assumed that everything "Active..." is a global resource and only one
unique instance exists.

So there is only one activeheet, activecell, etc.

I understand that but they could expose more sheet local properties.

The same applies to selection object.
You can leave some cells selected on sheet but if sheet in not active
you are unable to read what is selected.



That is what I did

' sheet1 worksheet module

Dim m_selection As Range
Dim m_activecell As Range

Property Get SheetSelection() As Range
Set SheetSelection = m_selection
End Property

Property Get SheetActiveCell() As Range
Set SheetActiveCell = m_activecell
End Property

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set m_selection = Selection
Set m_activecell = ActiveCell
End Sub

See that worksheet_deactivate is too late to use.
ActiveCell and Selection already point to new worksheet.

You can do that at workbook level and remember state on any sheet if
needed.


Now you can use it in any module

Sub test()
Dim s As Range
Set s = Sheet1.SheetSelection
Dim ac As Range
Set ac = Sheet1.SheetActiveCell
End Sub

 

[GS]

Yes, very clever idea. Lots of code for a wkb with many sheets so lots
of copy/paste or use a template with code in place for making new
sheets.

That's a lot simpler to impliment than what I used to do. I didn't say
anything because it's fairly complex in that it involved storing
Selection.Address and ActiveCell.Address as a delimited string in a
local scope defined name for each sheet via its _SelectionChange event
just as you did. I just accessed this for any sheet by evaluating the
defined name...

Dim vSetting, Wks As Worksheet
Set Wks = Sheets(<index>)
On Error Resume Next '//if name doesn't exist
vSetting = Wks.Names("uiSelection").RefersTo
If Not (vSetting = Empty) Then vSetting = _
Application.Evaluate("'" & Wks.name & "'!uiSelection")

...so I could then use that however I wanted. ActiveCell would then
be...

Dim rngActive As Range, rngSelected As Range
vSetting = Split(vSetting, "~")
Set rngSelected = Range(vSetting(0))
Set rngActive = Range(vSetting(1))

...or just use the address with Range() if I didn't need an object var.

Like I said.., your idea is simpler. For me it was just another
uiSetting stored in a hidden defined name and so the mechanism to
create and use this concept is already in place in most of my projects
by default. (What I've shown above is merely a small snippet of the
entire concept)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion