Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveCell in Another Sheet (XP)

    Can one reference an active cell from another sheet via code?

    Example: Msgbox Sheets("Sheet2").Range("ActiveCell")

    When you're in Sheet1 run the code and the active cell in Sheet2 is Cell B5 and B5 = 45 one would expect the msgbox to return 45.

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ActiveCell in Another Sheet (XP)

    But there can be only one ActiveCell at any time. So you'll have to:

    WorkSheets("Sheet2").Activate
    MsgBox Activewindow.ActiveCell.Value2

    However, if you can describe what you are doing there may be a better way; I'd prefer not to retrieve a value this way, I'd use a named range or another approach.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    John,

    Your answer is what I expected although I thought the cell in Sheet2 may have been referred to as something else.

    My workaround was to declare public variables in the code using the ActiveCell.Offset functionaility and passing the variables to other parts of the code.

    Thanks for replying.
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    JohnBF,

    Although my solution does resolve my challenge. I was wondering if there is something similar to:

    Do Until IsEmpty(Active) 'This line pertains to sheet1 when the active cell is A1
    'execute some code here
    Loop

    for testing in sheet2 while remaining in sheet1. Something like:

    Do until IsEmpty(Sheets("Sheet2").Range("a1"))
    ' execute some code here
    Loop

    Regards,
    John

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    The code you posted works fine for me. What problem are you having with it?
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    Legare,

    If the code associated with sheet1 is:
    Do Until IsEmpty(ActiveCell)
    'excecute some code
    ActiveCell.offset(1,0).select
    Loop

    I was trying to come up with an code for sheet2 without actually having to select or going to sheet2.

    John

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    Isn't that what your code (below) does? It does not select or "go to" Sheet2. Am I missing something here?

    <pre>Do until IsEmpty(Sheets("Sheet2").Range("a1"))
    ' execute some code here
    Loop
    </pre>


    If you are wanting to check the active cell on sheet2 without activating sheet2, I think that someone has already said that can not be done. There is no active cell on a sheet that is not active. There is only one active cell at a time and that is on the active sheet.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    Legare,

    I agree with you in concept that the code should work. If sheet2 cell A1=1, A2=2 and A3=3 one would think that the code would test Sheet2.Range("A1"), loop to Sheet2.Range("A2"), loop to Sheet2.Range("A3") and conclude upon finding the empty cell.

    I guess a more practical way would be for me to create a range in Sheet2 such as:

    Sub Test()
    For Each oCell In Range("MyRange")
    'execute some code here
    MsgBox oCell 'Note that the contents of the message box changes with each pass
    Next oCell
    End Sub


    I guess I anticipated the following to work in the same manner:

    Do until IsEmpty(Sheets("Sheet2").Range("a1"))
    ' execute some code here
    Loop


    John

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    Code does not work like copying a formula on the worksheet, it does not automatically go to the next cell (in your example, there is no way Excel could know if the next cell after A1 is A2 or B1). You have to write the code to make it do this. You could use something like this:

    <pre>Dim I As Long
    I = 0
    Do Until IsEmpty(Worksheets("Sheet2").Range("A1").Offset(I, 0))
    'Do some code
    I = I + 1
    Loop
    </pre>

    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveCell in Another Sheet (XP)

    Thanks Legare

    Amazing how this thread got soooo long for something so simple.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •