Excel 2016 VBA: Disable all tabs in tabstrip except for selected tab

[jedwardo]

I have a tabstrip named ufRooms.tsWoodRun. Trying to disable all other tabs when selecting one so you can't accidentally select another before pressing the enter key. Anyone know if theres a way to do that?

Thanks,
Jordan

 

[Domenic]

If you disable all other tabs by selecting a tab, then you won't be able to select any other tab since they've been disabled. Maybe you could do it this way. When the userform is first shown, the code makes sure that the first tab is active, and then updates the tabs by disabling all other tabs. Then, after the user enters any required information and clicks on the commandbutton, you have your code do whatever it needs to do first, then the code enables all of the tabs, then the next tab is activated, and then the tabs are updated so that the other ones are disabled. If it sounds like something that might work for you, here's the code that needs to be placed in the code module for the userform. Depending on your actual requirements, though, you may need to modify the code.

Option Explicit

Private Sub CommandButton1_Click()
    Dim oTab As MSForms.Tab
    'Your code here
    '
    '
    With Me.TabStrip1
        If .Value < .Tabs.Count - 1 Then 'the active tab isn't the last tab
            For Each oTab In .Tabs
                oTab.Enabled = True
            Next oTab
            .Value = .Value + 1 'go to the next tab
            Call UpdateTabStrip(Me.TabStrip1)
        End If
    End With
End Sub


Private Sub UserForm_Initialize()
    Dim oTab As MSForms.Tab
    Me.TabStrip1.Value = 0 'make sure the first tab is active when the form is shown
    Call UpdateTabStrip(Me.TabStrip1)
End Sub


Private Sub UpdateTabStrip(oTabStrip As MSForms.TabStrip)
    Dim oTab As MSForms.Tab
    For Each oTab In oTabStrip.Tabs
        If oTab.Index <> oTabStrip.Value Then
            oTab.Enabled = False
        End If
    Next oTab
End Sub

Change the name of the tabstrip (TabStrip1) and commandbutton (CommandButton1) accordingly.

Hope this helps!

 

[jedwardo]

Haha, hail storm knocked me out of commission while trying to respond. Pretty much exactly what I was looking for, thanks Domenic. About to play with this. You think it's a mute point to try and use something like this if no tabs are active when the form first loads? The way I have it set right now is no tabs active at initiation. Then about 20 different tabs to run through for each room of the house as you're doing the bid. Usually only a few tabs in each room ever receiving focus.

 

[jedwardo]

This is what I have to generate the tabstrip at the moment. Had it set to initialize with no tabs selected because everytime you select one it pulls up the userform keyboard to enter a value in tbxWoodQuant

Private Sub UserForm_Initialize()

Dim c As Excel.Range
Dim t As Object

'With cbxRooms
'    .ColumnCount = 2
'    .ColumnWidths = "135,135"
'    .List = Range("Room2").Value
'End With

Me.tsWood.Value = -1                                        'tabstrip no index start

    Do While tsWoodRun.Tabs.Count > 1
        tsWoodRun.Tabs.Remove (1)                           'remove all but first tab
    Loop

        For Each c In Worksheets("Lists").Range("Doors")

            If c.Value <> vbNullString Then                 'if range value not ""
                Set t = tsWoodRun.Tabs.Add                  'create new tab
                t.Caption = c.Text                          'name tab
            End If
        Next

ufRooms.tsWoodRun.Tabs.Remove (0)                           'tabstrip remove 1st tab after populating captions
Me.tsWoodRun.Value = -1



End Sub

Private Sub tsWood_Change()                     'tabstrip wood runtime nameranges

Dim varTab As Variant
Dim r As String

varTab = ufRooms.tsWood.Value

    If Me.tsWood.Value = -1 Then
        Me.fWood.Visible = False: GoTo fit
    ElseIf Me.tsWood.Value >= 0 Then
        Me.fWood.Visible = True
    End If
    
    r = ufRooms.tsWood.SelectedItem.Caption

    Do While tsWoodRun.Tabs.Count > 1
        tsWoodRun.Tabs.Remove (1)
    Loop

       Select Case varTab
       
           Case Is = 0, 1, 2, 3
    
               For Each c In Worksheets("Lists").Range(r)
                   If c.Value <> vbNullString Then
                       Set t = tsWoodRun.Tabs.Add
                       t.Caption = c.Text
                   End If
               Next
              
       End Select
       
ufRooms.tsWoodRun.Tabs.Remove (0)
Me.tsWoodRun.Value = -1

fit:
End Sub


Private Sub tsWoodRun_Click(ByVal Index As Long)
Me.tbxWoodQuant.SetFocus
End Sub

 

[jedwardo]

Awesome got it running. Thank you much Domenic. Here's my solution:

Option Explicit

Private Sub cbWoodReturn_Click()

    Dim oTab As MSForms.Tab
    'Your code here
    '
    '
    With Me.tsWoodRun
        If .Value < .Tabs.Count - 1 Then 'the active tab isn't the last tab
            For Each oTab In .Tabs
                oTab.Enabled = True
            Next oTab
            .Value = .Value + 1 'go to the next tab
            Call EnableTabStrip(Me.tsWoodRun)
        End If
        .Value = -1
    End With

End Sub

Made a second one for enabling, the tabs:

Private Sub UpdateTabStrip(oTabStrip As MSForms.TabStrip)
    Dim oTab As MSForms.Tab
    For Each oTab In oTabStrip.Tabs
        If oTab.Index <> oTabStrip.Value Then
            oTab.Enabled = False
        End If
    Next oTab
End Sub
Private Sub EnableTabStrip(oTabStrip As MSForms.TabStrip)
    Dim oTab As MSForms.Tab
    For Each oTab In oTabStrip.Tabs
        If oTab.Index <> oTabStrip.Value Then
            oTab.Enabled = True
        End If
    Next oTab
End Sub

Private Sub UserForm_Initialize()

Dim oTab As MSForms.Tab

'    Me.TabStrip1.Value = 0 'make sure the first tab is active when the form is shown
Call UpdateTabStrip(Me.tsWoodRun)

Solved

 

[Domenic]

That's great, I'm glad to hear that you've been able to adapt my code for your needs.

Cheers!