Set Focus to Particular Cell

[treeppm]

Hello Friends.

If i enter data from A1-A5 when i Press enter in A5 how to set focus to B1?. Kindly let me know all possible way's
Thanks in Advance

With Regards

 

[My Aswer Is This]

This is a auto sheet event script
Your Workbook must be Macro enabled
To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("B1").Select
End If
End Sub

After entering data in Range("A5") you will then set focus on "B1"

 

[treeppm]

is there any other method like lost focus A5 and set focus to B1?.

 

[My Aswer Is This]

You do not want to use Vba?
What is this not doing for you?

 

[treeppm]

No bro.. I asked for VBA. but my question was wrong. i should have to asked is there any other coding like that?.
Thanks for that previous answer bro. working fine. I just ask for my knowledge

Thanks with Regards

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

No bro.. I asked for VBA. but my question was wrong. i should have to asked is there any other coding like that?.
Thanks for that previous answer bro. working fine. I just ask for my knowledge

Thanks with Regards

 

[My Aswer Is This]

There are several ways to do nearly everything in Excel.
I could have provided it this way with Offset command:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(-4, 1).Select
End If
End Sub

Set Focus is used sometimes when doing code in UserForms.

For example:

TextBox1.SetFocus

Or it could be written this way with GOTO command

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.Goto Range("B1")
End If
End Sub

 

[treeppm]

Excellent bro.. Nice explanations. I got exactly how it's work. Also i need another help. after i entered value in "C7" how to sort order(descending) value from the cells B1-B5

Thanks with Regards

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
If Not Intersect(Target, Range("A5")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("B1").Select
End If
If Not Intersect(Target, Range("C7")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("B1:B5").Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo
End If
End Sub

 

[My Aswer Is This]

PS:

You should be aware. A worksheet change event requires a manual change to a sheet. A change to the sheet due to a formula change will not activate the script.