Set Tab order and highlight fields

[Silverspoon]

I am trying to set the tab order of a registration form that I created. I am not a coder and do not have any experience in VBA, etc. I'm a newbie at this.

Below is the code that I found on this site that I modified it to reflect my form. However, when I open the form this script does not run. When I open VBA to step through the code I just hear ping sound which indicate something is not working or there is an error but there is no error message.

This script is to set the tab order. I would like it to do the following:

1. First, the top part of the form is to tab the user to enter their name, phone numbers, email ,etc. It should start in cell A3 which is the Salutation (i.e. Mr. Mrs., etc.) field. This section of the form is standard.

2. The IF statement is checking to see if a registration number has been entered into cell B10. If a registration number is provided, Section A of the form cells contents should be cleared and locked. The cursor should then go to Section B cell B26 where the user will enter names.

3. IF a registration number has not been entered, the tab will continue to cell B11.

4. I would also like each field to be in a different color when either pressing the tab button or the enter key. I saw a thread about how to do this but I did not know where in my script should this script be entered. Below is the code that I found from your forum.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
ActiveSheet.Cells.Font.ColorIndex = xlAutomatic
Selection.Cells.Font.Color = RGB(256, 0, 0)
Application.EnableEvents = True
End Sub

Below is my script for tabbing without the code for changing the color.

Option Explicit 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    Dim aTabOrd As Variant 
    Dim iTab    As Long 
    Dim nTab    As Long 
    Dim iNew    As Long 
     
    aTabOrd = Array("A3", "B3", "D3", "F3", "A4", "B4", "C4", "D4", "A5", "B5", "C5", "B5", "C5", "D4", "B8", "C8", "E8", "B10") 
    nTab = UBound(aTabOrd) + 1 
    iTab = 0 
     
    If IsEmpty(aTabOrd) Then 
        If IsEmpty(Range("B10").Value) = True Then 'If regisrtration number entered
             'ActiveSheet.Unprotect ("PASSWORD") deactivate until script is working
             'ActiveSheet.Protect ("PASSWORD")
             
             'Clear Contents in SECTION A If Hotel Registration NOT Blank. Clear Lock B15:F17 & B19:F20
            Worksheets("Hotel Registration Form").Range("B15:F17").ClearContents 
            Worksheets("Hotel Registration Form").Range("B19:F20").ClearContents 
             
             'Lock all Cells in SECTION A
            Worksheets("Hotel Registration Form").Range("B15:F17").Locked = False 
            Worksheets("Hotel Registration Form").Range("B19:F20").Locked = False 
            MsgBox "You have entered a hotel registration number. Please proceed to SECTIONS B, C, & D to complete the registration form.", vbExclamation, "UK Residents ONLY" 
             
             'Set Tab Order to bypass SECTION A
            aTabOrd = Array("D10", "E10", "F11", "B26", "B27", "B28", "B30", "B31", "B32", "B33", "C35", "C26", "27", "C28", "C30", "C31", "C32", "C33", "C35", "D26", "D27", "D28", "D30", "D31", "D32", "D33", "C35", "E26", "E27", "E28", "E30", "E31", "E32", "E33", "C35", "F26", "F27", "F28", "F30", "F31", "F32", "F33", "C35", "B38", "C38", "D38", "E38", "B39", "C39", "D39", "E39") 
            nTab = UBound(aTabOrd) + 1 
            iTab = 0 
        End If 
         'Set Tab Order
        aTabOrd = Array("B11", "B15", "B16", "B17", "B19", "B20", "B26", "B27", "B28", "B30", "B31", "B32", "B33", "C35", "C15", "C16", "C17", "C19", "C20", "C26", "C27", "C28", "C30", "C31", "C32", "C33", "C35", "D15", "D16", "D17", "D19", "D20", "D26", "D27", "D28", "D30", "D31", "D32", "D33", "D35", "E15", "E16", "E17", "E19", "E20", "E26", "E27", "E28", "E30", "E31", "E32", "E33", "E35", "F15", "F16", "F17", "F19", "F20", "F26", "F27", "F28", "F30", "F31", "F32", "F33", "F35", "B38", "C38", "D38", "F38", "B39", "C39", "D39", "F39") 
        nTab = UBound(aTabOrd) + 1 
        iTab = 0 
    Else 
        On Error Resume Next 
        iNew = WorksheetFunction.Match(Target(1, 1).Address(False, False), aTabOrd, 0) - 1 
        If Err Then 
            iTab = (iTab + 1) Mod nTab 
        Else 
            iTab = iNew 
        End If 
        On Error Goto 0 
    End If 
     
    Application.EnableEvents = False 
    Range(aTabOrd(iTab)).Select 
    Application.EnableEvents = True 
     
End Sub

I appreciate any assistance anyone can provide.

-Thank you in advance

 

[skywriter]

Re: Set order Tab and highlight fields

Cross posted.

 

[Silverspoon]

Re: Set order Tab and highlight fields

What is Cross Post?

 

[MARK858]

Re: Set order Tab and highlight fields

Have read of the link below which explains it quite well (it is a link included in the forum rules (link to the forum rules in my signature if you want to read them))

Excelguru Help Site - A message to forum cross posters

 

[Silverspoon]

Re: Set order Tab and highlight fields

Thank you for this information. I was not aware. So, is it possible for me to get assistance with this issue and future issues?

 

[MARK858]

Re: Set order Tab and highlight fields

So, is it possible for me to get assistance with this issue and future issues?

Of course you can. A moderator would have told you if otherwise.
Afraid I can't provide any help myself at present as not at a computer but I am sure someone will jump in.
The only thing I will ask though is you have put the code in a worksheet module and not a regular module?

 

[Silverspoon]

Re: Set order Tab and highlight fields

I will try that. Thank you for your response.

 

[MARK858]

Re: Set order Tab and highlight fields

Just note that you can only put one of the codes you posted in the worksheet module as you can't have 2 Worksheet_SelectionChange codes in the same sheet.

 

[Silverspoon]

Re: Set order Tab and highlight fields

I have one that is Worksheet_Change and this one which is Worksheet_SelectionChange.

After moving the script to the worksheet as instructed, when I open the file the cursor starts in cell B3. When I click the tab key it goes to D3 and F3 and back to B3. It never goes beyond that. Also, please note that B3 is a merged field (B3 merged with C3) and D3 is also a merged field (D3 merged with E3). I don't know if that makes a difference or not.

In the script, it is suppose to start at A3...not sure why it is starting in B3 and not moving past F3.

 

[MARK858]

Re: Set order Tab and highlight fields

Like I stated I am not by a computer to check/test the actual code (on my phone) and so someone else will have to help you with it but in general merged cells normally mess up most VBA and should be avoided wherever possible.

I have one that is Worksheet_Change and this one which is Worksheet_SelectionChange.

then that part is fine as they can both reside in the worksheet module together.