リストボックスで値を取得し、更新。若しくはそれ以外のやり方

Question

以前質問しました、年月日と時間の上書きについてと内容が似ていますが
コードの形式を変えたので再度質問致します。
前回とは異なり、今回はリストボックスを使うことにしました。

リストボックスで例えば10/3を選んだら、４列目の11:00と19:00を
修正出来るようにしたいのですが、10/1の時刻のところに上書きされてしまいました。

特にVBAに明るくない上司から急かれながら色んなものを参考にしながら進めていますが、なかなか上手くいきません・・。

リストボックス用の変数の宣言が必要なのでしょうか？
行の位置特定に何が必要でしょうか？

若しくはリストボックスにこだわらずとも、他のやり方があれば教えていただけると幸いです・・。

ご教授お願い致します。

発生している問題・エラーメッセージ

エラーメッセージ

該当のソースコード

Private Sub UserForm_Initialize()　　　　　　　　　　　　　　’リストボックス、「勤怠修正」フォーム
Dim lastrow As Long
Sheets("10").Select
With ListBox1
.ColumnHeads = True
.ColumnCount = 3
.ColumnWidths = "70;50;50"
.RowSource = "10!A2:C32"
End With

End Sub

Private Sub cbform5go_Click()     　　　　　　　　　　　　　　  '編集フォーム、修正ボタン
  Dim tgtRow As Long
  Dim fnd As Range
  tgtRow = form4.ListBox1.ListIndex + 3
  With Sheet2
    .Cells(tgtRow, 2).Value = Me.tx2.Value　　　　　　　　　　　　　　'出勤時刻
    .Cells(tgtRow, 3).Value = Me.tx3.Value　　　　　　　　　　　　　'退勤時刻
  End With
  
End Sub

Private Sub UserForm_Initialize()
  Dim tgtRow As Long
  Dim fnd As Range
  tgtRow = form4.ListBox1.ListIndex + 3
  With Sheet2
    Me.tx2.Value = Format(.Cells(tgtRow, 2).Value, "hh:mm")
    Me.tx3.Value = Format(.Cells(tgtRow, 3).Value, "hh:mm")
  End With
End Sub

試したこと

tgtRow = form4.ListBox1.ListIndex + 2

※form4 = リストボックスがある、勤怠修正フォームの名前

⇒「出勤時刻」「退勤時刻」が取得され、編集のテキストボックスに表示される。
+3，+4とすると取得する行が一つずつ下がるだけなので
勤怠修正フォームで選択した行に上書きされない。

Dim tgtRow As Long
tgtRow = Listbox1.Value

⇒変数を定義してくださいのエラー文

補足情報（FW/ツールのバージョンなど）

Dim tgtRow As Long
Dim fnd As Range
tgtRow = form4.ListBox1.ListIndex + 3
With Sheet2
Me.tx2.Value = Format(.Cells(tgtRow, 2).Value, "hh:mm")
Me.tx3.Value = Format(.Cells(tgtRow, 3).Value, "hh:mm")
End With

を、編集ボタンのところに書けば良いのか、 UserForm_Initialize()に書けば
良いのかわからないのでどちらにも置いてます・・・。

Comments

[gentaro]

https://qiita.com/doikoji/items/7cf9e659095816ab1131

Answer 1

前の質問だと、シートは複数あって、シートは非表示など、の仕様があったが、
とりあえずシートは一つだけということで回答します。
それが解決してから、複数シート対応はまた新規に質問するといいでしょう。

現状の仕様をみて思ったのは、
リストボックスを表示するフォームと、出勤、退勤時刻を入力するフォームの２つに分ける必要があるのかということです。
一つのフォームにリストボックスとテキストボックスを2つ配置すれば、リストボックスで選択して、すぐに編集できます。
コードもシンプルになりますし、ユーザーにとっても使いやすいと思います。

フォームの構成

ユーザーフォーム名 UserForm1
リストボックス名　Listbox1
出勤テキストボックス　TextBox1
退勤テキストボックス　TextBox2
修正コマンドボタン　cmdEdit

ユーザーフォームモジュール

Option Explicit

Private Sub cmdEdit_Click()                                 '修正ボタン
    Dim tgtRow As Long

    tgtRow = Me.ListBox1.ListIndex + 2
    With ActiveSheet
        .Cells(tgtRow, 2).Value = Me.TextBox1.Value          '出勤時刻
        .Cells(tgtRow, 3).Value = Me.TextBox2.Value          '退勤時刻
    End With
    Me.ListBox1.ListIndex = tgtRow - 2 'リストの選択が解除されるので選択しなおし
End Sub

Private Sub ListBox1_AfterUpdate()
    With ListBox1
        Me.TextBox1.Value = Format(.List(.ListIndex, 1), "hh:nn")
        Me.TextBox2.Value = Format(.List(.ListIndex, 2), "hh:nn")
    End With
End Sub

Private Sub UserForm_Initialize()                            
    Dim lastRow As Long
    With Sheets("10")
        .Select
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With
    With ListBox1
        .ColumnHeads = True
        .ColumnCount = 3
        .ColumnWidths = "70;50;50"
        .RowSource = "A2:C" & lastRow
        .ListIndex = .ListCount - 1 'リストの最終行を選択
    End With
    Call ListBox1_AfterUpdate
End Sub

以上で、リストボックスで選択した行の出勤時刻と退勤時刻を修正できます。

Comments

[milk1218]

ありがとうございます！
頂いたコードを参考に別けずに同じフォームで実行したら解決しました！

本当に助かりました・・！！何度もご教授いただき誠にありがとうございます！

複数シートは前回の質問で新たな案を頂いたので試してみようと思います・・！

[hatena19]

複数シートの場合は、シートは月毎に分かれているのでしょうか。

前回の質問の案は、配列にしてそれをリストに設定するという案だと思いますか、
それだと、現状のやり方とはかなり異なりますので、かなりの修正が必要になります。
というかほとんど一から書き直すことになりそうです。

現状のコードは RowSource を使ってシートのセル範囲とリンクさせてます。これだと、シートのセル範囲を編集したら、リストボックスにも自動で反映します。配列にしてしまうと、上記の処理を自前で実装する必要があります。

また、月毎だとすると、数か月分のデータをリストボックスに表示することになりますが、あまりリスト数が多くなるとユーザーが目的の行を選択しづらくなると思います。

自分がするなら、この回答のフォームのリストボックスの上にコンボボックス（or リストボックス）を配置して、そこでシート名(月？)を選択して、そのシートのデータをリストボックスに表示させるというUIにします。
そうすれば、コードの修正は少しですみましす、ユーザーにとっても使いやすいUIになるかと思います。

あくまで、私の個人的な意見なので、参考程度に。