Private Sub UserForm_Initialize() Dim NewChkBx As MSForms.CheckBox Dim rngSource As Range Dim rngSource2 As Range Dim rngSource3 As Range Dim rngSource4 As Range Dim rngSource5 As Range Dim Quantity_definition_1 As Range Dim Quantity_definition_2 As Range Dim Quantity_definition_3 As Range Dim Quantity_definition_4 As Range Dim Quantity_definition_5 As Range Dim TopPos As Integer Dim MaxWidth As Long
With Worksheets("AppSyncData")
Set rngSource = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
Set rngSource2 = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp))
Set rngSource3 = .Range("H2", .Cells(.Rows.Count, "H").End(xlUp))
Set rngSource4 = .Range("I2", .Cells(.Rows.Count, "I").End(xlUp))
Set rngSource5 = .Range("J2", .Cells(.Rows.Count, "J").End(xlUp))
End With
TopPos = 15
MaxWidth = 0
For Each Quantity_definition_1 In rngSource
If Quantity_definition_1.Value <> "" Then
Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
With NewChkBx
.Caption = Quantity_definition_1.Value
.Left = 5
.Top = TopPos
.AutoSize = True
If .Width > MaxWidth Then MaxWidth = .Width
End With
TopPos = TopPos + 15
End If
Next Quantity_definition_1
TopPos = 15
For Each Quantity_definition_2 In rngSource2
If Quantity_definition_2.Value <> "" Then
Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
With NewChkBx
.Caption = Quantity_definition_2.Value
.Left = 50
.Top = TopPos
.AutoSize = True
If .Width > MaxWidth Then MaxWidth = .Width
End With
TopPos = TopPos + 15
End If
Next Quantity_definition_2
TopPos = 15
For Each Quantity_definition_3 In rngSource3
If Quantity_definition_3.Value <> "" Then
Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
With NewChkBx
.Caption = Quantity_definition_3.Value
.Left = 95
.Top = TopPos
.AutoSize = True
If .Width > MaxWidth Then MaxWidth = 500
End With
TopPos = TopPos + 15
End If
Next Quantity_definition_3
TopPos = 15
For Each Quantity_definition_4 In rngSource4
If Quantity_definition_4.Value <> "" Then
Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
With NewChkBx
.Caption = Quantity_definition_4.Value
.Left = 135
.Top = TopPos
.AutoSize = True
If .Width > MaxWidth Then MaxWidth = 500
End With
TopPos = TopPos + 15
End If
Next Quantity_definition_4
TopPos = 15
For Each Quantity_definition_5 In rngSource5
If Quantity_definition_5.Value <> "" Then
Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
With NewChkBx
.Caption = Quantity_definition_5.Value
.Left = 180
.Top = TopPos
.AutoSize = True
If .Width > MaxWidth Then MaxWidth = 500
End With
TopPos = TopPos + 15
End If
Next Quantity_definition_5
Me.Width = MaxWidth + 40
Me.Height = TopPos + 40
End Sub
Aucun commentaire:
Enregistrer un commentaire