使用变量/组合框搜索变量，然后在TabStrip中选择相关选项卡

Question

设置是这样的：我有一个UserForm，它是几个服务提供公司中每个公司的TabStrip(每个公司都有选择的接收账单的个人组(。TabStrip上有一个ComboBox和12个TextBox对象(一年中每个月一个(。我有一个定义的全局变量，它在我的所有UserForms之间传递，UserForms是接收账单的组之一。

我的目标：我希望根据当前选择的选项卡填充组合框列表。如果可能，我还希望在初始化UserForm时搜索当前变量，并将其与组合框值相匹配。如果变量不在组合框中，则应循环到下一个选项卡并再次执行搜索。它应该这样做，直到找到变量，然后激活该选项卡并从组合框中选择该值。

这是我当前UserForm设置的图片。希望它能更好地传达我的意图。用户表单图像

Answer 1

这应该有效：

Option Explicit
Private Sub UserForm_Initialize()
Call PriSubPopulateCombobox
End Sub
Private Sub TabStrip1_Change()
Call PriSubPopulateCombobox
End Sub
Private Sub PriSubPopulateCombobox()

'Declarations.
Dim RngCostumerList As Range
Dim RngRangeTarget As Range
Dim WksWorksheet As Worksheet
Dim CbxCombobox As ComboBox
Dim TbsTabstrip As TabStrip

'Setting variables.
Set CbxCombobox = Me.ComboBox1
Set TbsTabstrip = Me.TabStrip1

'Setting WksWorksheet according to the value of TbsTabstrip.
Select Case TbsTabstrip.Value
Case Is = 0
Set WksWorksheet = Worksheets("KDDI")
Case Is = 1
Set WksWorksheet = Worksheets("NTT West")
Case Is = 2
Set WksWorksheet = Worksheets("OT Net")
Case Is = 3
Set WksWorksheet = Worksheets("Advanced Pay")
End Select

'Setting RngCostumerList.
With WksWorksheet
Set RngCostumerList = .Range(.Range("A2"), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1))
End With

'Clearing CbxCombobox.
CbxCombobox.Clear

'Filling CbxCombobox.
For Each RngRangeTarget In RngCostumerList
CbxCombobox.AddItem RngRangeTarget.Value
Next

End Sub

将其放入表单模块中。检查变量设置是否正确，否则进行更改。

报告您遇到的任何问题或最终遇到的错误。如果有效，请将答案标记为已接受。

编辑：在初始化之前选择一个标记

我想出了两种方法。使用Select Case语句，我们有了这个解决方案：

Private Sub UserForm_Initialize()

'Declaration.
Dim StrCostumer As String

'Setting the variable.
StrCostumer = "Advanced Pay"

'Activating the tab accordingly to StrCostumer.
Select Case StrCostumer
Case Is = "KDDI"
Me.TabStrip1.Value = 0
Case Is = "NTT West"
Me.TabStrip1.Value = 1
Case Is = "OT Net"
Me.TabStrip1.Value = 2
Case Is = "Advanced Pay"
Me.TabStrip1.Value = 3
Case Else
MsgBox "No valid costumer found", vbCritical, "Error"
End Select

'Calling PriSubPopulateCombobox.
Call PriSubPopulateCombobox

End Sub

Pro：这是相当严格的，因此很容易检查。缺点：每次有新条目时都需要对其进行编辑。

第二种解决方案检查每个选项卡标题，直到找到想要的客户：

Private Sub UserForm_Initialize()

'Declarations.
Dim StrCostumer As String
Dim BytCostumer As Byte

'Setting the variable.
StrCostumer = "Advanced Pay"

'If an error occurs during the searching, it will probably be cause by an overflow. _
Whatever the reason, in such case the search is terminated.
On Error GoTo CP_No_Tab_Found
For BytCostumer = 0 To Me.TabStrip1.Tabs.Count
'If the wanted costumer is found, the tab is selected.
If Me.TabStrip1.Object(BytCostumer).Caption = StrCostumer Then
Me.TabStrip1.Value = BytCostumer
GoTo CP_Tab_Found
End If
Next
CP_No_Tab_Found:
MsgBox "No valid costumer found", vbCritical, "Error"
CP_Tab_Found:
On Error GoTo 0

'Calling PriSubPopulateCombobox.
Call PriSubPopulateCombobox

End Sub

Pro：新条目不需要编辑。缺点：读起来有点复杂。

在这两种情况下，都可以将StrCostumer设置为匹配公共变量或单元格的值或其他值。在这两种情况下，如果没有找到与StrCostumer匹配的标签，则会弹出一条关键消息，并且无论如何都会显示表单。