Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 147
Default ActiveCell on inactive sheet

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.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default ActiveCell on inactive sheet

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default ActiveCell on inactive sheet

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default ActiveCell on inactive sheet

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default ActiveCell on inactive sheet

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 147
Default ActiveCell on inactive sheet

GS wrote:
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.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default ActiveCell on inactive sheet

GS wrote:
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.


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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 147
Default ActiveCell on inactive sheet

GS wrote:
GS wrote:
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.


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



  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default ActiveCell on inactive sheet

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adv. Filter - Want to copy to inactive sheet Maki Excel Discussion (Misc queries) 7 January 20th 10 01:59 PM
Advanced Filtering on an inactive sheet Spencer Excel Programming 2 April 23rd 07 01:10 AM
Changing the focus in an inactive sheet. AVR Excel Programming 3 January 31st 07 11:28 PM
Calling an inactive sheet... RJH Excel Programming 1 July 17th 06 04:23 AM
How to get the activecell of inactive worksheet ? steven[_2_] Excel Programming 5 April 18th 04 12:59 PM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"