Can one access the active cell of a non active sheet (without activating it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

There is no active cell if the sheet is not active.


"GerryGerry" wrote in message
...
Can one access the active cell of a non active sheet (without activating
it first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

Hi,

If a sheet is not active, no cells have focus, none are active, none are
selected. You cannot use those properties on an inactive sheet.

If you want the address of the cell that would be active if the sheet was
selected then you can do this

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Where = Selection.Address
Sheets("Sheet1").Select
MsgBox "The selected cell on Sheet2 is " & Where
Application.ScreenUpdating = True

With screenupdating being false you will not see sheet 2 being fleetingly
activated.

Mike

"GerryGerry" wrote:

Can one access the active cell of a non active sheet (without activating it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

You can access a cell or a range on an inactive (not hidden) sheet by
specific value. Assume Sheet1 is the active sheet:

Sheets("Sheet2").Range("B4") = ActiveSheet.Range("A1").Value

would put the value of A1 in the active sheet into cell B4 of sheet2 without
activating sheet2. You can do the same thing in reverse:

ActiveSheet.Range("A1") = Sheets("Sheet2").Range("B4").Value


"GerryGerry" wrote in message
...
Can one access the active cell of a non active sheet (without activating
it first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

Hi,

Thank you for this. You have understood exactly what I wanted although I did
use the wrong terminology. I was wondering whether this was achievable
without activating the sheet (even with screenupdating turned off). It seems
a little surprising to me that this info is not accessible directly somehow
through excels object model.

It's not just that the code is cumbersome, it's the fact that I can't loose
the focus of the current cell while obtaining values from the other
worksheets in my intended application.

Thanks to all
Gerry

"Mike H" wrote in message
...
Hi,

If a sheet is not active, no cells have focus, none are active, none are
selected. You cannot use those properties on an inactive sheet.

If you want the address of the cell that would be active if the sheet was
selected then you can do this

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Where = Selection.Address
Sheets("Sheet1").Select
MsgBox "The selected cell on Sheet2 is " & Where
Application.ScreenUpdating = True

With screenupdating being false you will not see sheet 2 being fleetingly
activated.

Mike

"GerryGerry" wrote:

Can one access the active cell of a non active sheet (without activating
it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

Hi,

I'm struggling to understand what value it brings knowing this about an
inactive sheet. If you want a value from a sheet you can get it without any
sheet\range selection and the same goes for writing a value.

Mike

"GerryGerry" wrote:

Hi,

Thank you for this. You have understood exactly what I wanted although I did
use the wrong terminology. I was wondering whether this was achievable
without activating the sheet (even with screenupdating turned off). It seems
a little surprising to me that this info is not accessible directly somehow
through excels object model.

It's not just that the code is cumbersome, it's the fact that I can't loose
the focus of the current cell while obtaining values from the other
worksheets in my intended application.

Thanks to all
Gerry

"Mike H" wrote in message
...
Hi,

If a sheet is not active, no cells have focus, none are active, none are
selected. You cannot use those properties on an inactive sheet.

If you want the address of the cell that would be active if the sheet was
selected then you can do this

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Where = Selection.Address
Sheets("Sheet1").Select
MsgBox "The selected cell on Sheet2 is " & Where
Application.ScreenUpdating = True

With screenupdating being false you will not see sheet 2 being fleetingly
activated.

Mike

"GerryGerry" wrote:

Can one access the active cell of a non active sheet (without activating
it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)

Why would changing sheets to find the activecell, then changing back to the
original worksheet cause a problem?

If you have events that do things, turn them off.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim StartingWks As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set StartingWks = ActiveSheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = ActiveSheet.Name Then
'skip it
Else
wks.Activate
MsgBox ActiveCell.Address(external:=True)
End If
Next wks

StartingWks.Activate

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

End Sub

GerryGerry wrote:

Hi,

Thank you for this. You have understood exactly what I wanted although I did
use the wrong terminology. I was wondering whether this was achievable
without activating the sheet (even with screenupdating turned off). It seems
a little surprising to me that this info is not accessible directly somehow
through excels object model.

It's not just that the code is cumbersome, it's the fact that I can't loose
the focus of the current cell while obtaining values from the other
worksheets in my intended application.

Thanks to all
Gerry

"Mike H" wrote in message
...
Hi,

If a sheet is not active, no cells have focus, none are active, none are
selected. You cannot use those properties on an inactive sheet.

If you want the address of the cell that would be active if the sheet was
selected then you can do this

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Where = Selection.Address
Sheets("Sheet1").Select
MsgBox "The selected cell on Sheet2 is " & Where
Application.ScreenUpdating = True

With screenupdating being false you will not see sheet 2 being fleetingly
activated.

Mike

"GerryGerry" wrote:

Can one access the active cell of a non active sheet (without activating
it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)




--

Dave Peterson

Thank you for this. I'll try it tommorow and let everyone know how it went.

"Dave Peterson" wrote in message
...
Why would changing sheets to find the activecell, then changing back to
the
original worksheet cause a problem?

If you have events that do things, turn them off.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim StartingWks As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set StartingWks = ActiveSheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = ActiveSheet.Name Then
'skip it
Else
wks.Activate
MsgBox ActiveCell.Address(external:=True)
End If
Next wks

StartingWks.Activate

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

End Sub

GerryGerry wrote:

Hi,

Thank you for this. You have understood exactly what I wanted although I
did
use the wrong terminology. I was wondering whether this was achievable
without activating the sheet (even with screenupdating turned off). It
seems
a little surprising to me that this info is not accessible directly
somehow
through excels object model.

It's not just that the code is cumbersome, it's the fact that I can't
loose
the focus of the current cell while obtaining values from the other
worksheets in my intended application.

Thanks to all
Gerry

"Mike H" wrote in message
...
Hi,

If a sheet is not active, no cells have focus, none are active, none
are
selected. You cannot use those properties on an inactive sheet.

If you want the address of the cell that would be active if the sheet
was
selected then you can do this

Application.ScreenUpdating = False
Sheets("Sheet2").Select
Where = Selection.Address
Sheets("Sheet1").Select
MsgBox "The selected cell on Sheet2 is " & Where
Application.ScreenUpdating = True

With screenupdating being false you will not see sheet 2 being
fleetingly
activated.

Mike

"GerryGerry" wrote:

Can one access the active cell of a non active sheet (without
activating
it
first) in VBA as well as the selected range?

any help much appreciated as always
(i'm using Excel 2003)




--

Dave Peterson