Control Cell Pointer based upon cell answer

[Steve Jr.]

Hello,
I'm using Excel 2003 and I don't even know if this is possible, but here it goes. I want to control the cell pointer based upon a cell on different sheets within the workbook. I know the cell pointer setting is workbook wide, and I experimented that I could control it using hot keys and macros, but what I want is to control it automatically by the answer of a cell... now we're using Excel.

If whatever cell I pick has Y in it then I want the cell pointer to stop moving after pressing enter and then when the cell goes back to N then I want the script that starts automatically when the file opens to activate again.
Is this possible?


Here is the script that starts up when the file is opened:

Private Sub Workbook_Open()
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
End Sub


Let me know if you need anything further.

Thanks,
Steve

 

[Steve Jr.]

Marcello....

Here is the script after I made the most recent change for Ucase to Husband line. I'm curious, why wouldn't the If Not Intersect line referring to Husband have Ucase too?


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(ActiveSheet.Name) = "HUSBAND" Then
If Not Intersect(Sheets("HUSBAND").Range("G24"), Target) Is Nothing Then
If UCase(Target.Value) = "Y" Then
Application.MoveAfterReturn = False
ElseIf UCase(Target.Value) = "N" Then
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
End If
End If
End If
End Sub


Man oh man I can't believe how close you're getting for this to work. Now it's a matter of getting the sheet rename inside the script to work and you're home free. Go Marcello Go!!!

Thanks,
Steve

 

[Marcelo Branco]

The first IF compares the sheet-name with the string HUSBAND and is case-sensitive.

On the second IF, Sheets("HUSBAND") is used to refers to a sheet named Husband or HUSBAND or HUSband, not case sensitive.

You didnt answer my last post about the scenario...

How many sheets do you have? Only 2 - Husband and Wife?

M.

 

[Marcelo Branco]

Sorry, sorry...

I hadnt seen your previous post

Wait

M.

 

[Marcelo Branco]

Steve,

As you know each Sheet has two names, ie, a friendly name that you see in the sheet-tab and you can change, and also a code-name that is constant.

You see these two names at VBEditor under VBAProject as something like
Sheet1 (Husband)
Sheet2 (Wife)

The names within parentheses are the friendly-names (they can be changed). The names on the left are the codenames (they are constants).

That said, assuming that the code-names for sheets Husband and Wife are exacty as above (check and change to suit with your real situation) and the cells that controls the movement are G24 in both sheets, try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.CodeName = "Sheet1" Or Sh.CodeName = "Sheet2" Then
        If Not Intersect(Target, Sh.Range("G24")) Is Nothing Then
 
            If UCase(Target.Value) = "Y" Then
                Application.MoveAfterReturn = True
                Application.MoveAfterReturnDirection = xlDown
            ElseIf UCase(Target.Value) = "N" Then
                Application.MoveAfterReturn = False
            End If
 
        End If
    End If
End Sub

Tell me if this worked

M.

 

[Steve Jr.]

Marcello,
The script did not work right, but I noticed it worked backward and noticed the Y and N were backward in the script, so I switched them around and now it works... sort of. The problem I'm noticing now is the sheet is not always starting up the way it used to when the script was just this:

Private Sub Workbook_Open()
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
End Sub

Can I ask you something? Is it possible to somehow re-think the re-naming inside the script so the script uses the actual current name of the sheet once it gets changed? IF this is not possible then I understand, but it would be cool to start out with Husband like the script version prior to this one and then when the sheet name changes so does the script too. Is there any way to reference that C2 cell to get the tab name when it changes because it's that cell that the other script grabs and renames the sheet.


Current script version in use:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.CodeName = "Sheet1" Or Sh.CodeName = "Sheet2" Then
If Not Intersect(Target, Sh.Range("G24")) Is Nothing Then
If UCase(Target.Value) = "N" Then
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
ElseIf UCase(Target.Value) = "Y" Then
Application.MoveAfterReturn = False
End If
End If
End If
End Sub


Thank again,
Steve

PS: Is there any way to post the file here so you can use it?

 

[Marcelo Branco]

Can I ask you something? Is it possible to somehow re-think the re-naming inside the script so the script uses the actual current name of the sheet once it gets changed? IF this is not possible then I understand, but it would be cool to start out with Husband like the script version prior to this one and then when the sheet name changes so does the script too. Is there any way to reference that C2 cell to get the tab name when it changes because it's that cell that the other script grabs and renames the sheet.

Steve,

Why do you want to use the friendly-name that can change at any moment? Maybe i'm missing something but i cant see any benefit of doing so.

It would only complicate things, forcing the code to get the name of the sheet in a cell of a sheet whose name is not known, because this name may be changed at any moment.

Maybe i could use ActiveSheet.Range("C2")... but how would i compare the Activesheet.name to be sure that the relevant sheets (originally Husband or wife) that are being update...

I thought that Y = can move; N = cannot move, but as you said, i was wrong.

M.

 

[Marcelo Branco]

Steve,

Another possiblity is to duplicate the code inside the originally Husband and Wife sheets using the Worksheet_Change event of each.

M.

 

[Steve Jr.]

Hi Marcelo,

Is there anyway to post the file here or send it to you to see what I'm doing so it all makes sense to you?

The reason the sheet name needs to dynamically change is because it's changing to the person's name the data on the sheet belongs to. It makes it obvious and more personal that the data on that sheet belongs to Jane, Mary, Steve or Marcello and not just plain Husband or Wife.

Thanks,
Steve

 

[Steve Jr.]

OK Marcelo, the file is on its way to you now.

I have an idea about this sheet re-name problem, I bounced this off a couple people at work today and I was telling them how close you are to getting this figured out, but the sheet re-name is causing a problem. We all said why does Excel change formulas that reference sheet names and the formulas are dynamic and they change right along... it was then that it hit me how this could be done and it would be much easier for you.

Please consider this if you need to go another route because don't forget, I'm the one that has to keep this running since I don't have you next door.

Possibility #1... have a hidden sheet with a name that never changes and on that sheet always have the sheet names of the other sheets listed and then the script could pull the names from that sheet.

Possibility #2... and I like this one better, is to do the same thing as #1 but do it in the sheet PayrollWeeks since that tab does not change name, I could put the other sheet names below the payroll weeks tables and that would be a static place for a script to get the current sheet names

I'm heading for supper and will check back later.

Thanks,
Steve

 

[Steve Jr.]

The spreadsheet is working great and I want to post a wrap-up of what Marcelo did to get things working right. I will keep this simple because I myself don't fully follow what the syntax of the scripts, but I understand what they are doing. It's good to post these scripts as they may help someone else down the road doing something similar. Thank you.


This script is located in the "ThisWorkbook" of the VBA editor, the script makes the cell pointer move down after enter right and pushes N to specific cell on 2 sheets upon the opening of the file.

Private Sub Workbook_Open()
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
Sheet1.Range("G24") = "N"
Sheet2.Range("G24") = "N"
End Sub



The next script is in a sheet and it controls how the cell pointer is stopped and started upon conditions being met in a cell on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G24")) Is Nothing Then
Application.EnableEvents = False

If UCase(Target.Value) = "N" And UCase(Sheet2.Range("G24")) = "N" Then
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
ElseIf UCase(Target.Value) = "Y" Then
Application.MoveAfterReturn = False
End If

Application.EnableEvents = True
End If
End Sub



The next script is in another sheet and it forces the sheet name to change upon a cell entry being made.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Sheet1.Name = Range("B4")
If Err <> 0 Then Range("B4") = "Wife": Sheet1.Name = "Wife"
ActiveSheet.Name = Range("A2").Text
Application.EnableEvents = True
End If
End Sub



I want to say publicly that Marcelo was wonderful to work with and is obviously no slacker when it comes to VBA.

Bravo Marcelo!!!!!