Private Sub UserForm_Initialize() Dim lRow As Long With Worksheets("Sheet1") lRow = .Range("B" & Rows.Count).End(xlUp).Row End With With ComboBox1 .RowSource = "Sheet1!B3:B" & lRow End With With ComboBox2 .AddItem "男" .AddItem "女" End With End Sub |
Private Sub ComboBox1_afterupdate() Dim lRow As Long Dim i As Integer Dim myFlag As Boolean Dim myRow As Long Dim myNo As Variant If ComboBox1.Value = "" Then TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox4.Value = "" TextBox5.Value = "" ComboBox2.Value = "" Exit Sub End If 'シートに連番が入力済みか否かを調べる With Worksheets("Sheet1") lRow = .Range("B" & Rows.Count).End(xlUp).Row myNo = .Range("B3:B" & lRow).Value myFlag = False For i = LBound(myNo) To UBound(myNo) If CInt(myNo(i, 1)) = CInt(ComboBox1.Value) Then myFlag = True myRow = i + 2 Exit For End If Next i '既に入力されていたら、入力済みのデータを表示する If myFlag = True Then TextBox1.Value = .Range("C" & myRow) TextBox2.Value = .Range("D" & myRow) TextBox3.Value = .Range("F" & myRow) TextBox4.Value = .Range("G" & myRow) TextBox5.Value = .Range("H" & myRow) ComboBox2.Value = .Range("E" & myRow) Else TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" TextBox4.Value = "" TextBox5.Value = "" ComboBox2.Value = "" End If End With End Sub |
Private Sub CommandButton1_Click() Dim lRow As Long Dim i As Integer Dim myFlag As Boolean Dim myRow As Long Dim myNo As Variant If ComboBox1.Value = "" Or IsNumeric(ComboBox1.Value) = False Then MsgBox "連番には数値を入力してください" Exit Sub End If With Worksheets("Sheet1") lRow = .Range("B" & Rows.Count).End(xlUp).Row myNo = .Range("B3:B" & lRow).Value myFlag = False For i = LBound(myNo) To UBound(myNo) If CInt(myNo(i, 1)) = CInt(ComboBox1.Value) Then myFlag = True myRow = i + 2 Exit For End If Next i If myFlag = False Then myRow = lRow + 1 .Range("B" & myRow).Value = ComboBox1.Value .Range("C" & myRow).Value = TextBox1.Value .Range("D" & myRow).Value = TextBox2.Value .Range("E" & myRow).Value = ComboBox2.Value .Range("F" & myRow).Value = TextBox3.Value .Range("G" & myRow).Value = TextBox4.Value .Range("H" & myRow).Value = TextBox5.Value End With End Sub ’------------------------ Private Sub CommandButton2_Click() Unload UserForm1 End Sub |
Sub start1() UserForm1.Show vbModeless End Sub |
よねさんのExcelとWordの使い方|エクセル2013基本講座:目次|データ/数式/関数の入力|ユーザーフォームを使って入力する