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

 

[Marcelo Branco]

Hi Steve,

Maybe this in ThisWorkbook code-page

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If UCase(Target.Value) = "Y" Then
        Application.MoveAfterReturn = False
    ElseIf UCase(Target.Value) = "N" Then
        Application.MoveAfterReturn = True
        Application.MoveAfterReturnDirection = xlDown
    End If
End Sub

This changes the cursor setting if any cell in any worksheet = y or n. Do you really want this?

HTH

M.

 

[Steve Jr.]

You're right, it's not what I'm trying to do, let me try to explain it more clearly.

I want there to be specific cell or cells in various sheets that would trigger the cell pointer to stop moving at various times the user is entering data on the sheet. For example, in the sheet HUSBAND, I want cell G24 to be the cell that has Y or N in it and if it's Y then I want the cell pointer to not move after "enter<ENTER>" <ENTER><ENTER>is pressed. Then once it's put back to Y the cell pointer needs to continue on its merry way. Then in the WIFE sheet, the same thing. I'm thinking there needs to be "OR" type statements referring the each of the various cells on the various sheets that could trigger the cell pointer to stop moving.

BTW, I have another script in each sheet to automatically change the name of the Sheet Tab based upon a cell inside each sheet... depending upon how the sheet is referenced in the script for the cell pointer to stop, when the sheet tab name is changed can I presume that Excel will also change the name of the sheet in the new script automatically?

I hope you Excel gurus & divas out here could get me started on the first 2 sheets then it would just be a matter of repeating for the other sheets. I really don;t know VB, but I can surely copy once it gets started and follow the logic.

Thanks, Steve

 

[Marcelo Branco]

Hi Steve,

I think you need to clarify this part:

"I want cell G24 to be the cell that has Y or N in it and if it's Y then I want the cell pointer to not move after "enter<ENTER>" <ENTER><ENTER>is pressed. Then once it's put back to Y the cell pointer needs to continue on its merry way"

I'm assuming that the second Y is a N. If so you can make a test with Sheet Husband and G24 trying this

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

HTH

M.

 

[Zack Barresse]

Hi there Steve!

You can utilize an API and do this. Two sets of code here.

In your ThisWorkbook module:

Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call CursorCalc
End Sub

In a standard module:

Option Explicit

Private Declare Function ShowCursor Lib "user32" (ByVal bShow As Long) As Long

Sub CursorCalc()
    If ThisWorkbook.Worksheets("Sheet1").Range("B1").Value = "1" Then
        ShowCursor False
    Else
        ShowCursor True
    End If
End Sub

Edit: And btw, if you didn't notice in the code, set the worksheet name, range address, and value desired in the CursorCalc() sub routine.

Have a good day!

 

[Steve Jr.]

Hi Marcello... allow me to clarify more.

The question in cell F25 asks the user to enter Y or N in cell G25. The cell starts out as N, so the "normal" state is N for G25, which would mean the pointer should continue to move down after "enter", but when it's Y is when I need to make it stop moving until the cell goes back to N.

I hope that helps.
Thanks again, Steve

PS: I'm reading your script and I'm not following some of it, obviously you know what you're doing. The reason why I wait to go to scripts as the last resort is because even though I know they are powerful, if I have problems then I'm screwed to some extent because I don't follow them enough to debug or change as needed, but in this case I know it's the only option.

I'm also going to hold off on some of my questions about your script until we see how it works. I hope that's OK and that way you can stay focused on the script and not listen to me babble about what's this or that inside the script.

 

[Steve Jr.]

Hi Zac,
Again, I am no VB person, but the little I follow it seems like yours is not doing what I'm looking to do, I want the cursor to show, I don't want it to move after "enter" when a cell (or cells) in the active sheet are set to Y because the question I'm asking needs the cursor to stop moving until the user puts the cell back to N.
Thanks, Steve

 

[Zack Barresse]

My code will disable the mouse cursor, and yes, make it disappear. This is all based on a cells calculated value. Keyboard still works. When the value calculates back to the value you specify (1 in my example) the mouse will re-enable. Setting the mouse to show and not move is quite a bit more complex.

 

[Steve Jr.]

Zac,
I think you're missing what I'm trying to do... I'm trying to automate stopping the cell pointer from moving after "enter" is pressed (based upon the value of a cell) and then back to the normal way the original automatic script I already have makes it go down after "enter", when the value of that same cell is changed... I'm not trying to make the mouse disappear.

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.

I hope this clarifies the goal. If somehow the script can be done super simple in a way that I can debug down the road would be a bonus too.
Thanks,
Steve

 

[Zack Barresse]

But that will stop the cell from moving on enter, not your cursor. Your cursor moves with the mouse. Maybe I'm not following. You just want the active cell to not move? Nothing to do with the mouse??