|
▼きしやん さん:
>Private Sub UserForm_Initialize()
> '各TextBoxとリンクするシート上のセル範囲
> Set GSourceRange = Sheet1.Range("BB1").Resize(20)
> With GSourceRange
> TextBox2.ControlSource = .Item(1).Address(, , , True)
> TextBox5.ControlSource = .Item(2).Address(, , , True)
> TextBox8.ControlSource = .Item(3).Address(, , , True)
> TextBox9.ControlSource = .Item(4).Address(, , , True)
> TextBox10.ControlSource = .Item(5).Address(, , , True)
> TextBox11.ControlSource = .Item(6).Address(, , , True)
> TextBox12.ControlSource = .Item(7).Address(, , , True)
> TextBox13.ControlSource = .Item(8).Address(, , , True)
> TextBox14.ControlSource = .Item(9).Address(, , , True)
> TextBox15.ControlSource = .Item(10).Address(, , , True)
> TextBox16.ControlSource = .Item(11).Address(, , , True)
> TextBox17.ControlSource = .Item(12).Address(, , , True)
> TextBox18.ControlSource = .Item(13).Address(, , , True)
> TextBox21.ControlSource = .Item(14).Address(, , , True)
> TextBox24.ControlSource = .Item(15).Address(, , , True)
> TextBox27.ControlSource = .Item(16).Address(, , , True)
> TextBox30.ControlSource = .Item(17).Address(, , , True)
> TextBox33.ControlSource = .Item(18).Address(, , , True)
> TextBox36.ControlSource = .Item(19).Address(, , , True)
> TextBox39.ControlSource = .Item(20).Address(, , , True)
> End With
>End Sub
ですが、
以下のようにまとめることもできますね。
Private Sub UserForm_Initialize()
Dim tno, i As Long
Dim ss As String
Set GSourceRange = Sheet1.Range("BB1").Resize(20)
ss = "2 5 8 9 10 11 12 13 14 15 16 17 18 21 24 27 30 33 36 39"
For Each tno In Split(ss)
i = i + 1
Controls("TextBox" & tno).ControlSource _
= GSourceRange.Item(i).Address(, , , True)
Next
End Sub
|
|