user form tab index and sort code for sheet 1

Hi there just a couple of questions.

On the attached spreadsheet I am looking for code to change the tab index on userform1. Basically if optionbutton1 = true then I would like to tab through CB3 then CB4 then TB3 then commandbutton 1 not optionbutton 2, 3, 4 and then command buttton 1.

Likewise if option button 2 is selected i would like to tab through CB3, CB4, TB3, CB5,CB6, TB4 then commandbutton1 not CB3, CB4, TB3, CB5,CB6, TB4, OB3, OB4 Command button1.

Think you get the idea from this.

Also with this userform is there anyway to adjust the size of the userform depending on which option button is selected? So that it wont leave a large blank area if only 1 is selected?

Finally is it possible to add some code to commandbutton1 so that after an entry has been made to sheet 1 (products) it will put all the products in sheet 1 in alphabetical order?

Thanks in advance

Hi - the option buttons have a tabindex property, if you set this to something high (e.g. 100, 101, 102 for option 2, 3) then you will tab though the comboboxes and textboxes first.

You can adjust the userform height with the .height property of the userform and then the position of your command button using it's .top property.

Thanks for your advice. I understand these tab index but I still want to be able to tab through the optionbuttons so I can activate them with a space bar not have to use a mouse which is why I was looking for an If code so that if optionbutton1 was true then it would put the commandbuttons tab index lower?

You can renumber the tabindex by code, so you would have something like:

Private Sub OptionButton1_Click()
If Me.OptionButton1 = True Or Me.OptionButton2 = True Or Me.OptionButton2 = True Then
    Me.OptionButton1.TabIndex = 100
    Me.OptionButton2.TabIndex = 101
    Me.OptionButton2.TabIndex = 102
Else
    Me.OptionButton1.TabIndex = 6
    Me.OptionButton2.TabIndex = 7
    Me.OptionButton2.TabIndex = 8
End If
End Sub
Private Sub OptionButton2_Click()
If Me.OptionButton1 = True Or Me.OptionButton2 = True Or Me.OptionButton2 = True Then
    Me.OptionButton1.TabIndex = 100
    Me.OptionButton2.TabIndex = 101
    Me.OptionButton2.TabIndex = 102
Else
    Me.OptionButton1.TabIndex = 6
    Me.OptionButton2.TabIndex = 7
    Me.OptionButton2.TabIndex = 8
End If
End Sub
Private Sub OptionButton3_Click()
If Me.OptionButton1 = True Or Me.OptionButton2 = True Or Me.OptionButton2 = True Then
    Me.OptionButton1.TabIndex = 100
    Me.OptionButton2.TabIndex = 101
    Me.OptionButton2.TabIndex = 102
Else
    Me.OptionButton1.TabIndex = 6
    Me.OptionButton2.TabIndex = 7
    Me.OptionButton2.TabIndex = 8
End If
End Sub