excel2000 vba スピンボタン

質問

いつもお世話になっています。
下記内容の変更をしたいのですが、自分ではうまくいかず、お力をお貸しください。
よろしくお願いします。

一枚のデータシートと一枚の入力用フォームがあります。

入力フォームのスピンボタンのＮＯをキーにして、データシートのレコードを一件ずつ切り替えて、表示させるようにしています。

さてデータシートのオートフィルターでフィルターをかけた時に、それにあわせて、スピンボタンのＮＯを飛ばすようにしたいのですが、どのように修正していいか、わかりません。

現状ですと、下記プロシージャですが、いまのままだと、スピンボタンの値が一つずつしかかわりません。

'スピンボタンの値が変わったらテキストボックスに反映
Private Sub SpinButton1_Change()
TextBox1.Value = SpinButton1.Value
Call hyouji
End Sub

Private Sub hyouji()
'データを検索して表示する
Dim fRange As Range
Dim fRow As Long
Set fRange = Sheets("data").Columns(3).Find(what:=TextBox1.Value, _
LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
If (fRange Is Nothing) Then '
MsgBox "Noがみつかりません", vbExclamation
Exit Sub
End If

fRow = fRange.Row '検索されたNoの行位置を求める
With Worksheets("data")
TextBox2.Value = .Cells(fRow, 4).Value
TextBox3.Value = .Cells(fRow, 5).Value
TextBox4.Value = .Cells(fRow, 6).Value
TextBox5.Value = .Cells(fRow, 7).Value
TextBox6.Value = .Cells(fRow, 8).Value

End With
SpinButton1.SetFocus
End Sub

※現物ファイルを下記に投稿（No5643）させていただきました。見ていただけると幸いです。
http://www.kent-web.com/pubc/book/test/uploader/uploader.cgi

回答No.3

cj_mover · ベストアンサー

こんにちは。＃１、＃２、ｃｊです。
いや私説明下手ですから無理もないかと。

一応、フォローしておこうかと思いますが、Yahoo!の方で付いている回答は
SpinButton1_Changeイベントを一切使わない前提で書かれていますから、
SpinButton1_Change をコメントブロックしてからでないと試せないと思います。

あちらと同じコンセプトで書いたものを私も用意してはあったのです。
今回の目的を果たすだけならば、あちらの方が簡単ですが
今後更に仕様変更していく時に、どうかな？と思うことなどありましたので
そのまま＃１のように_Changeイベントで提示しました。

でも、一度、試した上で、どちらの方法を採るかちゃんと選んだ方が良いと思います。
今回の目的の部分だけでいえば、私が提示したものの方がメンテナンス
が少しだけ難しいと思います。

もしや、あちらのコード試せなくてお困りなのではないかと思ってのレスです。

お礼

ご丁寧に補足頂きありがとうございます。

いろんな方の回答がお聞きしたくて、YAHOOでも投稿させていただきました。

cj_mover様にコメント頂けたとおり、どちらのプロシージャでも、上手く行きました。

私としては、２通りの対処方を学ぶことが出来たので、夢のようにありがたい話です。

cj_mover様のコードですと、現コードに簡単に修正できますが、ご指摘とおり、
やはり第３者、並びに私自身がコードを見たときに、理解するのに苦しいという事も
あり、残念ながら、SpinButton1_Change の使用を見送りました。

しかし、せっかく教わったこのプロシージャの知識は、何らかの機会で必ず活かせる
と確信しています。
色々とご親切、ご丁寧にコメント頂き、ありがとうございました。

回答No.2

cj_mover

こんにちは。＃１、ｃｊです。お礼欄へのレスです。
さっそく説明に入ります。

仮に、SpinButton1.Value が元々 ３０ だった時にSpinDownボタンを押したとします。
この段階で SpinButton1.Value は ２９ に変わり、続いてPrivate Sub SpinButton1_Change()がCallされます。

Private Sub SpinButton1_Change()

SpinUp なら１、SpinDown なら－１、増減どちらなのか調べておきます。
SpBtnPrev は前回このプロシージャが呼ばれた時の SpinButton1.Value つまり、この例では３０
この段階の SpinButton1.Value は ２９ ですから、２９－３０＝－１。つまり SpinDown だと判ります。
　　nSgn = Sgn(SpinButton1.Value - SpBtnPrev)
SpinButton1.Value を 監視用の変数 SpBtnPrev に確保しておきます。
これは専ら nSgn を調べる為に使う変数です。
　　SpBtnPrev = SpinButton1.Value
ここまでが下ごしらえ。

まず、Sheets("data").FilterMode をチェックして
With Sheets("data")
If .FilterMode Then
フィルターが掛かっている時は一旦
i = SpBtnPrev
　２９ に対応した行 つまり Sheets("data").Rows(33) が非表示であるか調べます。
　非表示なら、続けて ２８、２７、２６、とそれぞれ対応した行を調べます。
　非表示ではない行にあたった処でLoopを抜けその結果が変数 i になります。
Do While .Rows(i + 4).Hidden
i = i + nSgn
Loop
　２９ に対応した行 が非表示でない場合は↑このループは一度も実行されませんので
　i = SpBtnPrev のままです。その場合は従来の処理に進みます。
　And そして、SpinButton1.Value でいうと １ から ２９ 対応した行
　　　つまり Sheets("data").Rows("5:33") がすべて非表示であった場合に変数 i は
　　　タイトル行　Sheets("data").Rows(4) を意味する ０ になり、
　　　SpinButton1.Value = 0 にはできない設定になっているせいでエラーになるので
　　　この場合を条件分岐で除外します
If i <> SpBtnPrev And i > 0 Then
　　　　ここまでの処理で得られた変数 i の値 つまり "飛ばす"先に当たる数値を
　　　　SpinButton1.Value に設定します。　　　　
SpinButton1.Value = i
　　　　この↑処理によって、SpinButton1 の値が変更されるのに伴い、
　　　　Private Sub SpinButton1_Change() がCallされます。
　　　　"ここ"のプロシージャの処理は一旦保留になり、"ここ"での処理とは別に
　　　　新たに呼び出された（再帰的に呼び出された） Private Sub SpinButton1_Change()
　　　　が処理されます。
　　　　~そちら~↑では "SpinButton1.Value に対応した行が非表示ではない前提"で処理が始まるので
　　　　まっさきに、従来の処理を済ませます。
　　　　再び、"ここ"のプロシージャでの処理に戻って来るのですが、
　　　　処理するべきことは先ほどCallしたプロシージャの方で済んでいて
　　　　他にやることもないので、プロシージャを抜けて終わりにします。
Exit Sub
End If
End If
End With
フィルターが掛かっていなければ、そのまま従来の処理をします。
TextBox1.Value = SpinButton1.Value
Call hyouji
End Sub

///
なお、無限ループについては

Private Sub SpinButton1_Change()
' ・
' ・
' ・
　　SpinButton1.Value = i
' ・
' ・
' ・
End Sub

この↑ような記述に関しては
きちんと Exit Sub　するように注意した方がいいということです。

説明解りにくいようでしたら、また遠慮なく訊いてください。

お礼

ご無理言いましたが、にもかかわらずご親切丁寧な回答をありがとうございました。

理解するのに、時間がかかりましたが、イメージすることが出来ました。

上級者の方々の深い世界には、頭が下がります。

回答No.1

cj_mover

全体を見通す余裕がないので、SpinButton1_Changeから派生する処理しかチェックしていませんが
一応、お望みの"スピンボタンのＮＯを飛ばす"ことは出来ていると思います。
dataシートで表示されているレコードだけをフォームに表示するという理解であってますよね？
dataシートで非表示のレコードをフォームに表示しない為にSpinButton1のValueを"飛ばす"と。

SpinButton1.Valueを監視する変数 SpBtnPrev これの宣言部は必ずUserFormモジュールの先頭に。
nSgn は SpinButton1 が増加したか減少したかを[ 1 | -1 ]で捉えます。
dataシート にフィルターが掛けられていれば、
SpinButton1.Value に対応した行が非表示である限り i は表示されているセルを nSgn 方向に探しに行きます。
当初のSpinButton1.Value に対応した行が非表示であった場合で、"飛ばす"候補となるインデックスが
　ゼロでない場合はSpinButton1.Valueを更新します。
SpinButton1.Valueを更新すると、Private Sub SpinButton1_Change()処理の途中で
再帰的に（二重に）Private Sub SpinButton1_Change()が呼び出され、そちらで通常の処理が済まされます。
元のPrivate Sub SpinButton1_Change()に戻ってからは、何も処理せず Exit Sub します。
手を加える時は、無限ループに気を付けてください。
Private Sub UserForm_Initialize()の追加分は nSgn を減少に定義してエラーを回避する為です。
細かな動作確認は出来ていませんので、何か問題があれば、詳しく具体的に伝えて下さい。

余談になりますが、今回ご相談の件でアドバイスを真摯に考えると
どうしても、SpinButton 以外のコントロールを使うことを奨めたくなります。
例えば、CommandButtonふたつであれば、増減を監視する必要もない訳で、、、。
将来的に、ご自分で設計する余裕ができた頃に、思い出して検討してもらえたら、と思います。
今回は現状を活かして比較的簡素なものを提案しています。

Private SpBtnPrev As Long ' モジュール先頭で！！宣言

Private Sub SpinButton1_Change()
Dim nSgn As Long
Dim i As Long
nSgn = Sgn(SpinButton1.Value - SpBtnPrev)
SpBtnPrev = SpinButton1.Value
　　With Sheets("data")
　　　　If .FilterMode Then
　　　　　　i = SpBtnPrev
　　　　　　Do While .Rows(i + 4).Hidden
　　　　　　　　i = i + nSgn
　　　　　　Loop
　　　　　　If i <> SpBtnPrev And i > 0 Then
　　　　　　　　SpinButton1.Value = i
　　　　　　　　Exit Sub
　　　　　　End If
　　　　End If
　　End With
TextBox1.Value = SpinButton1.Value

Call hyouji

End Sub

Private Sub UserForm_Initialize()

Dim fRange As Range
Dim fRow As Long

fRow = Sheets("data").Range("D50000").End(xlUp).Row - 3
SpBtnPrev = 65536 ' ←追加◆
SpinButton1.Value = fRow

SpinButton1.SetFocus

End Sub

お礼

回答ありがとうございます。試したところ、うまくいきました。
解説もご丁寧にいただけたので、なんとか理解したいと思い、なんども読み返して理解しようとしているのですが、下記部分がどうしても理解出来なくて苦しんでいます。
もしご迷惑でなければ、もう少し詳しく解説いただけないでしょうか。
ご無理ばかり申し上げてすみません。

「当初のSpinButton1.Value に対応した行が非表示であった場合で、"飛ばす"候補となるインデックスが
　ゼロでない場合はSpinButton1.Valueを更新します。
SpinButton1.Valueを更新すると、Private Sub SpinButton1_Change()処理の途中で
再帰的に（二重に）Private Sub SpinButton1_Change()が呼び出され、そちらで通常の処理が済まされます。
元のPrivate Sub SpinButton1_Change()に戻ってからは、何も処理せず Exit Sub します。」