TAB path

[giorgiotassi]

Hi, I am preparing an excel file where the user has to enter about 20 data to obtain a price calculation. What I would like to do is the following:
- start from a given cell (B4) and enter the first data
- then tab to the next cell (B5), enter data
- then tab to next cell (B6), enter data
- then tab to the next cell in another column (D4), enter data
- and so on.
Enclosed a file showing an example of the "path" that TAB should follow.
Is there a VBA code to instruct the TAB key to follow a given path?
many thanks in advance for the help that I will receive.
regards from Italy
Giorgio

 

[Ajesh]

Hi,

please try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet
  If Target.Row = 6 And Target.Column = 2 Then
  Cells(4, 4).Select
  End If
  If Target.Row = 6 And Target.Column = 4 Then
  Cells(8, 2).Select
  End If
  If Target.Row = 10 And Target.Column = 2 Then
  Cells(8, 4).Select
  End If
  End With
End Sub

Thanks

 

[Hui]

If you do the following it will do what you want

Select Foglio1
Select B4
Holding the Ctrl Key
Select B4 and drag to B6
While still holding the Ctrl Key Select D4 and drag to D6
While still holding the Ctrl Key Select B8 and drag to B10
While still holding the Ctrl Key Select D8 and drag to D10
While still holding the Ctrl Key Select B4
Release the Ctrl Key
Now enter a value and press Tab, Not Enter
Continue around your cells using Tab

 

[giorgiotassi]

Hi Hui,
many thanks for your advice.
I forgot to inform that I'm working with Excel 2011 for MAC.
I tried to follow your instructions, but I could not obtain the result.
Perhaps the MAC version of Excel has another set of instructions to ensure that, by clicking TAB, the active cells is activated following a given path?
I hope you can help further.
Many thanks anyway for your attention and best regards from Italy.
Giorgio

 

[giorgiotassi]

Hi,

please try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet
  If Target.Row = 6 And Target.Column = 2 Then
  Cells(4, 4).Select
  End If
  If Target.Row = 6 And Target.Column = 4 Then
  Cells(8, 2).Select
  End If
  If Target.Row = 10 And Target.Column = 2 Then
  Cells(8, 4).Select
  End If
  End With
End Sub

Thanks

Dear Ajesh,
I must admit I am an absolute beginner in VBA.
May I ask you for an additional effort?
Would you please send my file back with the addition of your macro in it?
I've seen that it is rather short and should take you just some minutes of your time.
For your convenience, I am sending you my excel file again, with addition of clearer info about my request.
I am using Excel 2011 for MAC, just for info.
Many thanks and regards from Italy.
Giorgio

 

[Somendra Misra]

Hi Giorgio,

In Excel 2011 for MAC just use Command key inplace of Ctrl Key as suggested by Hui.

Regards,

 

[Ajesh]

Hi giorgiotassi,

please see enclosed your file. I have added the macro to Foglio1. I have not used MAC version of Excel but this should work fine. You need to press "Return" or Enter Key after each entry.

Thanks

 

[giorgiotassi]

Dear Ajesh, many thanks for your help. Due to my total ignorance of VBA, I am still in troubles, though. I've tried to copy/paste your code in my macro form, but I get some negative replies by the system. I am tempted to give up and let the user of my sheet do the tabbing with his mouse, instead of creating a pre-defined path for his tab or enter key. However I wish to thank you so much for your help in any case. Best regards from Italy. Giorgio

 

[giorgiotassi]

Hi Giorgio,

In Excel 2011 for MAC just use Command key inplace of Ctrl Key as suggested by Hui.

Regards,

Hi Somendra Misra, many thanks for your advice. It does not work as you say, but I will do the tabbing by clicking the mouse in the appropriate cell, moving it "by hand". Too difficult to make it "automatic". Thanks anyway and best regards.
Giorgio

 

[Somendra Misra]

Well I did the same and it happened.
Select first cell.
Keep Press CMD key and than press touch pad left side and select all the ranges. Than start entering the data.

Regards,

 

[Somendra Misra]

Dear Ajesh, many thanks for your help. Due to my total ignorance of VBA, I am still in troubles, though. I've tried to copy/paste your code in my macro form, but I get some negative replies by the system. I am tempted to give up and let the user of my sheet do the tabbing with his mouse, instead of creating a pre-defined path for his tab or enter key. However I wish to thank you so much for your help in any case. Best regards from Italy. Giorgio

I tried Ajesh code on MAC Excel 2011, and it works just fine. As it should.

Regards,

 

[Ajesh]

Thanks SM, Was not able to check the forum lately hence could not reply.

@giorgiotassi : Firstly, Thanks for your feedback and kind words. And don't be disheartened by not knowing VBA. Even I am learing day by day, we all are by sharing our knowledge and skills.

I hope you are placing the code in relevent Sheet's Code window and not in a Module, or are you trying to make it work in a userform?

Maybe you can upload a sample file and anyone of us can help you further.

Thanks

 

[Somendra Misra]

@Ajesh

You are Welcome.

Regards,