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

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

I tried the Worksheet_Change event, and that's no good because it
initiated the macro every time I even selected a cell. There are only
two sheets in each workbook. ...well, there's actually a 3rd worksheet
(Notes) with notes as to what to do under certain circumstances. This
3rd sheet is helpful, too, with my code, because if for some reason
someone wants to access the CTD sheet, all they need to do is click on
the Notes tab to deselect the other two tabs, then the can select the
CTD tab alone. That part works well. I just need it to be able to
look at the analagous cell in the 2nd tab and press on with the macro.
For some reason, it's working when tested outside of the
Worksheet_SelectionChange event, but not working inside the event.
Thanks.

Frank


On Fri, 15 Apr 2011 19:52:50 -0400, GS wrote:

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.

It is good practice to use Option Explicit and declare all variables.
In worksheet event code "Target" is the active cell.
Qualify all ranges with the parent sheet (and the workbook if there is more than one).
The following code is untested but should be close to what you want...
'---
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myActiveCell As String
Dim myFormula As String

myActiveCell = Target(1).Address(False, False)
myFormula = Sheets("CTD").Range(myActiveCell).FormulaR1C1
If InStr(1, myFormula, "=IF(ISERROR", vbTextCompare) Then
Sheets("Data Entry").Select
Else
Sheets(Array("Data Entry", "CTD")).Select
End If
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Determine Colors (excel add-in) - in the free folder)




"Phrank"
wrote in message
...
I tried the Worksheet_Change event, and that's no good because it
initiated the macro every time I even selected a cell. There are only
two sheets in each workbook. ...well, there's actually a 3rd worksheet
(Notes) with notes as to what to do under certain circumstances. This
3rd sheet is helpful, too, with my code, because if for some reason
someone wants to access the CTD sheet, all they need to do is click on
the Notes tab to deselect the other two tabs, then the can select the
CTD tab alone. That part works well. I just need it to be able to
look at the analagous cell in the 2nd tab and press on with the macro.
For some reason, it's working when tested outside of the
Worksheet_SelectionChange event, but not working inside the event.
Thanks.
Frank

Phrank wrote :
I tried the Worksheet_Change event, and that's no good because it
initiated the macro every time I even selected a cell. There are only
two sheets in each workbook. ...well, there's actually a 3rd worksheet
(Notes) with notes as to what to do under certain circumstances. This
3rd sheet is helpful, too, with my code, because if for some reason
someone wants to access the CTD sheet, all they need to do is click on
the Notes tab to deselect the other two tabs, then the can select the
CTD tab alone. That part works well. I just need it to be able to
look at the analagous cell in the 2nd tab and press on with the macro.
For some reason, it's working when tested outside of the
Worksheet_SelectionChange event, but not working inside the event.
Thanks.

Frank

Both events fire whenever cells are selected. I really don't think this
is where you want to put this code. I reiterate the idea of putting it
in a standard module and running it from a menuitem on the right-click
popup menu. This, then, would work since you claim it works outside
either of those events.

So.., do you want help to do this OR do you want to fiddle with it a
bit more?

--
Garry

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

That works great!! Thank you so much!

For Garry, the reason I want it in an event is because I and others
periodically forget to select both tabs, which leads to extra work.
I'm just as likely to forget to right click and run this from a menu
item as I am to actually select both tabs. ...unless you were
thinking of something else. The automation of the procedure is what
I'm after, and Jim's fixing of my code seems to do the trick.

Thanks!

Frank

On Fri, 15 Apr 2011 20:05:33 -0700, "Jim Cone"
wrote:

It is good practice to use Option Explicit and declare all variables.
In worksheet event code "Target" is the active cell.
Qualify all ranges with the parent sheet (and the workbook if there is more than one).
The following code is untested but should be close to what you want...
'---
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myActiveCell As String
Dim myFormula As String

myActiveCell = Target(1).Address(False, False)
myFormula = Sheets("CTD").Range(myActiveCell).FormulaR1C1
If InStr(1, myFormula, "=IF(ISERROR", vbTextCompare) Then
Sheets("Data Entry").Select
Else
Sheets(Array("Data Entry", "CTD")).Select
End If
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Determine Colors (excel add-in) - in the free folder)




"Phrank"
wrote in message
.. .
I tried the Worksheet_Change event, and that's no good because it
initiated the macro every time I even selected a cell. There are only
two sheets in each workbook. ...well, there's actually a 3rd worksheet
(Notes) with notes as to what to do under certain circumstances. This
3rd sheet is helpful, too, with my code, because if for some reason
someone wants to access the CTD sheet, all they need to do is click on
the Notes tab to deselect the other two tabs, then the can select the
CTD tab alone. That part works well. I just need it to be able to
look at the analagous cell in the 2nd tab and press on with the macro.
For some reason, it's working when tested outside of the
Worksheet_SelectionChange event, but not working inside the event.
Thanks.
Frank

Phrank expressed precisely :
For Garry, the reason I want it in an event is because I and others
periodically forget to select both tabs, which leads to extra work.
I'm just as likely to forget to right click and run this from a menu
item as I am to actually select both tabs. ...unless you were
thinking of something else. The automation of the procedure is what
I'm after, and Jim's fixing of my code seems to do the trick.

Thanks!

Frank

I'm glad Jim was able to help you. My thinking was that SINCE YOU MUST
USE A MENU FROM SOMEWHERE (menubar or cell popup) to insert rows/cols
then why not 'train' yourself and your users to use your own custom
menus for automated tasks.

Using event code constantly adds to performance overhead; using a
menuitem only adds when needed!

--
Garry

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

Oh, ok, I'm following you now. If all I was looking at was to make
sure that the same row or column was inserted in both sheets, then I
think you are right that this would work. However, I need to ensure
that EVERYTHING (formatting AND data entry) that is done on the data
entry sheet is done on the final product sheet. So I'm looking for it
to automatically select the sheets. I do greatly appreciate the
suggestion and your time.

Frank

On Sat, 16 Apr 2011 13:36:39 -0400, GS wrote:

Phrank expressed precisely :
For Garry, the reason I want it in an event is because I and others
periodically forget to select both tabs, which leads to extra work.
I'm just as likely to forget to right click and run this from a menu
item as I am to actually select both tabs. ...unless you were
thinking of something else. The automation of the procedure is what
I'm after, and Jim's fixing of my code seems to do the trick.

Thanks!

Frank

I'm glad Jim was able to help you. My thinking was that SINCE YOU MUST
USE A MENU FROM SOMEWHERE (menubar or cell popup) to insert rows/cols
then why not 'train' yourself and your users to use your own custom
menus for automated tasks.

Using event code constantly adds to performance overhead; using a
menuitem only adds when needed!

Hi again,

Is there a way to tweak this macro so that the formatting in the cell
on the CTD sheet matches that of the same cell on the Data Entry
sheet? When data get entered, sometimes it needs to be center
justified and sometimes it needs to be right justified, and
significant figures change too. Thanks.

Frank

On Fri, 15 Apr 2011 20:05:33 -0700, "Jim Cone"
wrote:

It is good practice to use Option Explicit and declare all variables.
In worksheet event code "Target" is the active cell.
Qualify all ranges with the parent sheet (and the workbook if there is more than one).
The following code is untested but should be close to what you want...
'---
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myActiveCell As String
Dim myFormula As String

myActiveCell = Target(1).Address(False, False)
myFormula = Sheets("CTD").Range(myActiveCell).FormulaR1C1
If InStr(1, myFormula, "=IF(ISERROR", vbTextCompare) Then
Sheets("Data Entry").Select
Else
Sheets(Array("Data Entry", "CTD")).Select
End If
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Determine Colors (excel add-in) - in the free folder)




"Phrank"
wrote in message
.. .
I tried the Worksheet_Change event, and that's no good because it
initiated the macro every time I even selected a cell. There are only
two sheets in each workbook. ...well, there's actually a 3rd worksheet
(Notes) with notes as to what to do under certain circumstances. This
3rd sheet is helpful, too, with my code, because if for some reason
someone wants to access the CTD sheet, all they need to do is click on
the Notes tab to deselect the other two tabs, then the can select the
CTD tab alone. That part works well. I just need it to be able to
look at the analagous cell in the 2nd tab and press on with the macro.
For some reason, it's working when tested outside of the
Worksheet_SelectionChange event, but not working inside the event.
Thanks.
Frank