vba_Forms内をホイールで予期せぬエラー？

質問

Excel2016上でマクロを組んでいますが、
ユーザーフォームでのリストボックス内（2個）では、通常vbaの仕様でマウスホイールが効かないということなので、
あるサイトから検索した「マウスホイール」のコードを、標準モジュール内とフォーム内に入れ込んでみると、マウスポインタがそれぞれのリスト上に入ると自動的にフォーカスされてホイールが効くように
なり、Excel起動直後では動作はある程度は良好でした。
しかし、フォームのみを再表示した時など、マウスを近づけてもフォーカスが効かない時があり、何回かクリックをしてやっとフォーカスしてホイールできる時もあります。
今回、たまたまExcel2010で起動させてみると、同様の症状になる時に「予期せぬエラー」という表示がでるようになりました。デバッグエラーとはならないため、コードの途中でストップするのではないので、どこが悪いのかわかりません。
「マウスホイール」のコードは、そのまま貼り付けただけなので、ほぼ理解はできておらず、メモリエラーのような限界値を超えているような感じなのですが、何が原因していると考えられるでしょうか？

回答No.11

kkkkkm · ベストアンサー

Excel2010で「UserForm、RowSource」は駄目だったんですね。

範囲に名前を付けて
Case "AAA"
.Names.Add Name:="List2", RefersTo:="=一覧!$A$3:$A$26"
Case "BBB"
.Names.Add Name:="List2", RefersTo:="=一覧!$B$3:$B$26"
みたいなことをやってみたのですがセルだと切り替わるのですが、フォームのリストは切り替わりませんでした。

ListBox2でRowSourceプロパティは一切使えなくなりますが(使うとそれこそ2013でもClearで予期せぬエラーになります)AddItemで対応してみてはいかがでしょう。

Private Sub UserForm_Initialize()
Dim i As Long
With Sheets("一覧")
For i = 3 To 26
Me.ListBox2.AddItem .Cells(i, "A").Value
Next i
End With
End Sub

Private Sub ListBox1_Click()
With Me.ListBox1
Select Case .List(.ListIndex)
Case "AAA"
Call ChangeItem("一覧", Range("A3:A26"))
Case "BBB"
Call ChangeItem("一覧", Range("B3:B26"))
Case Else

End Select
End With
End Sub

Private Function ChangeItem(ByVal WSName As String, ByRef mRange As Range)
Dim i As Long
Me.ListBox2.Clear
With Sheets(WSName)
For i = mRange.Row To mRange.Count + mRange.Row - 1
Me.ListBox2.AddItem .Cells(i, mRange.Column).Value
Next i
End With
End Function

お礼

すごい！
この後半のAddItemの方法でエラーなしにExcel2010で動作しました。ホイールでlist2の表示もスムーズに。
（一部、list2のセル指定を修正しましたが。）
Case "BBB"
Call ChangeItem("一覧", Range("B3:B26"))
Case "CCC"
Call ChangeItem("一覧", Range("C3:C26"))　・・・

まだ理解はできていませんが、.RowSourceを使わない方法があるのですね。よかった。
大変お世話になりました。

補足

追）list1側も修正しました。
For i = 3 To 26
Me.ListBox2.AddItem .Cells(i, "A").Value
　⇒ Me.ListBox1.・・・

回答No.10

kkkkkm

> UserForm_Initialize()の中に、ListBox1のRowSourceが6タブ分ある

すみません、タブのページがいくらあってもListBox1は1個だと思うのでRowSourceが6個分あるというUesrFormの作られ方がよくわかりません。

とりあえずタブを除くなど最小単位(特にマウスホイールのコードはエラーが回避するまで削除だと思います)でテストして、機能追加しつつエラーが出るところで考えたほうがいいような気がします。

お礼

長い間、失礼しました。
いろいろ検索して試してみましたが完全に解消できる方法はなく、結論としてはExcel2010の一種のバグというか、セキュリティ上の問題で「UserForm、RowSource」等の条件が揃うと”内部のどこかで許可させなくなった”のではないかと思うに至りました。
試したのは、Excel2010に限り、
(1)新規ファイルで1タブのUserFormを作成しても「予期せぬエラー」
(2)マウスホイールコードを入れなくても「エラー」あり
(3)RowSourceでListBox2に表示させると「エラー」あり
(4)ListBox2のセルデータが全て空白でも「エラー」あり
(5)ListBox1でのRowSourceを止めると「エラー」なし
(6)Excel2010→Excel2016のPCでは「エラー」なし
(7)Excel2010→Excel2003へ変換しても「エラー」あり
(8)それをExcel2003のPCで使用すると「エラー」なし
(9)Excel2010をインストールし直しても「エラー」あり
(10)別のPCに新規でoffice2010を入れても「エラー」あり
(11)office2010あたりでセキュリティアップデートでの不具合が多くなっている模様
等のように、Excel2010のみの症状と結論付けし、Excel2016では実用上問題ないということでそのまま使うことにしました。
皆さん、いろいろとありがとうございました。

補足

追）
(12)タイマーで0.1～1秒程度、待たせても「エラー」あり
でした。

回答No.9

kkkkkm

> 最後には「UserForm1.Show」で停止

Formのコードの中に「UserForm1.Show」があるのですか？

お礼

いえ、外側にあるのですが、
エラーが出た後、ListBoxの入ったUserForm1を一旦閉じて、再表示した場合に、UserForm_Initialize()の中に、ListBox1のRowSourceが6タブ分あるので、最終はそこの1番目で止まります。
しかし、Excel自体を再起動しないとエラー復帰とはなりませんでした。

回答No.8

kkkkkm

エラーとは関係が無いとは思いますが

With ListBox2
Select Case ListBox1.List(ListBox1.ListIndex)
Case "AAA"
ListBox2.RowSource = "一覧!A3:A26"

With ListBox2にしているのですから「ListBox2.RowSource」ではなくても「.RowSource」でいいと思います。

With Me.ListBox2
Select Case Me.ListBox1.List(Me.ListBox1.ListIndex)
Case "AAA"
.RowSource = "一覧!A3:A26"

> 「_Click()」「_Change()」「.RowSource」があると、ListBox1で上下キーでもエラー発生するようです。

カーソル移動で_Click()が発生するみたいなので_Click()内で.RowSourceを外してListBox2を変更しないようにし、マウスクリックして離したときのイベントで.RowSourceを変更する用にしてみたのでよろしければ試してみてください。

Private Sub ListBox1_Click()
With Me.ListBox1
Select Case .List(.ListIndex)
Case "AAA"
.Tag = "一覧!A3:A26"
Case "BBB"
.Tag = "一覧!B3:B26"
'（・・・他に10個位あり）
Case Else
.Tag = ""
End Select
End With
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.ListBox2.RowSource = Me.ListBox1.Tag
End Sub

お礼

たびたびありがとうございます。
なるほど、「.RowSource」だけでよいのですね。
また「.Tag」方法は、List1をスクロールはスムーズにできて、クリックするまでList2に表示しないのでエラーはでませんでしたが、やはりクリックしないでList2に次々表示したいです。
そこで元の「.RowSource」後に一旦Msgboxで1行毎に停止させてみると、上下キーでもそのうちエラーがでました。次に「Me.ListBox2.SetFocus」をコメントアウトするとエラーとなってもどこにも停止せず、最後には「UserForm1.Show」で停止し、さらにはその中の別の「.RowSource」の部分で
「実行時エラー’-2147467259(80004005)’　RowSourceプロパティを設定できません。予期せぬエラーが発生しました。」
と出ました。どうやら「.RowSource」に原因があるようですが、システムメモリ不足ということなのかな？

回答No.7

kkkkkm

現状、ListBox1の選択肢をもとにListBox2の一覧を表示する機能と、マウスホイールでスクロールさせる機能を同時に含んだマクロで実行しているのだと思いますが、両者を完全に切り離して
フォームの仕様は同じにして
ListBox1の選択肢をもとにListBox2の一覧を表示する機能だけ(マウスホイールのマクロは削除されている)のブックで矢印キー操作でどうなるか
マウスホイールでスクロールさせる機能だけ(ListBox連携のマクロは削除されている)のブックででスクロールさせたらどうなるか
を確認してそれぞれ単体で異状がなければ(どちらかだけ異状があればそちらを調査)
Me.ListBox2.SetFocus
のエラーの原因をネットで検索するなどして(APIの仕様も検索しなくてはいけなくなるかもしれませんが)探って対処することになると思います。

お礼

アドバイスありがとうございます。調査の結果、
(1)ListBox1→ListBox2の一覧を表示する機能だけ(マウスホイールのマクロは削除)で矢印キー操作
　　　⇒「エラーあり」（ホイールマクロ関係なし）
(2)マウスホイールでスクロールさせる機能だけ(ListBox連携のマクロは削除)でスクロール
　　　⇒「エラーなし」（ListBox連携のマクロが原因？）
ーー（ListBox連携マクロ）ーーーーーーーーー
Private Sub ListBox1_Click()　'「←_Change()ではエラーあり。_DblClick()ではListBox2に瞬時表示しないためエラー出ず。」
With ListBox2
Select Case ListBox1.List(ListBox1.ListIndex)
Case "AAA"
ListBox2.RowSource = "一覧!A3:A26"
Case "BBB"
ListBox2.RowSource = "一覧!B3:B26"
（・・・他に10個位あり）（”一覧”内データ無でもエラー有 ）
End Select
End With
End Sub
ーーーーーーーーーーーーーーーーーーーーーーー
この中で、ListBox2を表示するための「_Click()」「_Change()」「.RowSource」があると、ListBox1で上下キーでもエラー発生するようです。これらの回避方法はまだ分かっていません。

回答No.6

kkkkkm

> ListBox1を速いホイール中に予期せぬエラーとなり、Me.ListBox2.SetFocusで停止したりしています。

ListBox1にマウスカーソルがあるのにMe.ListBox2.SetFocusで停止というのも不思議な気がしますね…。

一度今回のマクロを止めて普通に上下矢印キーで上下に素早く移動させたら問題は出ないでしょうか。

あと、 PostMessageする前に微妙に時間を空けてみるとか
http://blogwizhook.blog.fc2.com/blog-entry-158.html

この際やけくそでPostMessageやめて
SendKeys ("{UP}")
SendKeys ("{DOWN}")
でどうなんだろうとか

すみません、良く考えずに思いつくままですが

> そのタブ毎ににListBox1からフォーカス自動選択したものがListBox2に詳細表示される

このあたりでなにか時間を取られているということはないでしょうか。
ListBox2をダミーのデータで固定表示して試してみてもいいのかもしれません。

お礼

たびたびありがとうございます。
PostMessageは、まだ理解できていませんが、
ListBox1で上下（↓↑）キーでも「予期せぬエラー」が出ました。その時、ListBox2の表示はListBox1の選択項目と合致しているのですが、エラー復帰後、以降はList1をホイールで動かしてもList2は動かず、リンクが切れているようでした。
List2にマウスが入ると必ず「エラー」がでて、３回くらい繰り返すと、Me.ListBox2.SetFocusで停止していました。
List1の次の選択に移ろうとして、List2の表示ができなくなったので移る前に停止しているような感じです。

回答No.5

kkkkkm

> Excel2010では相変わらずマウスホイールを早めに回転させるとたまに動きが引っかかったようになって「予期せぬエラー」がでました。

なんとなくですが、PostMessageあたりで引っかかっているのかもしれませんね。

最後のerrH:に
errH:
MsgBox "エラー番号:" & Err.Number & vbCrLf & _
"エラーの種類:" & Err.Description, vbExclamation

もしかしたらこちらかも

MsgBox "Error code :" & Err. LastDllError

としたら何かでるのかもしれません。

お礼

たびたびありがとうございます。
Excel2010では、今回のMsgBoxでは特に表示されませんでしたが、前回の
Me.ListBox2.SetFocusの辺りでたまにエラー停止するようになりました。
正確にいうと、1フォーム内に6タブあり、そのタブ毎ににListBox1からフォーカス自動選択したものがListBox2に詳細表示されるのですが、その表示範囲はセル指定をマクロに入れており、スムーズに表示しています。
また、フォーカスはどちらもスムーズに移動して詳細表示もするのですが、そのListBox1を速いホイール中に予期せぬエラーとなり、Me.ListBox2.SetFocusで停止したりしています。ListBox2上のホイールではエラーになりません。ListBox1上で問題があるみたいです。
なお、実務pcのExcel2016での上記設定では、一度引っかかったことがあった位で、エラーもなく、今のところ実用上は問題なさそうです。

回答No.4

kkkkkm

No3の補足です。

Windowsは
Windows10 1909
です。

お礼

こちらは、Windows10 20H2でした。

回答No.3

kkkkkm

> 2016でエラーメッセージがでなければ、このまま使うことも考えますが、もし原因がわかればと。

2013 32bitでやってみたのですが、カーソルを移動してもフォーカスが移動先にいかないので、一回クリックしても無反応になる事が殆どのタイミングでありました。
なので、それぞれのMouseMoveに
(ListBox1_MouseMoveの場合だと)
Me.ListBox1.SetFocus
を
HookListBoxScroll
の前に入れたら、とりあえずフォーカス移動して問題なく動作してるような感じです。
愚策のような気もしますがよろしければ試してみてください。

お礼

たびたびありがとうございます。
このとおりやってみると、確かにフォーカスは確実に移動できました。Excel2016では使えそうです。
なお、Excel2010では相変わらずマウスホイールを早めに回転させるとたまに動きが引っかかったようになって「予期せぬエラー」がでました。スクロール量がオーバーしているのかな。

回答No.2

kkkkkm

どのサイトのコードなのか示した方がいいと思いますが…。
どのようなものか分からないのに何が原因なのかは考えられないと思いますよ。

予期せぬエラーは以下の原因ですし
https://docs.microsoft.com/ja-JP/office/vba/language/reference/user-interface-help/unexpected-error-quitting

お礼

さっそくにありがとうございます。
いくつかサンプルコードはあったのですが、
jizilog.com/vba-listbox-userform#toc7
(ListBoxでのマウススクロール有効化)
これが、そのまま使うことができました。
2016でエラーメッセージがでなければ、このまま使うことも考えますが、もし原因がわかればと。

回答No.1

kon555

まずExcelの2016→2010でマクロの挙動が異なる件ですが、軽く検索すると2010→2016でそうした事例がひっかかりましたので、何かしら細かい仕様差などが存在する可能性はありますね。
http://ooltcloud.sakura.ne.jp/blog/201712/article_22082901.html

　これはソフト全般に言える事ですが、旧バージョンから新バージョンへのデータ移行などはある程度対応可でも、新から旧へはメーカー側があまり丹念には作り込んでいなかったりするケースが多々あります。
　特にExcelは2013を境にソフト自体の仕様変更が入っているので、今回のケースはその辺りがひっかかっている可能性が高いかと。
https://www.nsdbi.co.jp/service/office/sample.html

　元々2016でも安定した挙動のマクロではないという事ですから、2010での使用は諦めた方がいい様に思いますね。

お礼

さっそくにありがとうございました。
Verが変わると、色々不具合が出るのですね。
ハードの問題かも、ということでなかなか難しいそうですが、
今のところ、2016ではエラーメッセージは出ていないので、このままという手もあります。