ユーザーフォームでの登録＆編集
ユーザーフォームのリストボックス(RowSource,P6:A26)で選択し14個のテキストボックスで編集しコマンドボタンでSheet(P6:AC26)にコピペしていますが、下記のコードだと無制限に登録されてしまいます。Sheetの範囲内で登録＆編集のコードの書き方がありましたらご教示賜りたく存じます。(番号は自動入力でなくてもいいです。)
Windows7・SP1 Office2010
Private Sub CommandButton2_Click()
Dim varRag As Variant
Dim myArray As Integer
Dim i As Long
varRag = Array(txtID, txtTextBox2, txtTextBox3, txtTextBox4, txtTextBox5, txtTextBox6, txtTextBox7, txtTextBox8, txtTextBox9, txtTextBox10, txtTextBox11, txtTextBox12, txtTextBox13, txtTextBox14)
If TextBox3.Text = "" Then
MsgBox "登録すべき内容がありません！", vbExclamation, "確認"
Exit Sub
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If ListBox1.ListIndex = -1 Then 'リストが選択していなかったら、
Cells(Rows.Count, 16).End(xlUp).Offset(1).Select
For myArray = 0 To 9
With Selection
txtID = .Row - 5
.Offset(, myArray) = varRag(myArray)
End With
Next myArray
Else
i = ListBox1.ListIndex + 6
Range("P" & i).Value = i - 5
Range("Q" & i).Value = TextBox2.Text
Range("R" & i).Value = TextBox3.Text
Range("S" & i).Value = TextBox4.Text
Range("T" & i).Value = TextBox5.Text
Range("U" & i).Value = TextBox6.Text
Range("V" & i).Value = TextBox7.Text
Range("W" & i).Value = TextBox8.Text
Range("X" & i).Value = TextBox9.Text
Range("Y" & i).Value = TextBox10.Text
Range("Z" & i).Value = TextBox11.Text
Range("AA" & i).Value = TextBox12.Text
Range("AB" & i).Value = TextBox13.Text
Range("AC" & i).Value = TextBox14.Text
End If
'データをクリア
ListBox1.ListIndex = -1
ID.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Private Sub ListBox1_Change()
Dim targetRow As Integer
With ListBox1
targetRow = .ListIndex
ID.Text = .List(targetRow, 0)
TextBox2.Text = .List(targetRow, 1)
TextBox3.Text = .List(targetRow, 2)
TextBox4.Text = .List(targetRow, 3)
TextBox5.Text = .List(targetRow, 4)
TextBox6.Text = .List(targetRow, 5)
TextBox7.Text = .List(targetRow, 6)
TextBox8.Text = .List(targetRow, 7)
TextBox9.Text = .List(targetRow, 8)
TextBox10.Text = .List(targetRow, 9)
TextBox11.Text = .List(targetRow, 10)
TextBox12.Text = .List(targetRow, 11)
TextBox13.Text = .List(targetRow, 12)
TextBox14.Text = .List(targetRow, 13)
End With
End Sub
お礼
ご解答・アドバイスありがとうございます。