Excel VBA リストボックス

質問

Excelで管理している台帳にあるデータをユーザーフォームで呼び出して更新作業をしたいと考えています。完全に我流でやってきており、まわりくどい記述等あるかもしれませんが、ご了承ください。
また開示しにくい情報が多々あるため、記述のごく一部を抜粋しています。

添付の記述は、Excelのマスタシートにある会社名のマスタをリストボックスに取り込み、
一覧にあるデータを選択状態にしようとしています。

D_会社名にはtestという会社名が入力されており、y_会社名にもtestがセットされるはずなのに
何故か””になってしまいます。

これは何が原因なのでしょうか。

回答No.4

TAKA_R

1です。
Findをする行の前に.activateを入れてみては？
（つまりマスタシートをアクティブにする）
この間、これで動かなかったfindが動き出したんです。
今の状態ではfindはD_会社名に代入したrangeのあるシートからアクセスしていることになっていて。
数値の読み込みには、アクティブ化しなくても読み込めるようですが。
書き込みとFindはアクティブ化しないと無理なようですね。

お礼

再回答ありがとうございます。

わざわざ何行目を選択状態という指示をしなくても、値そのものの指定ができることを教わったので、
今回はFindは使つ必要がなくりました。

ただ、個人的にはどこにあるかを探すのに多用していますので、今後使うときには参考にさせていただきます。

回答No.3

end-u

>y_会社名.Value = ""になっているのに
>ユーザーフォームを見ると、きっちり選択状態にはなっており、
これは、
>MsgBox y_会社名
この箇所で何も表示されないという事ですよね。

MsgBox y_会社名.ListIndex & vbLf & y_会社名.Value
としたらListIndexは何と表示されるかちょっと興味ありますが..
それはともかく実際には
>また開示しにくい情報が多々あるため、記述のごく一部を抜粋しています。
との事なので、他の要素が影響している事も考えられます。
最低限のテストコードで試してみてはいかがでしょうか。

テスト用にUserFormを追加してください。
ListBoxを一個だけ配置します。（オブジェクト名は変更しません）
そのFormモジュールに

Option Explicit

Private Sub UserForm_Initialize()
　　Call Try
End Sub

Private Sub Try()
　　Dim ws As Worksheet
　　Dim mx As Long

　　Set ws = Worksheets("マスタ")
　　mx = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
　　If mx >= 4 Then
　　　　With Me.ListBox1
　　　　　　.List = ws.Range("B4").Resize(mx - 3).Value
　　　　　　'On Error Resume Next
　　　　　　.Value = ActiveCell.Value
　　　　　　MsgBox .ListIndex & vbLf & .Value
　　　　End With
　　End If
End Sub

抜粋にて開示されたコードをシンプルに書き換えると上記のようになります。
.Value = ActiveCell.Value
としていますから、貴方のコードで言えば、
test と書いてある Range("C" & rw) のセルを選択して、上記のUserFormを開いてみてください。

原因不明の場合、
取り敢えずUserFormを作成し直してみる、という選択肢もありかもしれません。
その時は

・変数は明示的に宣言する。
・プロシージャ名、変数名、Control名に漢字などの２バイト文字を使わない。
・既定プロパティであっても、省略しない。
　ex) MsgBox y_会社名.Value　y_会社名.AddItem .Cells(i, 2).Value

..等々の事をおすすめします。

お礼

再度のお返事ありがとうございます。
VBAに関して会社で相談できる人間がいないのでとても頼もしいです。

これから試してみますが、まずはお礼まで。

補足

質問者です。

テスト用のユーザーフォームで試してみました。
行数と会社名がちゃんとMsgboxにでました。

参考にして下記のように書き直したら、やはりフォームは選択状態になっているにも関わらず、更新すると空白で上書きされます。

Sub (1)会社名読み込み()
Set ws = Worksheets("マスタ")
MATUGYO = ws.Cells(65536, 2).End(xlUp).Row

With Me.y_会社名
.List = ws.Range("B4").Resize(MATUGYO - 3).Value
'On Error Resume Next
.Value = Range("C" & rw).Value
End With

MsgBox y_会社名

''*****呼び出した行の項目を選択状態にしておく
' '呼び出した行の項目を取得
' D_会社名 = Range("C" & rw).Value
' 'それがマスタの何行目か
' Set syutoku = .Range("B4:B65536").Find(what:=D_会社名)
' If y_会社名.ListCount > 0 Then
' 'マスタの開始行分を差し引いた行を選択状態にする
' y_会社名.ListIndex = syutoku.Row - 4
'
' kari = y_会社名.Value
' End If

End Sub

実はこれベースは車両台帳でして、選択入力、直接入力、自動入力の30近くの項目をひとつのフォームにまとめており、作り直しはどうしても回避したいところです。

あとは下記にて改善することを願い、実践してみます。

・変数は明示的に宣言する。
・プロシージャ名、変数名、Control名に漢字などの２バイト文字を使わない。
・既定プロパティであっても、省略しない。

回答No.2

end-u

On Error Resume Next
この行をコメントアウトして確認してください。
エラーを無視してるわけですから
これを活かしたままだとデバッグできません。

確認の内容は、
MATUGYO、rw、D_会社名、syutoku、それぞれ順を追って
各変数に何が格納されているか確認する必要があります。
ステップ実行時にマウスカーソルをあてて確認しても良いし、
[ローカルウィンドウ]を表示させておいて確認しても良いし。
または、実行後にListBoxのどの行が選択されているかによって
推測できそうなものですが。

気になるのはrwですね。モジュールレベルの変数でしょうか。
D_会社名がEmptyで、ListBoxのデータに空白値があるなら
y_会社名.Value = "" もあり得るかもしれません。

ただ、目的からすると、単一項目選択のListBoxの場合は
y_会社名.Value = Range("C" & rw).Value
これで選択してくれます。
データがListにない場合も対応できるように
この直前にOn Error Resume Nextを入れれば良いです。

お礼

回答ありがとうございます。

各変数へは把握しているとおりに格納されていました。
D_会社名もしっかり格納されています。

rwはPublicで宣言してあります。

直接y_会社名.Value = Range("C" & rw).ValueでもOKとのことで、記述をそちらに切り替えましたが、やはり、y_会社名.Value = ""になってしまいます。

画像を添付したほうが見やすいのですが、やり方がわからないのでべた打ちしました。

Sub (1)会社名読み込み()
'On Error Resume Next
With Worksheets("マスタ")
MATUGYO = .Cells(65536, 2).End(xlUp).Row
For i = 4 To MATUGYO
y_会社名.AddItem .Cells(i, 2)
Next i

y_会社名.Value = Range("C" & rw).Value

MsgBox y_会社名

''*****呼び出した行の項目を選択状態にしておく
' '呼び出した行の項目を取得
' D_会社名 = Range("C" & rw).Value
' 'それがマスタの何行目か
' Set syutoku = .Range("B4:B65536").Find(what:=D_会社名)
' If y_会社名.ListCount > 0 Then
' 'マスタの開始行分を差し引いた行を選択状態にする
' y_会社名.ListIndex = syutoku.Row - 4
'
' kari = y_会社名.Value
' End If
End With
End Sub

y_会社名.Value = ""になっているのに
ユーザーフォームを見ると、きっちり選択状態にはなっており、
そのまま更新ボタンを押して、再度Excelに戻すと空白で上書きされてしまいます。
選択状態になっているリストをクリックしてから更新ボタンを押せば、ちゃんとtestがExcelに上書きされます。

回答No.1

TAKA_R

私も我流で申し訳ありませんが、コードに誤りは無さそう（？）です。
それ以外（？）の理由としては

原因１；データ型間違い（可能性低い）
ここでは載っていないので、確認できません。
Dim 最終行 As Long, i As Long
Dim d_会社名 As String, 仮 As String
Dim 取得 As Range

原因２；書くところの誤り
d_会社名はlistboxを指しているので、フォームモジュールに書く。
データ型を補うときちんと動いていましたよ？
(userform_initialize,userform_clickの両方とも)

原因３；実はセルにtestと書くのを忘れている。

お礼

回答ありがとうございます。

原因(2)ですが、userform_initializeの中に書いています。（callで画像のsubを呼び出している）

原因(3)もないですね＾＾；

後は原因(1)・・・変数宣言しないで使っているので、きっちり指定して動くか試みます。

補足

質問者です。
変数宣言して実行しようとすると、「このメンバ識別子は既にオブジェクトモジュールの中に存在しています」と言われてしまいました。