エクセルマクロ＿上書き（リストボックスでデータを抽出し、その行へ上書き）

質問

エクセルマクロ初心者です。（２００３使用＿ユーザーフォーム，リストボックス，テキストボックス，チャックボックス）
Sheet1のデータをユーザーフォームへ反映させ、修正したものをその行へ上書きするマクロを作成したいと思っていますが、うまくいかないのでどなたかお教え願います。

（Sheet1―A8より表がはじまります）
管理番号 品名 数量 希望納期 変更数量 完納 コメント
アカ12 みかん 　　　　40 　　8月3日
アオ35 りんご 　　　　30 　　8月5日
クロ54 なし 　　　　50 　　7月7日
アカ98 もも 　　　　20 　　9月1日
アオ43 すいか 　　　　35 　　8月3日

●完納の行がチャックボックスで、チェックがついたら、”完納”と記載、チェックがついていなかったら空欄のまま。もし表に”完納”と記載されていたら、コマンドボタン１でデータを抽出した時に、チェックがつくようにしたいのです。が、まったくうまくいかないのです。

●また、コマンド１での抽出データに値を入力したものを上書きすることは一部できるのですが、空欄にしたときに、空欄の状態（無記入）に上書きすることができません。

Private Sub UserForm_Initialize()

Dim r As Range
Set r = Sheets("Sheet1").Range("A8").CurrentRegion.Offset(1)
Set r = r.Resize(r.Rows.Count - 1)

With ListBox1
.ColumnWidths = "50;50;0;0;0;0;0"
.ColumnCount = 7
.RowSource = r.Address(A8, G)
End With

End Sub

Private Sub CommandButton1_Click()

If ListBox1.ListIndex <> -1 Then
TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 0)
TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 1)
TextBox3.Value = ListBox1.List(ListBox1.ListIndex, 2)
TextBox5.Value = ListBox1.List(ListBox1.ListIndex, 4)
TextBox6.Value = ListBox1.List(ListBox1.ListIndex, 6)
End If

If Not IsEmpty(ListBox1.List(ListBox1.ListIndex, 3)) Then
TextBox4.Value = Format(CDate(ListBox1.List(ListBox1.ListIndex, 3)), "m/d")
Else
TextBox4.Value = ""
End If

End Sub

Private Sub CommandButton2_Click()

If ListBox1.ListIndex <> -1 Then
Dim r As Range
' 元データの範囲を取得
Set r = Sheets("Sheet1").Range("A8").CurrentRegion.Offset(1)
Set r = r.Resize(r.Rows.Count - 1)
' 日付型かどうかをチェック
If IsDate(TextBox4.Value) Then
' 選択されたセルの更新
r(ListBox1.ListIndex + 1, 4) = TextBox4.Value
End If
r(ListBox1.ListIndex + 1, 7) = TextBox6.Value
End If
End Sub

Private Sub CheckBox1_Click()

If ListBox1.ListIndex <> -1 Then
Dim l As Range
Set l = Sheets("Sheet1").Range("A8").CurrentRegion.Offset(1)
Set l = l.Resize(l.Rows.Count - 1)

If CheckBox1.Value = True Then
l(ListBox1.ListIndex + 1, 5).Value = "完納"
If CheckBox1.Value = False Then
l(ListBox1.ListIndex + 1, 5).Value = "<>"
End If

End If

End If
End Sub

すみませんが、ご親切に教えていただけましたら幸いです。
よろしくお願いいたします。

回答No.3

myRange · ベストアンサー

＞8月3日をdeleteキーで消した状態にして
＞（空白のテキストボックスの状態にして）
＞コマンドボタン２を押しても、
＞シートには8月3日と書かれたままになってしまうのです。

そうです、このように具体的に書いてもらうと分かりやすいですよね。
で、回答。

＞' 日付型かどうかをチェック
＞If IsDate(TextBox4.Value) Then
＞' 選択されたセルの更新
＞r(ListBox1.ListIndex + 1, 4) = TextBox4.Value
＞End If

上記では「日付以外」の処理がしてないので質問のようになります。

TextBox4の入力値は、
日付、空白、日付以外の文字列、の３種類ありますので
次のようにすればいいでしょう。

●日付と空白は、セルへ転記する
●それ以外はエラーなので転記しないで再入力を促す
'---------------------------------------------------

If IsDate(TextBox4.Value) Or TextBox4.Value = "" Then
　　r(ListBox1.ListIndex + 1, 4) = TextBox4.Value
Else
　　MsgBox "希望納期が間違い、再入力せよ！"
　　Textbox4.Value = ""
　　TextBox4.SetFocus
End If
'------------------------------------------------

■それから気になったことひとつあり。

管理番号や品名、数量、変更数量が変更されたときは、
その変更をセルに反映させなくていいのですか？

以上です。

お礼

ご丁寧な回答、本当にありがとうございました。
無事できました。
おっしゃられている通り、日付以外の箇所も変更をセルに反映させる必要があります。
ご親切に回答いただき助かりました。ありがとうございます。

回答No.2

imogasi

質問の説明が不十分。
>完納の行がチャックボックスで、チェックがついたら、”完納”と記載
チェックボックスはフォームにあるものだよね。
それとシートの完納列の対応行と関連図けるのか。
チェックボックスをフォームに置かずにシートの各行においては。
チェックボックスはいいカッコだけで、完納と入れて不便か？
自由にVBAが使えるならやるのも勝手だが。
＞Sheet1のデータをユーザーフォームへ反映させ
シートのどの項目をユーザーフォームのどのコントロールに反映させるのか。
長いコードを書いて回答者・読者に読ませ（皆忙しい中読んでいる）、読解させる前に、質問者の構想そのものを文章で説明すべきだし、そこに無理があるのではないか（わざわざむつ時下市区している。ちなみに、入力規則などでよいのでは）。
＞コマンドボタン１でデータを抽出した時
抽出とは条件で抜き出すことだ。どれを対象に、どういう条件で、抜き出しているのかな。

回答No.1

myRange

回答がついてないようなので。。。。

＞●完納の行がチャックボックスで、チェックがついたら、”完納”と記載、チェックがついていなかったら空欄のまま。

これはCheckBox_Clickイベントの下記のコードにミスあり。

＞If CheckBox1.Value = True Then
＞l(ListBox1.ListIndex + 1, 5).Value = "完納"
＞If CheckBox1.Value = False Then
＞l(ListBox1.ListIndex + 1, 5).Value = "<>"
＞End If
＞End If

If構文にミスがあるし、完納のセル位置は、列５ではなく、列６ですよね。
以下のように修正する。

If CheckBox1.Value = True Then
　　l(ListBox1.ListIndex + 1, 6).Value = "完納"
Else
　　l(ListBox1.ListIndex + 1, 6).Value = ""
End If

'-------------------------------------------------------

＞●表に”完納”と記載されていたらコマンドボタン１でデータを抽出した時に、チェックがつくようにしたい

CommandButton１のClickイベントのところに下記を追加
-----------------------------------------------------
If ListBox1.List(ListBox1.ListIndex, 5) = "完納" Then
　　CheckBox1.Value = True
Else
　　CheckBox1.Value = False
End If
-----------------------------------------------------

＞●また、コマンド１での抽出データに値を入力したものを上書きすることは一部できるのですが

コードでは一部しか上書きしてないからです。

＞●空欄にしたときに、空欄の状態（無記入）に上書きすることができません

これ意味不明です。具体的な例を挙げましょう。
以上です。

補足

myRange様，ご丁寧な解説をいただきまして本当にありがとうございます。チェックボタンの質問ですが、うまく作動することができました。

不明な文[空欄にしたときに、空欄の状態（無記入）に上書きすることができません]に関してですが、
アオ43をリストボックスで選択し、コマンドボタン１を押すと、その行にある＜数量35＞や＜希望納期の8月3日＞がテキストボックスに抽出されるのですが、たとえば希望納期を8月5日に変更することはコマンドボタン２で可能なのですが、8月3日をdeleteキーで消した状態にして（空白のテキストボックスの状態にして）コマンドボタン２を押しても、シートには8月3日と書かれたままになってしまうのです。

説明が下手ですみません。
お手数ですが、ご回答いただけましたら幸いです。
よろしくお願いいたします。
（もうひとつの不明な文は、これと同じことを言いたかったのお忘れ願います。）