ExcelマクロVBA処理3

☆PDF資料はこちら

演習１
次のようなユーザーフォームを作成しがん合を記入することにより、そのデータ数値が行番号になるようにしなさい。また[次へ]ボタンを押すことによりテキストボックスに入れられた数値がクリアされるようにプログラムを完成させなさい。

Private Sub CommandButton2_Click()
Hide
End Sub

Private Sub CommandButton1_Click()
Dim a As Integer
a = TextBox1
Worksheets(“sheet1”).Cells(a, 1).Value = TextBox1
Worksheets(“sheet1”).Cells(a, 2).Value = TextBox2
Worksheets(“sheet1”).Cells(a, 3).Value = TextBox3
Worksheets(“sheet1”).Cells(a, 4).Value = TextBox4

TextBox1 = Clear
TextBox2 = Clear
TextBox3 = Clear
TextBox4 = Clear
End Sub

例題8 色々なコントロールを組み合わせたユーザーフォームを利用できるようにしよう。

Private Sub ComboBox1_Change()
Dim i As Integer
For i = 1 To 40
ComboBox1.AddItem i
Next
Dim y As Integer
For y = 1 To 40
k = Worksheets(“sheet3”).Cells(y, 2)
ComboBox2.AddItem k
Next
End Sub

Private Sub ComboBox2_Change()
Dim i As Integer
For i = 1 To 40
k = Worksheets(“sheet3”).Cells(i, 2)
ComboBox2.AddItem k
Next
End Sub

Private Sub CommandButton1_Click()
Worksheets(“sheet2”).Cells(1, 3).Value = “番号”
Worksheets(“sheet2”).Cells(1, 4).Value = “氏名”
Worksheets(“sheet2”).Cells(1, 5).Value = “性別”
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 3).Value = ComboBox1.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 4).Value = ComboBox2.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 6).Value = TextBox1.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 7).Value = TextBox2.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 8).Value = TextBox3.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 9).Value = TextBox4.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 10).Value = TextBox5.Value
ComboBox1.Value = Clear
ComboBox2 = Clear
OptionButton1.Value = Clear
OptionButton2.Value = Clear

TextBox1 = Clear
TextBox2 = Clear
TextBox3 = Clear
TextBox4 = Clear
TextBox5 = Clear
End Sub

Private Sub CommandButton2_Click()
Hide
End Sub

Private Sub OptionButton1_Click()
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 5).Value = “男”
End Sub

Private Sub OptionButton2_Click()
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 5).Value = “女”
End Sub

例題9 タブリストリップの活用術
TAB１で書き込んだテキストボックス内のデータは、ワークシート2へ書き込み、TAB2で書き込んだテキストボックス内のデータは、ワークシート3へ書き込まれるプログラムを作成せよ。

Private Sub CommandButton1_Click()
If TabStrip1.Value = 0 Then
Worksheets(“sheet2”).Cells(1, 3).Value = TextBox1.Value
Else
Worksheets(“sheet3”).Cells(1, 3).Value = TextBox1.Value
End If
TextBox1.Value = Clear
End Sub

Private Sub CommandButton2_Click()
Hide
End Sub

Private Sub TabStrip1_Change()
TabStrip1(0).Caption = “中間”
TabStrip1(1).Caption = “期末”
End Sub

演習３ 例題9を参考にして次のフォームより、中間試験TABで書き込んだテキストボックス内データは、ワークシート２へ書き込み、期末試験TABで書き込んだテキストボックス内データはワークシート3へ書き込まれるプログラムを作成せよ。

Private Sub ComboBox1_Change()
Dim i As Integer
For i = 1 To 40
ComboBox1.AddItem i
Next
Dim y As Integer
For y = 1 To 40
k = Worksheets(“sheet3”).Cells(y, 2)
ComboBox2.AddItem k
Next
End Sub

Private Sub ComboBox2_Change()
Dim i As Integer
For i = 1 To 40
k = Worksheets(“sheet3”).Cells(i, 2)
ComboBox2.AddItem k
Next
End Sub

Private Sub CommandButton1_Click()
If TabStrip1.Value = 0 Then
Worksheets(“sheet2”).Cells(1, 3).Value = TextBox1.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 3).Value = ComboBox1.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 4).Value = ComboBox2.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 6).Value = TextBox1.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 7).Value = TextBox2.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 8).Value = TextBox3.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 9).Value = TextBox4.Value
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 10).Value = TextBox5.Value
ComboBox1.Value = Clear
ComboBox2 = Clear
OptionButton1.Value = Clear
OptionButton2.Value = Clear

TextBox1 = Clear
TextBox2 = Clear
TextBox3 = Clear
TextBox4 = Clear
TextBox5 = Clear
Else
TextBox1.Value = Clear
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 3).Value = ComboBox1.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 4).Value = ComboBox2.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 6).Value = TextBox1.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 7).Value = TextBox2.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 8).Value = TextBox3.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 9).Value = TextBox4.Value
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 10).Value = TextBox5.Value
ComboBox1.Value = Clear
ComboBox2 = Clear
OptionButton1.Value = Clear
OptionButton2.Value = Clear

TextBox1 = Clear
TextBox2 = Clear
TextBox3 = Clear
TextBox4 = Clear
TextBox5 = Clear
End If

End Sub

Private Sub CommandButton2_Click()
Hide
End Sub

Private Sub OptionButton1_Click()

If TabStrip1.Value = 0 Then
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 5).Value = “男”
Else
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 5).Value = “男”
End If
End Sub

Private Sub OptionButton2_Click()
If TabStrip1.Value = 0 Then
Worksheets(“sheet2”).Cells(ComboBox1.Value + 1, 5).Value = “女”
Else
Worksheets(“sheet3”).Cells(ComboBox1.Value + 1, 5).Value = “女”
End If
End Sub

Private Sub TabStrip1_Change()

With TabStrip1
.Tabs(0).Caption = “中間試験”
.Tabs(1).Caption = “期末試験”
End With

End Sub