Setting object variable to activecell 2

[SgtPepps]

I've used the code:

Set MyRange = Worksheets("MySheet&quot.ActiveCell

As far as i'm aware this should work, does anyone know why it doesn't?

Regards

Mike

 

[xlbo]

Activecell doesn't have a worksheet prefix as there can only be ONE activecell at any one time

Set myRange = Activecell

will work just fine Rgds
~Geoff~

 

[VBslammer]

The ActiveCell property only works with the 'Window' object, and doesn't allow you to specify a sheet name. In fact, you can use ActiveCell without any qualifiers to return the ActiveWindow's ActiveCell. However, it can refer to the active cell on any sheet by activating the sheet before before using ActiveCell, for example:

Sheets("Accounting Errors").Activate
Set myRange = ActiveCell

Here are a couple of functions to play with:

Function ActiveSelectionRange(ByVal strSheet As String) As Range
  Sheets(strSheet).Activate
  Set ActiveSelectionRange = Selection
End Function

Function ActiveCellRange(ByVal strSheet As String) As Range
  Sheets(strSheet).Activate
  Set ActiveCellRange = ActiveCell
End Function

VBSlammer

Unemployed in Houston, Texas

 

[SgtPepps]

Cheers Guys, All very helpfull!

Regards

SgtPepper