VLookup関数を使ってラベルに表記させるには？

質問

●質問の主旨

1.コンボボックスから選択した顧客名によって
ラベルに顧客種別を表記させるにはどのように
コードを書き換えればよいでしょうか？

2.またどの顧客が選ばれても、顧客種別に
　対応して表記させるにはコードが足りない
　ような気がします。もしそのような対応が
　できるコードがあれば、合わせてご教示
　ください

●質問の補足
添付の画像のユーザーフォームのコンボボックスから
顧客名を選んで、その顧客名によって別シートの顧客
一覧表から

・「新規顧客」
・「既存顧客」
・「自社」

のどれに当たるかVLookup関数を使ってラベルに
表記させたいと考えております。

以下のコードを記述しましたが、
「メソッドまたはメンバが見つかりません」と
エラーが返されてしまいます。

●ユーザーフォームの構成
「顧客名」→Label3
「顧客リスト」→ComboBox1
「顧客種別」→Label4

なお、ユーザーフォームはプリントスクリーンで
貼り付けており、本来はWorksheets(1)に表示されます。

●コード
'ユーザーフォームの初期化
Private Sub UserForm_Initialize()

With ComboBox1

.AddItem "NPO法人亀山社中"
.AddItem "土佐商会株式会社"
.AddItem "(株)薩摩示現流"
.AddItem "越後屋株式会社"
.AddItem "藩校コンサルティング(株)"
.AddItem "株式会社ラーニングバー"
.AddItem "自社"

　End With

End Sub

'顧客リストが選択された時の顧客種別の表記
Private Sub ComboBox1_Click()

Worksheets(2).Activate
Label4.Value = Application.WorksheetsFunction.VLookup("B4", Range("B3:C8"), 2, 1)

End Sub

以上よろしくお願い申し上げます。使用機種はWindowsVistaで、
Excel2007です。私はVBA初心者です。

回答No.1

end-u · ベストアンサー

まず、
LabelコントロールにはValueプロパティがありませんから
「メソッドまたはメンバが見つかりません」のコンパイルエラーになります。
VBEditorで[F2]キー、[オブジェクトブラウザ]を開いて
＜すべてのライブラリ＞ではなく
[　MSForms　]ライブラリの中から
[　Label　　]を検索してみてください。
Label のメンバにどんなものがあるか確認する事ができます。

また、ComboBox1にアイテムを追加する時、
コード内に直値を書き込んでしまうと変動時の修正が大変です。
シートに設定するなら、そこから読み込んだほうが良いです。
どうせ読み込むなら、「顧客種別」も同時に読み込めば、
VLookup関数を使う必要もなくなります。

Private Sub UserForm_Initialize()
　　Dim r As Range

　　With Worksheets(2)
　　　　'B列基準でデータ範囲を取得
　　　　Set r = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
　　　　'データ範囲以下に無関係な何かが入力してあるなら
　　　　'Set r = .Range("C3", .Range("B3").End(xlDown))
　　End With

　　With Me.ComboBox1
　　　　'Comboを2列設定し2列目は非表示
　　　　.ColumnCount = 2
　　　　.ColumnWidths = ";0"
　　　　'データ範囲 r をまとめてセット
　　　　.List = r.Value
　　End With

　　Set r = Nothing
End Sub

Private Sub ComboBox1_Click()
　　Worksheets(2).Activate
　　With Me.ComboBox1
　　　　'ComboBoxのListの選択行、2列目の値。(Indexは0から始まる)
　　　　Me.Label4.Caption = .List(.ListIndex, 1)
　　End With
End Sub

お礼

ご丁寧なご回答ありがとうございます。
ご回答について補足質問がございます。
そちらについてもご確認いただければ
幸いです。

補足

コードの記述及び各コードのご説明ありがとうございます。

上記の通りコードを書き換えると、
顧客名と顧客種別を同時に選択することができました。

ただ
Set r = Nothing

というコードの存在が良く分かりません。
ネットなどで検索をかけて見ていると、

「フォームの解放処理が行われずに、
メモリ上に残ってしまう現象なのだと
想像できます」

と初心者には分かったようわからないような説明が
書いてあったりします。

end-u様のコードにあてはめて考えてみて、
もしこのコードがないとどんな不都合が生じるのでしょうか？

たびたびの質問で恐縮ですが、
ご教示くだされば幸いです

ちなみに私が検索したHPのアドレスは
以下の通りです。

http://www.gizcollabo.jp/vbtomo/log/archive/vbqanda_11269_1.html

回答No.2

end-u

フォーム..に限った話ではないです。
が、今回は
>もしこのコードがないとどんな不都合が生じるのでしょうか？
不都合は生じません。（と、思われます）

この（と、思われます）部分をつっこんで検証しようとすると
ちょっと大変かなぁ..という感じです。

Object型変数について。

本来、プロシージャ内の変数はそのプロシージャを抜けると
変数のメモリ参照も解放されるはずなので
Set xxx = Nothing
は必要ない、..というポリシーの方もいらっしゃるようです。

実経験として、Object型変数で参照したメモリ領域が解放されず、残ってしまうケースもあるようで、
確実に開放したいため、【Object型変数を初期化している】、
..というポリシーの方もいらっしゃいます。

ポリシーの問題で片付けてはいけないのでしょうけど、
「守破離」の「守」段階であるワタシ的には先人の教えの中で
自分が一番腑に落ちるものを踏襲しているだけですので詳しく説明できるわけではありません。

ただ、実際に
Set xxx = Nothing
を書いておいたほうが良いケースもあるようで。で、あれば
「このケースは必要ないから書かなくていいや」
「ここ書かないとまずいかな」
なんて頭悩ませながら書くより最初から
Set xxx = Object
としたら同時に
Set xxx = Nothing
書いておく、というコーディングスタイルを取っています。私は。

あとは、いろんな情報を仕入れて判断してみてください。
http://www.excel.studio-kazu.jp/kw/20090303232447.html
http://starfleet.txt-nifty.com/blog/2008/02/excel_vba_5749.html

お礼

補足質問にもお答えいただきありがとうございます。
end-u様の紹介HPも参考にさせていただきました。

ただ「Set xxx = Nothing」というコードについての
扱いはいろいろあるようですね。

今の私は初心者なので、
「とりあえず書いてるからかいておこう」
というスタイルにしておきます。

二度にわたるご回答ありがとうございました。