[RESOLVED] Populate Tabstrip

[Resource Dragon]

I have a userform with a tabstrip in it. The tabstrip has a variety of labels, text boxes &c that are supposed to slurp up information from an excel worksheet. In the code that follows the captions for the tabs are values from the first column of the spreadsheet and there are two text boxes that are supposed to take values from the 4th and 5th columns. Each tab is meant to have data from a separate row of the spreadsheet.

The problem is that while the coding for the tabs works, the values in the text boxes only pick up the values for the largest value of i, regardless of which tab I click on.

Private Sub UserForm_Initialize()
Call Tab_Name
End Sub

Sub Tab_Name()
Dim i As Integer
Dim fecha(6) As Date
Dim amt(6) As Currency

For i = 0 To TabStrip1.Count - 1
TabStrip1.Tabs(i).Caption = Sheets("Sheet1").Cells(2 + i, 1)
fecha(i + 1) = Sheets("Sheet1").Cells(2 + i, 4)
amt(i + 1) = Sheets("Sheet1").Cells(2 + i, 5)
txt_date = Format(fecha(i + 1), "dd-mmm-yy")
txt_amt = Format(amt(i + 1), "$###,##0.00")

Next i
End Sub

In other words, how do I get the information that the user sees when they click on tab1 to correspond to the information in row 2 of the spread sheet, the information they see when they click in tab2 correspond to row 3 of the spread sheet, etc?

Also, how do I code to add another tab to a tabstrip?

[Resource Dragon]

Sorry to ask a question and then answer part of it myself. The following code populates fields so that selecting a given tab gives the corresponding information. I would, however, still be interested in a more elegant solution. (I’ve also introduced a label called lbl_traveller into the coding, in addition to the two text boxes I included in the original question.)

Private Sub UserForm_Initialize()
Call Tab_Name
End Sub
------------------------
Sub Tab_Name()
' This sub pulls tracking numbers off the spreadsheet and uses them to populate the tab names.
Dim i As Integer

For i = 0 To TabStrip1.Count - 1

TabStrip1.Tabs(i).Caption = Sheets("Sheet1").Cells(i + 2, 1)

Next i

End Sub
------------------------
Private Sub TabStrip1_Change()
Call Tab_Contents
End Sub
------------------------
Sub Tab_Contents()

'This sub matches the tab value to the appropriate row in the spreadsheet and pulls out the data.

Dim j As Integer
Dim fecha(6) As Date
Dim amt(6) As Currency
Dim traveller(6) As String

For j = 0 To TabStrip1.Count - 1
If TabStrip1.Value = j Then
traveller(j + 1) = Sheets("Sheet1").Cells(2 + j, 2)
fecha(j + 1) = Sheets("Sheet1").Cells(2 + j, 4)
amt(j + 1) = Sheets("Sheet1").Cells(2 + j, 5)
lbl_traveller.Caption = traveller(j + 1)
txt_date = Format(fecha(j + 1), "dd mmm yy")
txt_amt = Format(amt(j + 1), "$###,##0.00")
End If
Next j
End Sub
------------------------
I tried calling both subs with the UserForm_Initialize event, but I was back with the original problem.

I’d still like to know how to code for a variable number of tabs in a tabstrip so that I can add or delete them as required. Thanks.

[Ecniv]

if each tab refers to a row... can you use the tab index (+1 or +2) to get the rows data? Would save you looping. You may be able to pass the active tabs index, otherwise it would be on the tab change somehow?


eg
tab1 changed to tab 3

tabindex = 3 'active tab index

txt1 = sht.cells(tabindex+2,4)
txt2 = sht.cells(tabindex+2,5)

just a thought, post up if you try a method using it...

[Resource Dragon]

I'll mark this one as resolved. The code
Dim n As Integer
n = ActiveCell.Row
solves a number of problems.