セルに追加されるデータをListBoxに表示させたい

質問

ＶＢＡ初心者です
エクセルのシートに入力されたデータを、ListBoxに表示させるプログラムを作っているのですが
セルＢ５以下に入力されるＢ列のデータを、追加されたら、自動的にリストボックスにも追加されるというプログラムを作りたいと考えています

ご指南、宜しくお願い致します

回答No.1

NOBNNN · ベストアンサー

LISTBOX のプロパティをに値を設定することにより可能です。
以下のマクロを標準モジュールに貼り付けて実験してください
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Sub リストボックスTESTマクロ()
'
' Macro2 Macro
' マクロ記録日 : 2007/4/22 ユーザー名 : NOBU
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "入力データ"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ニュウリョク"
Range("B2").Select
ActiveCell.FormulaR1C1 = "12"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+3"
Range("B3").Select
Selection.Copy
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFill Destination:=Range("B3:B29"), Type:=xlFillDefault
Range("B3:B29").Select
Range("C28").Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=288, Top:=99.75, Width:=183.75, Height:= _
99).Select
Application.CommandBars("Control Toolbox").Visible = False
End Sub
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
◆解説
セルの値を指定するにはListbox のプロパティのListFillrange
　にセル範囲のアドレスを記述します。
　Listbox のプロパティのListFillrange に入力データが入っている
　セルのアドレス、上記の例では $B$2:$B$29 としています。
　もしB列の最後のアドレスが変わるのであれば
　Sheet1 のＶＢＡにセルが変更されたイベントの処理にて
　もし、Ｂ列に新しく追加されている場合はListFillrangeプロパティ　にそのアドレス範囲を設定　しなくてはなりません。　
　そこで以下のコードをsheet1のVBAに追加してください。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
Me.ListBox1.ListFillRange = "$B$2:$B$" & Worksheets(1).Columns("B:B").SpecialCells(xlCellTypeLastCell).Row
End If
End Sub　
__________________________________________________________
どうですか　これで完璧になったはずです。

ほかには　データの入力規則のリストでも同じことができます。

以下の参考サイトをご覧ください。
__________________________________________________________
参考 ＜勝手ながら参考リンクさせていただきます＞

・ListBoxによる入力支援マクロ
http://www5b.biglobe.ne.jp/~mebaru/ExcelVBA/ExcelVBA018.htm

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
以上

お礼

参考になるコードとリンク、有難うございました
色々と実験して、勉強させて頂きます

回答No.4

NOBNNN

#1です。
このマクロを実行すればわかりますがListBOX がシート
に現れます。
テストデータやLISTBOX オブジェクトを動かすための環境を
作成するためのマクロです。
説明を簡略する意味でマクロ化しました。

実験して動きを確認していただければわかると思います。
VBAの画面にて　ブレークポイント　デバッグモードなどで
確認できると思います。

LISTBOXのオブジェクトを使用する場合はユーザーフォームに
作成あるいはシートに作成いずれかの方法をとります。
それぞれのオブジェクトのプロパティを見るには　LISTBOXのオブジェクトを右クリックし、プロパティ　を選択してください。

＃２の方がおっしゃる通り他の方法もあります。
データの入力規則でも同様なことも実現できます。
用は何が目的でどのような動作を期待しているかによります。

中身をじっくり見ないとわかりにくいかもしれませんがこのマクロを
一度、動かしてから再度質問してください。

以上

お礼

お礼が大変遅くなってしまい、申し訳ありませんでした
参考になるご回答、有難うございました！

回答No.3

Wendy02

#2です。

>ListBoxのプロパティに「ListFillRange」の項目が見当たりません

そのListBox は、フォーム側ですね。
Excelには、二つの種類があります。今後は、なるべく、コントロール側のほうに移行してください。

今回は、マクロは必要ありません。
単に、コントロールの書式設定－コントロール

#2で示したように、挿入－名前－定義 で、名前と参照範囲　に入れて、
入力範囲に、
「ListData」
を入れてください。それでうまくいくはずです。

お礼

お礼が大変遅くなってしまい、申し訳ありませんでした
参考になるご回答、有難うございました！

回答No.2

Wendy02

こんにちは

その程度でしたら、本来はVBAは必要ないかもしれません。

単に、名前で登録すればよいと思います。

例：
挿入－名前－定義

名前(W):
ListData

参照範囲(R):
=INDIRECT("Sheet1!B5:B"&COUNTA(Sheet1!$B$5:$B$1000)+4+NOW()*0)

コントロールツール
ListBox　－プロパティ

ListFillRange ： ListData

で、出来るはずです。削除しても増やしても変わります。

ただ、コントロールのフォーカスの関係で、更新しないときがあります。

その場合のために、

Private Sub ListBox1_GotFocus()
　ListBox1.ListFillRange = Range("LISTDATA").Address
End Sub

を加えておけばよいと思います。

お礼

参考になる手法、有難うございました
それで、お聞きしたい事があるのですが
ListBoxのプロパティに「ListFillRange」の項目が見当たりません
プロパティにListFillRangeを表示させるには何か設定などが必要なのでしょうか？