Set tabindex automatically
 

Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

To do it by code, you would need to use the control's name to assign the tab
index. If this is a one time effort, it is probably just as fast to right
click the form and select TabIndex from the shortcut menu. Then you can line
up your controls in the dialog box click OK and that's it.

"Paul" wrote:

Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

not test but this may work.
I am assuming that your textboxes are still named Texbox1, TextBox2 etc
Place code behind form. In example I have only allowed for 8 textboxes but
change as appropriate.

Hope helpful

Private Sub UserForm_Initialize()

For i = 1 To 8

Controls("TextBox" & i).TabIndex = i - 1

Next i

End Sub
--
jb


"Paul" wrote:

Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

Try this -

Sub myTabOrder()
' dump control names to cells
' select re-sorted controls in tab index order high first
' change bDump True/false and form name to suit
Dim bDup As Boolean
Dim i As Long
Dim cel As Range
Dim ctr As Object

bDump = True ' read

With ThisWorkbook.VBProject.VBComponents("UserForm1").D esigner

If bDump Then ' read

For Each ctr In .Controls
' this order will always be the order
' controls were added to the form
i = i + 1
Cells(i, 1) = ctr.Name
Cells(i, 2) = ctr.TabIndex

Next
''''''''''''''''''''''
'' discard any controls whose container is not the form
'' eg in frames or page controls
'' Sort controls in order highest tabindex to lowest
'' note first tabindex is 0,
'' eg if nine controls in order is 8-0
'' Select cells with the control names
'''''''''''''''''''''''''''

Else
For Each cel In Selection
.Controls(cel.Value).TabIndex = 0
Next
End If
End With

End Sub


Regards,
Peter T
"Paul" wrote in message
...
Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

Thanks for the reply, but I really want to do this by code, we are talking
about more than 500 controls to scroll trough.

Any ideas?

Cheers,

Paul

"JLGWhiz" wrote:

To do it by code, you would need to use the control's name to assign the tab
index. If this is a one time effort, it is probably just as fast to right
click the form and select TabIndex from the shortcut menu. Then you can line
up your controls in the dialog box click OK and that's it.

"Paul" wrote:

Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

Sorry,

It's not working

Cheers,

Paul

"john" wrote:

not test but this may work.
I am assuming that your textboxes are still named Texbox1, TextBox2 etc
Place code behind form. In example I have only allowed for 8 textboxes but
change as appropriate.

Hope helpful

Private Sub UserForm_Initialize()

For i = 1 To 8

Controls("TextBox" & i).TabIndex = i - 1

Next i

End Sub
--
jb


"Paul" wrote:

Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

Peter,

Thanks for your reply,

I'm getting a 1004 error when running your code, any suggestions?

Cheers,

Paul.

"Peter T" wrote:

Try this -

Sub myTabOrder()
' dump control names to cells
' select re-sorted controls in tab index order high first
' change bDump True/false and form name to suit
Dim bDup As Boolean
Dim i As Long
Dim cel As Range
Dim ctr As Object

bDump = True ' read

With ThisWorkbook.VBProject.VBComponents("UserForm1").D esigner

If bDump Then ' read

For Each ctr In .Controls
' this order will always be the order
' controls were added to the form
i = i + 1
Cells(i, 1) = ctr.Name
Cells(i, 2) = ctr.TabIndex

Next
''''''''''''''''''''''
'' discard any controls whose container is not the form
'' eg in frames or page controls
'' Sort controls in order highest tabindex to lowest
'' note first tabindex is 0,
'' eg if nine controls in order is 8-0
'' Select cells with the control names
'''''''''''''''''''''''''''

Else
For Each cel In Selection
.Controls(cel.Value).TabIndex = 0
Next
End If
End With

End Sub


Regards,
Peter T
"Paul" wrote in message
...
Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul

Set tabindex automatically
 

When something doesn't work it's helpful for others to have an idea of what
doesn't work. Eg, I'm quite sure the example posted by John will work,
though potentially it might end up with incorrect tab indexes (change one
can change none, some or all others).

The error with mine, on which line and what's the message. Even though the
code is fine the error could be due to loads of things.

Insert the following as the first lines of code

On Error Resume Next
If ThisWorkbook.VBProject.Name = "dummy" Then:
If Err.Number Then
MsgBox "Ensure ''Trust access to Visual Basic Project''" & vbCr _
& "in Macro Security is allowed"
Exit Sub
End If
On Error GoTo 0

Carefully follow the notes

500 controls ?

Regards,
Peter T





"Paul" wrote in message
...
Peter,

Thanks for your reply,

I'm getting a 1004 error when running your code, any suggestions?

Cheers,

Paul.

"Peter T" wrote:

Try this -

Sub myTabOrder()
' dump control names to cells
' select re-sorted controls in tab index order high first
' change bDump True/false and form name to suit
Dim bDup As Boolean
Dim i As Long
Dim cel As Range
Dim ctr As Object

bDump = True ' read

With ThisWorkbook.VBProject.VBComponents("UserForm1").D esigner

If bDump Then ' read

For Each ctr In .Controls
' this order will always be the order
' controls were added to the form
i = i + 1
Cells(i, 1) = ctr.Name
Cells(i, 2) = ctr.TabIndex

Next
''''''''''''''''''''''
'' discard any controls whose container is not the form
'' eg in frames or page controls
'' Sort controls in order highest tabindex to lowest
'' note first tabindex is 0,
'' eg if nine controls in order is 8-0
'' Select cells with the control names
'''''''''''''''''''''''''''

Else
For Each cel In Selection
.Controls(cel.Value).TabIndex = 0
Next
End If
End With

End Sub


Regards,
Peter T
"Paul" wrote in message
...
Hi,

I've got a userform with a lot of textboxes, comboboxes etc. During
development there was a lot dragging those boxes all over the form, and
offcourse the tabindexes are now totally off!

Is there a way to, via a macro or something, to automatically set the
tabindexes the way I would like them?

Cheers,

Paul