samedi 5 août 2017

Urgent Please Help Me want to include this code to create the check-boxes in a sheet not in the form

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