New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Move cursor based on another cells value

Macros, VBA, Excel Automation, etc.

Move cursor based on another cells value

Postby LostNExcel » Thu Aug 23, 2012 10:43 am

Hello, I'm just trying to use VB within Excel for the first time, been searching for a solution, have not found any. My question is I want to move the cursor to a different cell based on the value on another cell. Example

I enter data into cell B6, when I press enter I would like the cursor to move to either C7 or B8 depending if there is a number in cell B7.

I did find some code that would do some of what I want but have been unable to get it to do it all. here is the code

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 15 And Target.Column = 2 Then Target.Offset(2, 0).Select
End Sub


Honestly I don't really understand all of this but enough to get it to move the cursor after entering data but to only one cell.

Thank you in advance for any help with this. Also if you could provide some description of the solution so that I may understand that would be great.
  • 0

LostNExcel
Rookie
 
Posts: 7
Joined: Aug 23, 2012
Reputation: 0
Excel Version: 2010

Re: Move cursor based on another cells value

Postby charles » Thu Aug 23, 2012 1:08 pm

LostNExcel,

Welcome to the forum.

This bit of code may help you.
I specifier the target for input as "B6". If any thing other that that is selected the code
will exit the sub.
If you explain what it is you would like to do and provide a workbook
we may be of more help.


Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("B6") Then Exit Sub
If IsNumeric(Target.Offset(1, 0).Text) Then
    Target.Offset(1, 1).Select
Else
    Target.Offset(2, 0).Select
End If
End Sub
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Move cursor based on another cells value

Postby LostNExcel » Fri Aug 24, 2012 5:38 am

Thank you Charles. Your solution seems to be close with the exception of the cursor always moves the same way. I will try and explain to see if that helps with a solution I'm not sure I can send the book will have to check with the boss. Anyway if the first part of the book we determine the weight of a truck, if we can, sometimes not possilbe with the information provided, thus we have to manualy enter the weight, the company does not prefer that because of typing errors. Regardless, the next section is to determine total weight that is being shipped out. it looks like this

1 Col A Col B Col C

2 Gross weight 34123 (we manually enter this figure)
3 Truck Weight 999 777
4 Trailer Weight 222
5 Shipped Weight

So in this example we enter the 34123 (B2)for gross weight, now when i press the enter key I would like to move to Col C3 to enter 777 if the truck weight is missing from Col B3 (999) ( which comes from another section of the book). BUT if the truck weight is in COl B3 populated from the other section with 999 then when I press the enter key after entering the gross weight in col b2 34123 I want the cursor to move to col b4 for the trailer weight entery which is manual. With the solution that you provided I'm always going to col c3 (777) after pressing the enter key in the gross weight cell regardless if there is a truck weight or not. I hope that makes some sense.. Sorry about the column numbers and such but I thought I would just reduce my issue down to this simple idea thanks so much
  • 0

LostNExcel
Rookie
 
Posts: 7
Joined: Aug 23, 2012
Reputation: 0
Excel Version: 2010

Re: Move cursor based on another cells value

Postby charles » Fri Aug 24, 2012 12:14 pm

Hi,


Lets see if this works. I used the info you supplied.
Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("B2") Then Exit Sub
If IsNumeric(Target.Offset(1, 0).Text) Then
''' There is a number in in "B3" '' select B4''
''' Target(2 row down, same column)''''
    Target.Offset(2, 0).Select
Else
    ''' No number in B3 select C3 '''
    ''' 1 row down 1 column to the right '''
    Target.Offset(1, 1).Select
End If
End Sub
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Move cursor based on another cells value

Postby Sisyphus » Fri Aug 24, 2012 12:28 pm

Hello lostnexcel,
There is nothing wrong with Charles' code. However, I have re-written it in a way that may enable you to understand it easier and, in consequence, perhaps modify it yourself. I did this because the cell references you mention in your latest post differ from the ones you requested earlier. Here is the code:
Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("B6").Address Then
        If IsNumeric(Range("B7").Value) Then
            Range("B7").Select
        Else
            Range("C8").Select
        End If
    End If
End Sub
Please install it in the code sheet of the worksheet on which you want it to act.

To let you understand what it does:
  1. The code will run whenever a change is made in any cell on the worksheet
  2. If the changed cell was B6 action will be taken (otherwise nothing will happen)
  3. If B7 holds a number, B7 will be selected
  4. If B7 holds nothing or text, C8 will be selected.

You say that Charles' macro does "always the same" but you don't say what it does, select B7 or select C8.
If it always selects C8 the meaning is that B7 doesn't hold a number. It is possible to format the cell as "Text" and even if a number is entered it will not be recognized as such. Also, if you enter 32444kg in B7 this would be recognized as text, not as a number.

So, if you need other cells to be selected, I think you can change my code to test or select the cells you need selected or tested. If the numeric nature of B7 is in question, look at the cell format and the way you enter the weight. If you need to test for the cell being blank or non-blank change the above code by replacing If IsNumeric(Range("B7").Value) Then with
Code: Select all
If Not IsBlank(Range("B7")) Then
You can reverse the cell selection by removing the Not.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Move cursor based on another cells value

Postby charles » Fri Aug 24, 2012 12:42 pm

Sisyphus,

Thanks for the input.
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Move cursor based on another cells value

Postby LostNExcel » Mon Aug 27, 2012 6:52 am

Thank you both for your response, I keep learning something new. I will try and do better in explaning what I think is happening. sorry for the confusion in my second response I was trying to reduce my question to a simple form hoping that would make it easier. Guess that didn't work. I will try again. I will stay with the B2 example


Cell B2 is a user entered gross weight of a load
Cell B3 is a calculated cell figuring the weight of just the truck from previous cells if possible if not possible then 0

When I enter a number in B2 (no 99999kg is used just a number) and press enter, if B3 is 0 then I want the active cell to be C3, if B3 has a number not 0 then I want the active cell to be B4

in the first code provided by charles when I pressed enter in B2, the active cell always went to C3

In the last two codes provided the active cell always went to B4 regardless of the value of B3

All cell have numbers only, no text or numbers and text.

Thank you again for your help and assistance and especially your patience.
  • 0

LostNExcel
Rookie
 
Posts: 7
Joined: Aug 23, 2012
Reputation: 0
Excel Version: 2010

Re: Move cursor based on another cells value

Postby Sisyphus » Mon Aug 27, 2012 7:39 am

Hello lostnexcel,
Why is it that you talk about B2 now whereas we were discussing B6 before?
Do you think it might be a good idea to let us have a look at your actual spreadsheet? It appears to have some features that were Lost In Excel until now. ;)
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Move cursor based on another cells value

Postby LostNExcel » Mon Aug 27, 2012 12:05 pm

I'm sorry I thought I had explained the confusion. In my first post I used the actual cells in the sheet. Then when charles had a question I tried to reduce or simplify the sheet to just the part I was having trouble with. I just used the B1~C4 reference without really thinking about it. I do apologize. My questions and/or problem are within these four cells. I didn't realize that it really made much difference. As i stated earlier I can not send you a copy of the entire worksheet without permission and my boss is on vacation this week.

Scrap:
gross 0
truck 0 0
box 0 <----Box weight provided if not calculated in Column B

Here are the cells in question. Again sorry.
  • 0

LostNExcel
Rookie
 
Posts: 7
Joined: Aug 23, 2012
Reputation: 0
Excel Version: 2010

Re: Move cursor based on another cells value

Postby charles » Mon Aug 27, 2012 12:35 pm

Hi,

Do you have formulas?
If so which cell?
Also will there be a value in it?
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 47 guests

cron