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.]

Zac... you got it... this has nothing to do with "the mouse" it has everything to do with the cell pointer as I use the "enter" key. My last reply shows how I can do it manually (every time which is PITA) and then semi-automatically using hot key, now I want to go fully automatic brother.
Thanks,
Steve

 

[Zack Barresse]

So if the cell becomes "N" you don't want the active cell to move. You just want it to sit there and not move until the user changes it to "Y"? That would mean you couldn't enter any other info on the sheet until your criterion is met. About the best we can do is 1) after data entry make that the active cell, and 2) anytime another cell(s) is attempted to be selected make that cell the active cell. Does that sound like what you would want?

 

[Steve Jr.]

Zac, all I can do is copy and paste the prior reply where I spell out how I can do it already NON-VBA style. I hope this clarifies it because again I can do it manually, I want to automate it now based upon the contents of a cell on each sheet.

Prior reply:
I can already do it manually by doing TOOLS >> OPTIONS >> EDIT >> uncheck the box for "move selection after enter". Obviously doing this every time I need the cursor to stop moving sucks, so there needs to be another way. I can already do it semi-automatically by recording a macro of me doing these steps and then assign a hot key to it and the macro works great, but this is Excel and automation rules here and if I can now automate the process 100% then that takes the end user out to decide what to do next.

Thanks,
Steve

 

[Steve Jr.]

Guys....

Here's the logic of the VBA... typed out in Excel formula style that should clarify this:

IF(HUSBAND!G24<>"Y",then allow cell pointer to keep moving down automatically,otherwise stop moving it after pressing "enter")

BUT THEN, after the Y is put back to Y then the original script when the file is opened needs to be reactivated so the cell pointer can resume going down automatically after pressing "enter".

I hope this helps.

Thanks,
Steve

PS: I had to post another reply because the system timed out and stopped me from editing. I'm still learning this board.

 

[Steve Jr.]

Gents,
It seems to me that Marcello is on the right track even though it's not working, it just seems to me to be on the right track.
I have no idea about the IF NOT INTERSECT.... IS NOTHING logic, but could that be where it's getting hung up?
Thanks,
Steve


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If 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

 

[Marcelo Branco]

Steve,

If Not Intersect(Sheets("HUSBAND").Range("G24"), Target) Is Nothing Then

Checks whether the range that has changed (user has entered Y or N ) is exactly the cell G24 of the sheet HUSBAND

Only when this occurs, in my example, the inside-IF will be evaluated. I thought that this was what you need. Am i wrong?

M.

 

[Steve Jr.]

Hi Marcello,
Is there another way to write those IF statements so they stand out for each condition on each sheet? I did try your script, but unfortunately it's not working for either the Y or the N condition. I really think you're close, it just needs to be figured out.
Thanks,
Steve

 

[Steve Jr.]

Hole-eee crap... HOLD ON!!!

I figured out why the script was not wokring... the sheet name in the script was all caps and that's not how the sheet name was typed. I changed the script to Husband and now it works. Wow!!!

Marcello, can the script still be written more clearly or is this the best way to go? Can you please post it with the second sheet for the Wife too included?

BUT it's still not 100% as the name of the sheet will change automatically once a person types their name in on another setup sheet. I just tried it and the sheet name changes but the script stays as Husband. Can the script pull the current sheet name after it's been changed?

Thanks again Marcello... you rule dude!!!!
You are getting so close, please keep at it.

 

[Marcelo Branco]

Hey Steve,

Its my fault. I should have coded
If UCase(ActiveSheet.Name) = "HUSBAND" Then

FYI: UCase function transforms a string to upercase

Please, try to give me the complete scenario of what you are trying to do.

I didnt know the sheet names could change. How they change? User enter a new name where? Which cell/sheet? What is the reason for a user change the sheet-names?

M.

 

[Steve Jr.]

Marcello... I'll give it a shot to explain this.

Here's the script that changes the sheet tab name...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
ActiveSheet.Name = Range("C2").Text
enditall:
End Sub

This script is in the individual sheets as the cell reference is not the same on some sheets... BUT for the Husband and Wife sheets it is the same cell C2 and the script you see above is from the Husband's sheet.

The reason for the sheet name change is to customize it for the end user so when they type in their first name on another sheet that sets up their tax stuff and payroll stuff, I have the Husband sheet grab that name to cell C2 and then as you see the script above renames the sheet tab to the person's name instead of just Husband. As far as the sheets go, cell C2 is where it's at for these 2 sheets to get their tab name right.

What's nice about Excel formulas (but obviously not VBA scripts) is that Excel changes ALL references to the sheet tab name change in all the formulas where I grab info from the other sheets back and forth. Too bad scripts don't change the sheet name reference automatically too.

I trust this helps.

Thanks,
Steve