When dynamically creating textboxes/comboboxes on a userform, I create a combobox with information in another Excel workbook. (Not in the workbook I created the code/userform in. Nor can it be because it may change/get issued in the future & I don't want to have to move code into it).
After I create the combobox, I call another sub, Populate_Steel_List, to populate it. After everything has been generated, when tabbing through the textboxes/comboboxes actual Tab values are being placed in the textboxes/comboboxes, instead of moving on to the next textbox/combobox on the userform!
If I comment out calling for Populate_Steel_List, the userform's tabbing works fine. So I know calling it is causing the problem.
I tried adding a For-Next (For Each-Next as well) loop for each control, setting the .TabKeyBehavior = False. It didn't seem to do anything.
Here is my code:
Private Sub NumberMembers_Change()
' ....there is some other code in this sub, but has been tested,
' ie. commented out, till I found out
' where the offending code actually is
Dim TextBox8 As Object ' have also tried As Control; both run fine,
' but neither fix TabKeyBehavior on the userform
' Create Member Cross Section List Textboxes
Set TextBox8 = Controls.Add("Forms.ComboBox.1")
With TextBox8
.Name = "MemberSectionList" & i
.height = 17
.Width = 90
.Left = 494
.Top = 20 * i
.TabIndex = NextTabNumber + 3
End With
TextBox8Name(i) = TextBox8.Name
MembSectCurrBox = TextBox8.Name
Populate_Steel_List 'If I comment this out, the problem goes away,
' but that's not gonna work
End Sub
' ...and here's the code for Populate_Steel_List
Private Sub Populate_Steel_List()
Dim fd As Office.FileDialog
'Dim TextFile As Integer
' Check to see if log file has already been created
' Skip to using the path to find the excel document to have VBA read from it
If Len(FilePathToAISCShapesDatabase) > 0 Then GoTo Skip
If FilePathToAISCShapesDatabase = vbNullString Then
ChDir (Replace(ActiveWorkbook.FullName, ActiveWorkbook.Name, ""))
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select the latest AISC shapes database file."
' Clear out the current filters, and add our own.
.Filters.Clear
.InitialFileName = "AISC Shapes Database v14.1.xlsm"
.Filters.Add "Excel", "*.xlsm, *.xlsx"
.Filters.Add "Excel 97–Excel 2003", "*.xls"
If .Show = True Then
FilePathToAISCShapesDatabase = .SelectedItems(1)
'Now I have the filepath to the AISC Shapes Database
Else
Exit Sub
End If
End With
' Create textfile in this location:
' Application.ThisWorkbook.Path & "AISCListLoc.txt"
'Open the text file
Open DirFile For Output As #1
'Write some lines of text
Print #1, FilePathToAISCShapesDatabase
'Save & Close Text File
Close #1
End If
Skip:
' Read from file that you have path to
Application.ScreenUpdating = False
Application.Workbooks.Open (FilePathToAISCShapesDatabase)
Dim rng As Range
Set rng = Sheets("Database v14.1").Range("C2:C" & Range("C" & Rows.Count).End(xlUp).row)
Me.Controls(MembSectCurrBox).List = rng.value
ActiveWorkbook.Close False
Application.ScreenUpdating = True
Set rng = Nothing
AppActivate ThisWorkbook.Application
Dim i ' Not sure if I really need to have this here or not;
' I also added it in the UserForm_Initialize(),
' but adding it doesn't seem to help
For i = 1 To Me.Controls.Count
If TypeName(Me.Controls(i - 1)) = "Textbox" Then
Me.Controls(i - 1).TabKeyBehavior = False
End If
Next
End Sub
TextBox8Name(i) is a private variant I used to keep track of how many comboboxes get generated (for limitations of an FEA program down the road).
FilePathToAISCShapesDatabase is a global string (in the module) so the user doesn't always have to point to the AISC shapes spreadsheet after the first time.
I tried not to select anything while in the other workbook, AISC Shapes Database v14.1.xlsm, but maybe I messed up?
