◎一例として下図のようなダイアログを作成



Option Explicit
Option Base 1
Option Private Module


'クラスモジュールに以下を記述
Option Explicit

Private WithEvents mySpin As MSForms.SpinButton
Private intIndex As Integer

Public Property Get Entity() As MSForms.SpinButton
      Set Entity = mySpin
End Property

Public Property Let Entity(ByVal ctlNewValue As MSForms.SpinButton)
      Set mySpin = ctlNewValue
End Property

Public Property Get index() As Integer
      index = intIndex
End Property

Public Property Let index(ByVal intNewValue As Integer)
      intIndex = intNewValue
End Property

'スピンボタンに表示させたいテキストボックスを別クラスモジュールに記述
Option Explicit

Private WithEvents myTBox As MSForms.TextBox
Private intIndex As Integer

Public Property Get Entity() As MSForms.TextBox
      Set Entity = myTBox
End Property

Public Property Let Entity(ByVal ctlNewValue As MSForms.TextBox)
      Set myTBox = ctlNewValue
End Property

Public Property Get index() As Integer
      index = intIndex
End Property

Public Property Let index(ByVal intNewValue As Integer)
      intIndex = intNewValue
End Property

'用意したフォームを活用する1例
Option Explicit
Option Base 1

Dim newTextBox(INPUTFIELD) As New ClassTextBox
Dim newSpinBtn(INPUTFIELD) As New ClassSpinButton


'ユーザーフォーム初期化
Private Sub UserForm_Initialize()

    Dim bytHrs(HOURS) As Byte
    Dim bytMin(MINUTES) As Byte
    Dim bytHourBegin As Byte
    Dim bytHourClose As Byte
    Dim bytMinsBegin As Byte
    Dim bytMinsClose As Byte
    Dim bytDefHour1 As Byte
    Dim bytDefHour2 As Byte
    Dim bytDefMins1 As Byte
    Dim bytDefMins2 As Byte
    Dim bytIndex    As Byte
    Dim bytBegin    As Byte
    Dim bytEnd      As Byte
    Dim myTextBox As New Collection
    Dim mySpinBtn As New Collection
    Dim count As Integer
    Dim limit As Integer
    
    bytIndex = TIMEINDEX
    bytHourBegin = HOURBEGIN
    bytHourClose = HOURCLOSE
    bytMinsBegin = MINSBEGIN
    bytMinsClose = MINSCLOSE
    bytDefHour1 = DEFAULT_HOUR1
    bytDefHour2 = DEFAULT_HOUR2
    bytDefMins1 = DEFAULT_MINUTE1
    bytDefMins2 = DEFAULT_MINUTE2
    limit = INPUTFIELD

    'テキストボックス配列を生成
    With myTextBox
        .Add Item:=TextBox1     '
        .Add Item:=TextBox2
        .Add Item:=TextBox3
        .Add Item:=TextBox4
    End With
    
    'スピンボタン配列を生成
    With mySpinBtn
        .Add Item:=SpinButton1
        .Add Item:=SpinButton2
        .Add Item:=SpinButton3
        .Add Item:=SpinButton4
    End With
    
    '拡張テキストボックスクラスにインスタンス代入
    count = bytIndex
    For count = count To limit
        Set newTextBox(count) = New ClassTextBox
        With newTextBox(count)
            .Entity = myTextBox(count)
            .index = count
        End With
    Next count
    
    '拡張スピンボックスクラスにインスタンス代入
    count = bytIndex
    For count = count To limit
        Set newSpinBtn(count) = New ClassSpinButton
        With newSpinBtn(count)
            .Entity = mySpinBtn(count)
            .index = count
        End With
    Next count

    '「時」を設定
    count = bytIndex
    limit = UBound(bytHrs)
    For count = count To limit Step 1
        bytHrs(count) = count - 1
    Next count
    
    '「分」を設定
    count = bytIndex
    limit = UBound(bytMin)
    For count = count To limit Step 1
        bytMin(count) = count - 1
    Next count

    '拡張スピンボタンを初期化
    limit = UBound(newSpinBtn)
    count = bytIndex
    For count = count To limit - 2 Step 1   'SpinButton1&2
        With newSpinBtn(count).Entity
            .Min = bytHourBegin - 1
            .Max = bytHourClose + 1
            .Value = .Min
        End With
    Next count
    For count = count To limit Step 1       'SpinButton3&4
        With newSpinBtn(count).Entity
            .Min = bytMinsBegin - 1
            .Max = bytMinsClose + 1
            .Value = .Min
        End With
    Next count

    '拡張テキストボックスを初期化
    count = bytIndex
    newTextBox(1).Entity.Value = Format(bytDefHour1, "0#")
    newTextBox(2).Entity.Value = Format(bytDefHour2, "0#")
    newTextBox(3).Entity.Value = Format(bytDefMins1, "0#")
    newTextBox(4).Entity.Value = Format(bytDefMins2, "0#")

    '画面状態を復帰
    With Application
        .ScreenUpdating = True
        .Cursor = xlDefault
    End With

End Sub
'「キャンセル」押下時
Private Sub SetCancel_Click()
    pubFormState = vbCancel
    Unload SetTargetTime
End Sub
'「OK」押下時
Private Sub SetOK_Click()
    Dim strBuf1  As String
    Dim strBuf2  As String
    Dim strBuf3  As String
    Dim strBuf4  As String
    
    strBuf1 = newTextBox(1).Entity.Value
    strBuf2 = newTextBox(2).Entity.Value
    strBuf3 = newTextBox(3).Entity.Value
    strBuf4 = newTextBox(4).Entity.Value
    
    pubTimeVal1 = Format(strBuf1 & strBuf3, "0000")    '"開始時間" hh+mm
    pubTimeVal2 = Format(strBuf2 & strBuf4, "0000")    '"終了時間" hh+mm
    
    'Do not allow Start=End
    If pubTimeVal1 = pubTimeVal2 Then Exit Sub
    
    pubFormState = vbOK
    Unload SetTargetTime
End Sub

Private Sub SpinButton1_Change()
    With SpinButton1
        If .Value = .Min Then .Value = .Max - 1
        If .Value = .Max Then .Value = .Min + 1
        newTextBox(1).Entity.Value = Format(.Value, "0#")
    End With
End Sub

Private Sub SpinButton2_Change()
    With SpinButton2
        If .Value = .Min Then .Value = .Max - 1
        If .Value = .Max Then .Value = .Min + 1
        newTextBox(2).Entity.Value = Format(.Value, "0#")
    End With
End Sub

Private Sub SpinButton3_Change()
    With SpinButton3
        If .Value = .Min Then .Value = .Max - 1
        If .Value = .Max Then .Value = .Min + 1
        newTextBox(3).Entity.Value = Format(.Value, "0#")
    End With
End Sub

Private Sub SpinButton4_Change()
    With SpinButton4
        If .Value = .Min Then .Value = .Max - 1
        If .Value = .Max Then .Value = .Min + 1
        newTextBox(4).Entity.Value = Format(.Value, "0#")
    End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim myVal As Integer

    myVal = Val(TextBox1.Value)

    With SpinButton1
        Select Case myVal
            Case .Min To .Max
                .Value = myVal
            Case Else
                .Value = .Min + 1
            End Select
    End With

    TextBox1.Value = Format(SpinButton1.Value, "0#")

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim myVal As Integer

    myVal = Val(TextBox2.Value)

    With SpinButton2
        Select Case myVal
            Case .Min To .Max
                .Value = myVal
            Case Else
                .Value = .Min + 1
            End Select
    End With

    TextBox2.Value = Format(SpinButton2.Value, "0#")

End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim myVal As Integer

    myVal = Val(TextBox3.Value)

    With SpinButton3
        Select Case myVal
            Case .Min To .Max
                .Value = myVal
            Case Else
                .Value = .Min + 1
            End Select
    End With

    TextBox3.Value = Format(SpinButton3.Value, "0#")

End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim myVal As Integer

    myVal = Val(TextBox4.Value)

    With SpinButton4
        Select Case myVal
            Case .Min To .Max
                .Value = myVal
            Case Else
                .Value = .Min + 1
            End Select
    End With

    TextBox4.Value = Format(SpinButton4.Value, "0#")

End Sub