CODEWITHSUNDEEP
excelvba
StoneGiant
StoneGiant

Reputation: 1497

How Do I Access the Active Cell in a Non-active Sheet?

As I flip through the tabs of an Excel (Office 365 MSO 16.0.12527.20880 64-bit) workbook, I can see that Excel remembers the most recently selected cell in each sheet.

Can I access this in VBA? I know I can use

myString = ActiveCell.Text

to get the active cell on the active sheet. But, I'm thinking that, since Excel knows what cell is selected on the other sheet, I should be able to get it without activating it. Wouldn't this be nice?

myString = Sheets("OtherSheet").SelectedCell.Text

Any ideas?

Upvotes: 2

Views: 866

Answers (1)

Variatus
Variatus

Reputation: 14373

No. It wouldn't be nice because of the provisions I would have to make for there not having been any selection yet and/or the selection not being the one I let me code think it was. All of this changes when there is a controlled environment where I, say, select a cell on one sheet, switch sheets, and then want the contents of the just selected cell. In this kind of environment it's a lot easier to control the process.

  • Declare a public variable (perhaps a variant, string or a range)
  • In the Worksheet_Deactivate procedure, assign either the cell or its value to the variable.
  • Use the variable wherever you need it.

Upvotes: 1

Related Questions