Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Macro to start cursor at a specific field

Posted on 2011-05-10
10
Medium Priority
?
181 Views
Last Modified: 2012-05-11
I am using the following code to tab from F to G. Then it goes down to F then G again and so on and so on. I am just entering data into the F and G field. What i need is when open the cursor automatically starts at F6. Here is my code. Can I add anything to start at F6?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column > 7 Then
     Application.EnableEvents = False
     Target.Offset(1, -Target.Column + 6).Select
     Application.EnableEvents = True
End If
End Sub

0
Comment
Question by:allenkent
  • 4
  • 3
  • 3
10 Comments
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35731828
try
Range("F6").Select
0
 
LVL 23

Expert Comment

by:Brian Gee
ID: 35731874
I just tested this and it worked for me:

Option Explicit

Private Sub Workbook_Open()
     Range("F6").Select
End Sub

Open in new window

0
 

Author Comment

by:allenkent
ID: 35732064
I tried plugging in and get errors. Here is my code. Do I put the F6 after the word range?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column > 7 Then
     Application.EnableEvents = False
     Target.Offset(1, -Target.Column + 6).Select
     Application.EnableEvents = True
End If
End Sub
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:Brian Gee
ID: 35732179
Using the code I put above (you can exclude the Option Explicit), once you open your Workbook, the cell F6 will be selected. It's using the Open action of the Workbook to trigger the F6 cell selection.
0
 

Author Comment

by:allenkent
ID: 35732213
What about my code? I need both things to happen. When I put both codes I get an error. I need it to open at F6 and only go from F to G on tabs.
0
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35732255
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("F6").Select
If Target.Column > 7 Then
     Application.EnableEvents = False
     Target.Offset(1, -Target.Column + 6).Select
     Application.EnableEvents = True
End If
End Sub
0
 
LVL 23

Accepted Solution

by:
Brian Gee earned 1000 total points
ID: 35732259
Both events can co-exist. One just addresses what to perform at Worksheet open, and the other runs your IF statement upon a Worksheet change event.

Private Sub Workbook_Open()
     Range("F6").Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
     If Target.Column > 7 Then
          Application.EnableEvents = False
          Target.Offset(1, -Target.Column + 6).Select
          Application.EnableEvents = True
     End If
End Sub

Open in new window

0
 

Author Comment

by:allenkent
ID: 35732392
Point Pleasant code does not let me go anywhere except F6. It always stays in F6
Yobri code does not start in F6 but instead it opens in A41 everytime.

0
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35732509
not seeing the code beore the sub call


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


i would suggest you move the

Range("F6").Select


before the call is made to your sub


0
 
LVL 23

Expert Comment

by:Brian Gee
ID: 35732566
Could you try closing Excel entirely and then relaunching it? That Workbook_Open sub really should leave no other option but to select cell F6 at open...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question