on 4/15/2011, Phrank supposed :
Hi,

I've got a workbook with one tab for data entry and the other tab for
data presentation (with imbedded formulas for %differences from
controls and such). Initially, both sheets are identical, but I
sometimes need to insert rows and columns. Rather than doing this
twice, I just select both tabs. The problem is that I (and others)
oftentimes forget to have both tabs selected during modification or
data entry, and we end up having to waste time fixing things. So, I
drafted a quick macro for the Worksheet_SelectionChange event that I
thought would work well, but I've got a snag. It reads the address of
the active cell, then checks the same cell in the 2nd sheet for the
formula. IF the formula starts a certain way (with =IF(ISERRO), then
I want the macro to end. If it doesn't start with that, then I want
the macro to force the selection of the second tab. But, it's failing
when it tries to check the same cell on the 2nd sheet. The macro
seems to work if I try it outside of the event routine, but when I put
it in the Worksheet, it fails. Can anyone give me advice on how to
fix and/or improve this please? Thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myActiveCell = ActiveCell.Address
Sheets("CTD").Activate
Range(myActiveCell).Activate ' IT'S FAILING RIGHT HERE. WHY?
myFormula = ActiveCell.FormulaR1C1
myStartFormula = Mid(myFormula, 1, 11)
If myStartFormula = "=IF(ISERROR" Then
Sheets("Data Entry").Select
GoTo Line1
Else

Sheets(Array("Data Entry", "CTD")).Select

End If

Line1:
End Sub

Try using the Worksheet_Change event instead. Though I feel you might
be better off putting code behind a custom menuitem so people can just
insert rows/columns based on a position of the active cell on either
sheet. Otherwise, you'll have to put code behind each sheet.

You could add custom menuitems to the right-click popup menu to make
the process more convenient. I'd use one menuitem for rows, another for
columns. If you're interested in this approach post back and say so and
I'll try to help you with it.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc