Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adv. Filter - Want to copy to inactive sheet | Excel Discussion (Misc queries) | |||
Advanced Filtering on an inactive sheet | Excel Programming | |||
Changing the focus in an inactive sheet. | Excel Programming | |||
Calling an inactive sheet... | Excel Programming | |||
How to get the activecell of inactive worksheet ? | Excel Programming |