Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox inserted on an Excel sheet does not allow TABINDEX or SetF | Excel Programming | |||
Order the TabIndex | Excel Programming | |||
multipage tabindex | Excel Programming | |||
Problem with Tabindex | Excel Worksheet Functions | |||
TabIndex property | Excel Programming |