Need help setting tab orders

[Valdis]

Hello my name is BJ and I am new to the site. I am helping my wife with a project she is working on. She is creating an excel spreadsheet for new accounts. She wanted me to help her create tab stops in her spreadsheet. I found this code on this site :


Private Sub Worksheet_Change(ByVal Target As Range) Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i

End Sub


I used the code and it works pretty well.... but it wasn't exactly what I was looking for. I noticed that once I set the tab stop order I had to enter data into every field that I tabbed to or the next time I pressed the tab button it would break the order that was in the code. I am also needing to know how I could make it so that she could ALT+Tab and go back to the last field in the tab order.

Any help would be greatly appreciated.

Thanks for your time,

-BJ

[rory]

For a straightforward tab order like that, I would just unlock the cells you want to tab into (Format-Cells, then it's on the Protection tab) and then Protect the worksheet. Pressing the tab key will then automatically hop from unlocked cell to unlocked cell.
HTH
Rory

[lucas]

Another possibility would be a simple userform for data input directly to the sheet....

The only problem with unlocking the cells and protecting the sheet is that they still don't follow the input pattern that you described....they go across each column and then down.

[rory]

Ah, my mistake - I was going by the order in the posted code, without looking at the workbook!

[lucas]

I thought of that too Rory before I tried it on the attachment...

I put a userform in your file and added only one textbox for the client number but it is fairly straigtforward. If it looks like it might be a solution and you have questions you can post them here.

One other thing that I would suggest is to avoid merged cells if at all possible. It is better to select the cells you would ordinarily merge and right click on them and select "format cells" then choose the alignment tab. On the Horizontal option drop the box and select "Center across selection"

[Valdis]

Another possibility would be a simple userform for data input directly to the sheet....

The only problem with unlocking the cells and protecting the sheet is that they still don't follow the input pattern that you described....they go across each column and then down.

A simple userform would be good but there are going to be 2 issues with that scenario.
1. The spreadsheet is huge
2. I don't know how to create a userform

My skills with Excel are a little above average but still very weak. I'm a network technician so I don't mess around much with applications... unless the program crashes or it doesn't run like it should.

I will attach the entire worksheet so you will know just how big this thing is.

Thanks for your help so far.... I've learned a bunch already :)

-BJ

[lucas]

I looked at it and it's not that bad if you consider that you would only have to go through the work of setting it up one time...then your good to go. Would you be working on only one of the sheets at a time? If so you could make a userform for each form you wish to fill out.

You're left with the options already suggested really. I don't know of any other ways to accomplish this.

VBAExpress has a submittal page where you can upload your file to get a quote on doing this for you here (http://www.vbaexpress.com/consulting/consulting.php).

[Valdis]

Thanks for the link.

Could you tell me how to create a userform first, so I could see if I would be able to do it myself?

BTW, I believe that they would only be using one sheet at a time.

[lucas]

Absolutly. I'm sure you can do this yourself once you get started and you can ask any followup questions here.

In the vbe on the file menu select Insert-userform.
You can grab the grips and resize it. A toolbox should open with it from which you can select and insert onto your userform labels, textboxes, buttons, etc.

Since you are going to have multiple userforms I would have the first userfrom be one for selecting which form you wish to fill out. It would close after you make your selection and the userform you chose would open.

[Valdis]

I will give that a shot.

Thanks so much for your time.

[lucas]

Post what you have when you get started and we will help you along. Especially when you get to comboboxes, etc.

[Valdis]

I talked with my wife over lunch today and she is thinking that tab order will not be important on this spreadsheet. I will unlock the cells that need to be edited and then protect the sheet.... I will see what she thinks of that.

Thanks again.... if I need more help I will be sure to let you know.

-BJ

[Valdis]

I think I have it all formatted. I am having one problem though. There are certain fields that populate across all sheets... BUT there is one field that will not populate on the third sheet. I have checked that formula and it is consistant across all sheets. What have I done wrong?

If you look on the third sheet, under Client Name you will see "0" as the client name... that is not good

Does anyone have a suggestion of where I messed up?

Thanks,

BJ

[Bob Phillips]

Just because you have merged a number of cells, the values is still in the first as far as Excel is concerned, so just use

='Client Information'!C14

[Valdis]

Thank you sooo much. I love this site :clap:

All of you have been a great help.

Thanks agian

[CaptRon]

I have used this method in some of the work I've done for the agency I'm with. It's not really very sexy but it gets the job done and I'm glad to share it with you.

I needed to arrange the tab order a particular way on certain portions of the sheet, then let it return to the Excel default style of tab order. I could not have the tab order dependent on whether an entry was made in the cell. So this is what I put together from different ideas I gleaned from here and there. Again, its neanderthal compared to what some of these guys do, but it works and I've run it through Excel 97, 2000, 2002, and 2003 without a problem. Hopes it helps your situation.

[CaptRon]

I placed the code I sent you into your sample file and I'm returning it just so you can see how it works. I see the full workbook you've sent and it would be a bit of work to set this up with what I've used but I think it could be done. Not every cell has to be specifically directed by code and that would cut down the job considerably. I have not applied this method to several different sheets within a workbook. I suspect it would create a few complications.

Ron

[lucas]

That works pretty good CaptRon. I couldn't get it to work on enter key but it did work on tab...

While I was looking at yours I kept thinking that I had seen this done before so I searched the kb and found this (http://vbaexpress.com/kb/getarticle.php?kb_id=364). You guys should take a quick look if you have time.

[CaptRon]

I'll have to try this revision of byundt's submission. When I first found it and tried it out, it was associated with a worksheet_change event and I needed to be able to tab to and through a cell without entry, if necesssary. Also, if you deleted the contents of a cell, ALL the entries disappeared. I pm'd him about that and I think he might have made some adjustments. This code work is certainly more compact. I must try it.

I agree with Valdis. This site is so good. I've learned more here in 6 months than I have in 3 yrs previously.

Ron

[lucas]

hmm, It works on Worksheet_SelectionChange but your right about deleting everything....had not noticed that.

[CaptRon]

Steve,

I gave Byundt's code a try and it works great except if you need to delete a single entry, they all go bye-bye. That would be very frustrating to someone who just filled out a sheet, realized they entered something in error, deleted it, and everything disappeared. Boy, would my phone be ringing.

One of these days, I going to figure out a slick way of getting Excel to move from cell to cell the way I want it to but until then, I'll just have to rely on my clumsy approach. It's not all that efficient, but so far it has worked as needed.

Don't know why the ENTER key didn't work for you. I added that in because some of our secretaries prefer to advance through the sheet with the ENTER key, others prefer the TAB key.

Ron

[Valdis]

I had the chance to look at the form and it works beautifully. When I get back to my PC I will play with the VBE a little bit to see if I can get the hang of what you did.

Thanks for sharing your ideas.

-BJ

[lucas]

I'm kinda interested in working out a simpler way to do this now...on byundt's code maybe disable delete....the cells all seem to be selected and that's why it all gets deleted..?

I'll take another look at yours tommorrow if I have time and see if some of my settings may be affecting it not working on enter.

[lucas]

See what you started BJ...:devil2:
Maybe tommorrow some of the most excellent coders here will take a look at this with us.

[Valdis]

I think I got it working... can anyone find something that is not working right?

Any feedback would be appreciated.

Thanks again for all the help.

-BJ

[Bob Phillips]

Steve,

I gave Byundt's code a try and it works great except if you need to delete a single entry, they all go bye-bye. That would be very frustrating to someone who just filled out a sheet, realized they entered something in error, deleted it, and everything disappeared. Boy, would my phone be ringing.

One of these days, I going to figure out a slick way of getting Excel to move from cell to cell the way I want it to but until then, I'll just have to rely on my clumsy approach. It's not all that efficient, but so far it has worked as needed.

Don't know why the ENTER key didn't work for you. I added that in because some of our secretaries prefer to advance through the sheet with the ENTER key, others prefer the TAB key.

Ron

See http://xldynamic.com/source/xld.xlFAQ0008.html

[CaptRon]

I was in error. Brad's KB submission was always a Worksheet_SelectionChange event, but it did delete all entries.

I sent Brad another PM on this. He sent a modified version and it works better as it only deletes the entry in the single cell, not everything. However, doesn't work well with merged cells. Also, all the cells in the array remain "selected" and appear marked. I'm not sure if I like that or not.

How will it work with several sheets of different arrangements. I suppose you could try moving the code from ThisWorkbook to the sheet module and change Workbook_SelectionChange to WorkSheet_SelectionChange. Maybe????

I want to work with him on it. Brad's method is much more streamlined than what I use and would certainly be easier to set up, but I need it to function pretty much in the same fashion. I believe a lot of people could use this capability. It's handy as a pocket on a shirt.

You can set tab order in Access and on a userform in Excel but I wish this was also a standard capability of Excel worksheets.

[lucas]

I'd also be interested in seeing what you guys come up with. Bob's link in post #26 works well it just has a 46 non contiguous cells limit but that's an awful lot of cells to be tabbing through....should be plenty for most of us.

[CaptRon]

Man, you got it going! The only thing I see is clean out the unnecessary commented-out stuff left in there, then apply the same approach to the other sheets, if necessary.

It looks like you needed a custom tab order only for the first section of the Client Information sheet and the default tab order was sufficient for the balance of the sheet. Is that correct? Just FYI, you can apply this to separate sections of a sheet, anywhere the standard across and down tab order doesn't work for you. It does require that the entry cells be unlocked and the sheet must be protected.

If I were to apply this to another sheet within the same workbook, I'd simply add another module, copy and paste the simple little macros to it, change the cell references in the macros for the new sheet (if the cell reference is the same on both sheets, no need to copy that macro as it will work for both sheets). The new macro module should contain only the macros that reference cells not addressed in the first module.

Then, copy and paste the code from the sheet Client Information to the other sheet module and, again, change the cell and macro references in that code for the tab order on the second sheet. Takes a little time and attention to detail, but its not any more complicated than that.

Good job!

Ron