エクセルvba アクティブシート以外の範囲指定

現在エクセルのVBAを勉強中(かなり初心者ですが。。)で、仕事でも使う封筒印刷用のマクロを組んでみようと思っています。

作成したユーザーフォーム内のリストボックスに、宛名一覧を入力したワークシートからデータを引っ張ってきて表示しようと思っているのですが、宛名一覧を記述しているシートがアクティブの際にはうまく動作するのですが、その他のシート(封筒のレイアウトを書き込んでいるシートなど)がアクティブの時には

実行時エラー'1004'
アプリケーション定義まてはオブジェクト定義のエラーです。

というエラーが表示され、うまく行きません。
うまく行く方法をご教授いただけたらと思います。よろしくお願いいたします。

ソース(標準モジュールに記述)
Sub adrsList()
UserForm1.ListBox1.List = Worksheets("address").Range(Cells(Worksheets("address").UsedRange.Rows.Count, 8)).Value
UserForm1.Show
End Sub

※address:住所録のデータがH列に格納されているワークシートです

No.3 回答者： tom04 回答日時： 2014/11/18 22:07

こんばんは！

address　SheetのH1セル以降にデータがあるとすると

Dim lastRow As Long
lastRow = Worksheets("address").Cells(Rows.Count, "H").End(xlUp).Row
With UserForm1
.ListBox1.RowSource = "address!H1: H" & lastRow
.Show
End With

としたらどうなりますか？

※　検証はしていませんが、一般的には　プロパティで表示データを決めるコトが多いので
RowSource　を使ってみました。
address　Sheetの1行目が項目行でデータは2行目以降にある場合は
＞H1　を　H2　として調整してみてください。m(_ _)m

No.2 回答者： keithin 回答日時： 2014/11/18 22:03

初心者さんが必ず一度は引っかかるミスですが、エラーの直接の原因は

Cells(Worksheets("address").UsedRange.Rows.Count, 8)
このcellsが「どのシートの」それなのか、明示されていません。

このときＶＢＡは「アクティブシートの」指定セルを取得しようとしますが、それが
worksheets("address").range(　)
の中に入っている、つまりaddressシートのセル範囲を指定するのに他のシートのセルを使おうとして矛盾が発生しています。


このエラー自身の直接の解決方法は、

Worksheets("address").Range(Worksheets("address").Cells(Worksheets("address").UsedRange.Rows.Count, 8))...

のように、該当のcellsもしっかりaddressシートのそれだと指定する事になります。
今回のご質問の直接の回答は、ここまでです。


で。
もっとも次の問題として、これでホントにリストボックスに格納したいセル範囲が取得できてるのかという部分が、非常にアヤシイですね。
具体的にaddressシートのどの範囲をリストボックスに取り込みたいのか、つまりそもそも「具体的に」（あなたがこーなったらいいなと願ってることじゃなく）何がどういう理屈でどーなって「いま欲しいセル範囲」になるのか、そこんところを良く考えてやる必要がありそうです。

ここんところは今回のご質問内容じゃありませんので、実際のあなたのaddressシートの、具体的にどこ番地のセルにどんなデータが記入されてて、そこからどんな具合にlistboxに取り込みたいのか、あなたのエクセルを回答者の手元に再現できるように手抜きせず情報提供して、新しい次のご相談として投稿し直してみてください。

No.1 回答者： taigideatta 回答日時： 2014/11/18 21:57

＊＊＊未検証なので違ってるかも＊＊＊

おそらく、Cellsの部分だと思われます。
Cells
↓
.Cells