UserForm 컨트롤 동적 추가 방법 - Heeyoung-Ahn/Excel_VBA GitHub Wiki

기본문법

Set ctl = Me.Controls.Add(ControlClass[, Name, Visible])

• Name 생략 시 TextBox1 형태로 이름 부여됨
• Visible 생략 시 True로 설정됨
• ControlClass
  • 예: "Forms.TextBox.1"
• 샘플

Dim txtBox as MSForms.TextBox
Set txtBox = Me.Controls.Add("Forms.TextBox.1", "txt_sample")
With txtBox
  .Top = 20
  .Left = 20
  .Width = 30
End With

• 동적으로 추가된 컨트롤에 접근하는 방법 Me.Controls("txt_sample")
  • Me.txt_sample 방식으로는 접근 불가
• 컨트롤 삭제 시 Me.Controls.Remove "txt_sample"
• 동적으로 추가된 컨트롤에 작업 시

Dim obj AS Object
Dim i As Integer

i = 1
For Each obj In me.Controls
  If Left(obj.Name, 3) = "txt" Then
    obj.Text = Cells(i, 1).Value
  End If
Next obj

---

샘플코드1

Dim Mycmd As Control

Private Sub CommandButton1_Click()
 
    Set Mycmd = Me.Controls.Add("Forms.commandbutton.1")
    With Mycmd
        .Left = 18
        .Top = 150
        .Width = 175
        .Height = 20
        .Name = "cmd_test"
        .Caption = "This is fun." & Mycmd.Name
    End With
 
End Sub
 
Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    Label1.Caption = "Control was Added."
End Sub

---

샘플코드2

Private Sub UserForm_Initialize()

    Dim txtB As MSForms.TextBox
    Dim i As Integer, j As Integer, cntC As Integer, cntR As Integer
    
    '프레임 높이
    cntR = 20: cntC = 4
    If cntR > 17 Then '16개 까지는 기본 설정으로 커버 가능
        Me.fra2.ScrollHeight = 72 + 16.5 * cntR + 10
    End If
    
    '법인명 넣기
    For i = 1 To cntR
        Set txtB = Me.MultiPage1("task_pic").fra2.Add("forms.textbox.1", "txt_co" & i)
        With txtB
            .Height = 16.5
            .Width = 100
            .Top = 72 + (16.5 * (i - 1))
            .Left = 12
            .BorderColor = &HE0E0E0
            .BorderStyle = fmBorderStyleSingle
            .TextAlign = fmTextAlignLeft
            .Font.Name = "맑은고딕"
            .Font.Bold = False
            .Font.Size = 9
            .Locked = True
            .text = "법인" & i
        End With
    Next i
    
    '완료일 넣기
    For i = 1 To cntR: For j = 1 To cntC
        Set txtB = Me.MultiPage1("task_pic").fra2.Add("forms.textbox.1", "txt_item" & j & "_dt" & i)
        With txtB
            .Height = 16.5
            .Width = 65
            .Top = 72 + (16.5 * (i - 1))
            .Left = 112 + (110 * (j - 1))
            .BorderColor = &HE0E0E0
            .BorderStyle = fmBorderStyleSingle
            .TextAlign = fmTextAlignCenter
            .Font.Name = "맑은고딕"
            .Font.Bold = False
            .Font.Size = 9
            .Locked = True
            .text = "2020-06-17"
        End With
    Next j: Next i
    
    '입력자 넣기
    For i = 1 To cntR: For j = 1 To cntC
        Set txtB = Me.MultiPage1("task_pic").fra2.Add("forms.textbox.1", "txt_item" & j & "_ur" & i)
        With txtB
            .Height = 16.5
            .Width = 45
            .Top = 72 + (16.5 * (i - 1))
            .Left = 177 + (110 * (j - 1))
            .BorderColor = &HE0E0E0
            .BorderStyle = fmBorderStyleSingle
            .TextAlign = fmTextAlignCenter
            .Font.Name = "맑은고딕"
            .Font.Bold = False
            .Font.Size = 9
            .Locked = True
            .text = "김복음"
        End With
    Next j: Next i
    
    '예결산금액 넣기
    For i = 1 To cntR: For j = 1 To 2
        Set txtB = Me.MultiPage1("task_pic").fra2.Add("forms.textbox.1", "txt_item" & j & "_amt" & i)
        With txtB
            .Height = 16.5
            .Width = 70
            .Top = 72 + (16.5 * (i - 1))
            .Left = 552 + (115 * (j - 1))
            .BorderColor = &HE0E0E0
            .BorderStyle = fmBorderStyleSingle
            .TextAlign = fmTextAlignRight
            .Font.Name = "맑은고딕"
            .Font.Bold = False
            .Font.Size = 9
            .Locked = True
            .text = Format("123456", "#,##0원")
        End With
    Next j: Next i
    
    '예결산건수 넣기
    For i = 1 To cntR: For j = 1 To cntC
        Set txtB = Me.MultiPage1("task_pic").fra2.Add("forms.textbox.1", "txt_item" & j & "_cnt" & i)
        With txtB
            .Height = 16.5
            .Width = 45
            .Top = 72 + (16.5 * (i - 1))
            .Left = 622 + (115 * (j - 1))
            .BorderColor = &HE0E0E0
            .BorderStyle = fmBorderStyleSingle
            .TextAlign = fmTextAlignCenter
            .Font.Name = "맑은고딕"
            .Font.Bold = False
            .Font.Size = 9
            .Locked = True
            .text = Format("1234", "#,##0건")
        End With
    Next j: Next i
    
End Sub
Private Sub cmd1_Click()
    Me.Controls("txt_co1").Text = "법인1수정"
End Sub