| |
UserFormの構造が解らないので？ですが
UserFormにTextBox1、TextBox2、CommandButton1が有るとして
CommandButton1で転記される物とします
UserFormのコードモジュールに以下を記述して下さい
Option Explicit
'Listの先頭セル位置(「車種」の行見出し位置）
Private rngList As Range
'Listの列数
Private lngEnd As Long
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Or TextBox2 = "" Then
Exit Sub
Else
If DataExist Then
Exit Sub
End If
End If
'List列数を更新
lngEnd = lngEnd + 1
'車種、車型を書き込み
With rngList
.Offset(, lngEnd).Value = TextBox1.Text
.Offset(1, lngEnd).Value = TextBox2.Text
End With
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If .Text <> "" Then
If TextBox2.Text <> "" Then
If DataExist Then
Beep
MsgBox "同名の車種、車型が有ります"
Cancel = True
End If
End If
End If
End With
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox2
If .Text <> "" Then
If TextBox1.Text <> "" Then
If DataExist Then
Beep
MsgBox "同名の車種、車型が有ります"
Cancel = True
End If
End If
End If
End With
End Sub
Private Sub UserForm_Initialize()
'Listの先頭セル位置設定
Set rngList = ActiveSheet.Cells(1, "B")
With rngList
'Listの列数取得
lngEnd = .Offset(, 256 - .Column).End(xlToLeft).Column - .Column
If lngEnd <= 0 Then
lngEnd = 0
End If
End With
End Sub
Private Sub UserForm_Terminate()
Set rngList = Nothing
End Sub
Private Function DataExist() As Boolean
Dim vntFound As Variant
Dim rngScope As Range
'List列数が0で無いなら
If lngEnd > 0 Then
'車種の範囲を取得
Set rngScope = rngList.Offset(, 1).Resize(, lngEnd)
'同名の車種が有るか探索
vntFound = Application.Match(Trim(TextBox1.Text), rngScope, 0)
'無いなら
If IsError(vntFound) Then
Exit Function
Else
'同名の車型が無い場合
If rngList.Offset(1, vntFound).Value _
<> Trim(TextBox2.Text) Then
Exit Function
Else
DataExist = True
End If
End If
Set rngScope = Nothing
Else
Exit Function
End If
End Function
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
