P
Phrank
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
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