Having a tabstrip open only when called by a textbox

[KevinMMO]

Hi, I'm new to the forum and I would like to ask a question regarding a problem that I'm having with a userform. I have used this page as a way to learn more of excel and solve past doubts.

What I'm doing is a userform where I want to ask the number of failures using a textbox, and then each failure has three fields that are "type", "part number" and "rework", each of them having a specific textbox. What I think that would be a good idea is to use tabstrip to have a tab depending on the number of failures. For example: I have 2 failures, so I go to a tab named "2", which has the three fields mentioned and 2 textboxes for each one. Is there a way to link the first textbox to the tabs? I would also like to know if there is a way to hide the rest of the tabs when they are not called. Any help with this will be greatly appreciated. Also, if there's an easier way to do this, it would be nice to know. Thanks.

 

[My Aswer Is This]

I have used TabStrips some but not often. I see Tabstrips as a way to display Sheet data. I do not believe you can add data to a TabStript. But you can add data to the sheet range which is displayed in the TabStrip.

 

[My Aswer Is This]

I've looked at Tabstrips a little more:
What do you mean by field when you say

which has the three fields mentioned and 2 textboxes for each one​

And yes you can hide tabs.
And not sure what you mean when you say

Is there a way to link the first textbox to the tabs?​

So your only wanting one tab visible at a time?

Show me any code you have already written. Maybe I will see what your overall plan is.
What is the ultimate goal.

 

[jproffer]

Why not just make the other textboxes visible/invisible based on the entry in the first.

Not in a position to test, but I'm thinking something like:

c = Tb1.Value
    For i = 1 to c
        Tb & i.Visible = True
    Next i

Adjust names accordingly, of course. With a default setting of False for the visible property of each Textbox.

 

[mikerickson]

A TabStrip is essentially a horizontal one column ListBox.

Which brought up the idea of using a 4 column List box to control this data entry. Create a userform with 5 textboxes, tbxFailureCount, tbxType, tbxPartNumber and tbxReWork and a ListBox, ListBox1. Then you could use code like.

Option Explicit

Private Sub ListBox1_Click()
    With ListBox1
        If .ListIndex <> -1 Then
            Me.tbxType = .List(.ListIndex, 1)
            Me.tbxPartNumber = .List(.ListIndex, 2)
            Me.tbxRework = .List(.ListIndex, 3)
            Me.tbxPartNumber.Visible = True
            Me.tbxRework.Visible = True
            Me.tbxType.Visible = True
        Else
            Me.tbxPartNumber.Visible = False
            Me.tbxRework.Visible = False
            Me.tbxType.Visible = False
        End If
    End With
End Sub

Private Sub tbxPartNumber_Change()
    With ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 2) = tbxPartNumber.Text
        End If
    End With
End Sub

Private Sub tbxRework_Change()
    With ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 3) = tbxRework.Text
        End If
    End With
End Sub

Private Sub tbxType_Change()
    With ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 1) = tbxType.Text
        End If
    End With
End Sub

Private Sub tbxFailureCount_AfterUpdate()
    Dim i As Long
    
    With tbxFailureCount
        If IsNumeric(.Text) Then
            If 0 < Val(.Text) And Val(.Text) < 4 Then
            StartListBox
            For i = 1 To Val(.Text)
                ListBox1.AddItem "Failure " & i
            Next i
            ListBox1.Visible = True
            .Visible = False
            End If
        End If
        If .Visible Then MsgBox "Enter a number between 1 and 3"
    End With
End Sub

Sub StartListBox()
    With ListBox1
        .Clear
        .ColumnCount = 4
        .AddItem vbNullString
        .List(0, 1) = "Type"
        .List(0, 2) = "Part Number"
        .List(0, 3) = "Rework"
    End With
End Sub
Private Sub UserForm_Initialize()
    StartListBox
    ListBox1_Click
    ListBox1.Visible = False
End Sub

Note the extra textbox to take the focus after entering the number of failures to be recorded.

 

[KevinMMO]

I like the idea that you're saying, but when copying the code and running it, only a textbox shows and nothing happens when I introduce a value. Is there anything am I doing wrong? I created a userform, the txtboxes with their respective names and the listbox. Maybe it's something I'm ignoring.

 

[KevinMMO]

I'm trying to show in this picture what I would like to do. The issue is with the tabstrip part section, where I need to ask the user how many defective parts where found, followed by asking 3 other things. Maybe there is an easier way to put this into the userform and doesn't use tabstrip.

 

[KevinMMO]

Here you can see what I'm trying to achieve. The issue is in the part related with asking the user for the number of defective parts found, followed by 3 related fields that the user has to fill about each defective part.

https://drive.google.com/file/d/1BO-JfgbJFg_BR9EvhJBUPzb44rSvzfi4/view?usp=drivesdk

 

[mikerickson]

If you comment out all of the lines that control the .Visible property, it should show what I have in mind.

As written, enter 2 into the textbox and press enter. If that does nothing, do the same to the other textbox.