simple vb question

  • Thread starter TheObstacleIsThePath
  • Start date
T

TheObstacleIsThePath

How do I poll the number of the selected row on a non-selected
worksheet?

My crude attempt looks like this:

=Worksheets("caseload").Selection.Row
 
G

Gary''s Student

Bounce back and forth:

For example to get the row of the acitvecell on Sheet1 from any sheet:

Sub whichRow()
Set cSheet = ActiveSheet
Sheets("Sheet1").Activate
n = ActiveCell.Row
cSheet.Activate
MsgBox (n)
End Sub
 
D

Dave Peterson

Option Explicit
Sub testme()

Dim ActWks As Worksheet
Dim myRow As Long

Application.ScreenUpdating = False
Set ActWks = ActiveSheet
With Worksheets("CaseLoad")
.Select
myRow = Selection.Row
End With
ActWks.Select
Application.ScreenUpdating = True

MsgBox myRow

End Sub
 
T

TheObstacleIsThePath

The codes posted are exactly what I asked for. Thanks to both of
you..

I still have a problem in that the code is activated by a
Worksheet_Activate sub. The toggling between worksheets causes the
code to loop endlessly. I'm not sure how to set up a flag that will
only allow a single activation of the subroutine.

..Private Sub Worksheet_Activate()
Dim ActWks As Worksheet
Dim myRow As Long
Application.ScreenUpdating = False
Set ActWks = ActiveSheet
With Worksheets("CaseLoad")
.Select
selectedrow = Selection.Row
End With
updateflag = 1
ActWks.Select
Application.ScreenUpdating = True

Cells(4, 112).Value = Worksheets("caseload").Cells(selectedrow,
1).Value
End Sub

sorry to complicate a simple question.
 
G

Gary''s Student

The problem can be avoided by using the Deactivate Event rather than the
Activate Event.

Let's say that for each worksheet we have a Deactivate macro that just
records the address of the activecell in a public, static, string variable.

That way, any time a worksheet and been activated and then deactivated, any
other sheet will have direct access to the other sheet's most recent active
cell.

Thus you can avoid the "bouncing" behavior in my previous post.
 
T

TheObstacleIsThePath

The problem can be avoided by using the Deactivate Event rather than the
Activate Event.

Let's say that for each worksheet we have a Deactivate macro that just
records the address of the activecell in a public, static, string variable.

That way, any time a worksheet and been activated and then deactivated, any
other sheet will have direct access to the other sheet's most recent active
cell.

Thus you can avoid the "bouncing" behavior in my previous post.
Click to expand...

This is what I had before coming to the Newsgroup. The problem was
that the subroutine that kept track of the row was triggered every
time I moved to a new cell -- it made it cumbersome to navigate
because of the lag it created.
 
G

Gary''s Student

My mistake. You are correct, Deactivate does not run soon enough. You would
need the selection_change event.
 
Top