リストボックスでデータを呼び出し→修正を反映させる方法を教えてください

質問

マクロ初心者です。（エクセル２００３使用＿ユーザーフォーム）
リストボックスで表示した行のデータをユーザーフォームに呼び出し、修正を加える方法を教えてください。
（Sheet1のデータ）
客先 管理番号 品名 注文数量 出荷数量 出荷日 完納状況
Ｇ社 　1324 　　パソコン 　　　30 　　　20
Ｋ社 　1554 　　プリンタ 　　　250 　　　70 　　　8月9日
Ｊ社 　7634 　　テレビ 　　　　　40 　　　25
Ｋ社 　4653 　　パソコン 　　　75 　　　75 　　　6月4日 　完納
Ｇ社 　6675 　　パソコン 　　　170 　　　60
Ｇ社 　7789 　　プリンタ 　　　200　　　 120

表の行数は、日々変動し、出荷日や完納状況欄は、分かり次第記入や変更をするため、空白の箇所もあります。

（コマンドボタン１まで動く（日付が数字になってしまうが）コード）
Private Sub UserForm_Initialize()　　
With ListBox1
.ColumnWidths = "50;50;50"
.ColumnCount = 2
.RowSource = "Sheet1!A2:G" & Worksheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
End With
End Sub　　　　　　　　　　　　→リストに表示されるが、本当は＜管理番号＞と＜品名＞を表示させたいです。（今は、客先と管理番号です）

Private Sub CommandButton1_Click()
TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 5)　→日付が数字ででてしまいます。
TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 6)
End Sub

Private Sub CommandButton2_Click()
TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 5)　→動きません。
TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 6)　→動きません。
End Sub

空白は、空白のまま表示させたり、修正したいと思っています。
（例　管理番号1554の日付を８月１４日にのみ修正したい時、そこは修正し、完納欄は空白のまま表示され、空白の状態でコマンド２を押すことができてほしいということです）

すみませんが、どなたかご回答よろしくお願いいたします。

回答No.3

redfox63

以下のような感じでいいのでは ・・・
Option Explicit

Private Sub CommandButton1_Click()
　　' リストボックスで選択された行がある
　　If ListBox1.ListIndex <> -1 Then
　　　　' 元のデータの日付の入力をチェック
　　　　If Not IsEmpty(ListBox1.List(ListBox1.ListIndex, 5)) Then
　　　　　　' 日付型に変換して Formatで表示形式を整形
　　　　　　TextBox1.Value = Format(CDate(ListBox1.List(ListBox1.ListIndex, 5)), "m月d日")
　　　　Else
　　　　　　TextBox1.Value = ""
　　　　End If
　　　　TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 6)
　　End If
End Sub

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

Private Sub UserForm_Initialize()
　　Dim r As Range
　　' セルA1を基準にしたデータ範囲を取得
　　Set r = Sheets("Sheet1").Range("A1").CurrentRegion.Offset(1)
　　' 先頭の題目分を除外
　　Set r = r.Resize(r.Rows.Count - 1)
　　With ListBox1
　　　　' 列の表示幅の設定
　　　　.ColumnWidths = "0;50;50;"
　　　　' 何列あるのかを設定
　　　　.ColumnCount = 3
　　　　' 元データの設定
　　　　.RowSource = r.Address(0, 0)
　　End With
End Sub

といった具合で ・・・

お礼

すごいです。完璧です！ありがとうございます。
自分でも理解できるように、これから勉強します。
本当にありがとうございます。

回答No.2

nag0720

.RowSource = "Sheet1!A2:G" & Worksheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row

これだと、示された例でいうと、始めの４件しかリストに表示されません。（最後の２件は表示されません）
これが望み通りのリスト内容ならいいんですが・・・

もし、リストに全部表示したいなら、
.RowSource = "Sheet1!A2:G" & Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

回答No.1

watabe007

>本当は＜管理番号＞と＜品名＞を表示させたいです。（今は、客先と管理番号です）
Private Sub UserForm_Initialize()　　
With ListBox1
.ColumnWidths = "50;50;50" '→　"0;50;50"　一列目の幅を0にする
.ColumnCount = 2　　'→3
.RowSource = "Sheet1!A2:G" & Worksheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
End With

>日付が数字ででてしまいます。
Format関数で日付に変更してください

>Private Sub CommandButton2_Click()
>TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 5)　→動きません。
セルの値を修正するのなら
Range(該当セル).Value= TextBox1.Value となるのでは？