Excel - which worksheets are active

[stroyer74]

I am looping through all the worksheets in a spreadsheet and want to determine if the worksheet is active or not - active being the worksheet tab being highlighted. Anyone have a suggestion for me?

---
Sub TestFindActiveWorksheets()
Dim objCurrentSheet as Worksheet
Dim x as Integer

For x = 1 to ActiveWorkbook.Worksheets.Count
Set objCurrentSheet = ActiveWorkbook.Worksheets(x)
if objCurrentSheet.{not sure what to put here} = ? then
msgbox objCurrentSheet.Name
end if
next x
End Sub
---

Thanks in advance,
Sheldon

 

[Zathras]

You can use either ActiveSheet.Index or ActiveSheet.Name

 

[stroyer74]

Zathras,

Thanks for the quick response. Using the ActiveSheet works, but it only lists the sheet that is currently on top. If I select multiple sheets by holding CTRL while selecting them, that would mean that I have more than one active sheet - but using ActiveSheet only shows me one worksheet. Am I missing something?

Sheldon

 

[PHV]

You have multiple selected sheets but only one active.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[stroyer74]

OK - that makes sense. When I select multiple pages, then choose to print, one of my options is to print "Active sheet(s)". I was assuming the terminology would be the save in VBA.

Is there a way to determine if a sheet is selected or not? Something like

if objCurrentSheet.IsSelected = True then
...
end if


I have been looking online and through the help, but am not finding anything like that.

Sheldon

 

[PHV]

Browse the ActiveWindow.SelectedSheets collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[stroyer74]

That's it! Thanks a million, PH.

Sheldon