[[20150528134527]]　『ListFillRangeを更新する際に、Changeイベントを秩x（さいき）　ページの最後に飛ぶ

『ListFillRangeを更新する際に、Changeイベントを停めたい』（さいき）

 いつも勉強させていただいております

 シート上(報告シート)に設置したActiveXのコンボボックスのChangeイベントを停止する方法が知りたいです

 コンボボックスで選んだスタッフの販売一覧を、元データシートから表示させています

 スタッフは変動ありで、人数も名前も確定した一覧がありません

 元データ更新のタイミングで報告シートのコマンドボタンのListFillRangeも更新したいので、
『元データK列を重複なしにして、指定している範囲(設定シート)に貼付け→最終行まで指定しなおす』
 というコードを書き、 元データシートにコマンドボタンを設置しました

 しかし貼付けたタイミングで、報告シートのコマンドボタンChangeイベントが作動します
 ListFillRangeを更新の際にChangeイベントを停止できればよいのですが、できなくて困っています

 いまのところ、ChangeイベントにActivesheet名で分岐処理してうまくいっておりますが、ComboBox1_Changeのイベントは停められないものなのでしょうか？

 コマンドボタンに表示させたい名前を、更新の都度Addしたほうがいいのでしょうか？
 それとも考え方がそもそも違い、確定していないものをコンボボックスで選択しようということがおかしく、セルに曖昧検索で入力させた方がいいのでしょうか？
 なにか方法がございましたらご教授ください。よろしくお願いいたします

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

こんにちは

＊それとも考え方がそもそも違

うのかどうか分からないですけど、

元データシート（Sheet2とする）のデータ範囲に名前を設定します。

セルA1以下、データの有る範囲として「リスト」という名前で範囲に

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

と設定します。

元データシートのチェンジイベントに

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect([リスト], Target) Is Nothing Then
        Application.EnableEvents = True
    Else
        Application.EnableEvents = False
    End If
End Sub

と設定しておき、報告シートのチェンジイベントに（コンボボックスのオブジェクト名はComboBox1として）

Private Sub ComboBox1_DropButtonClick()

    ComboBox1.ListFillRange = "=" & [リスト].Address(1, 1, 1, 1)
End Sub

と設定するとどうでしょうか？

（ウッシ） 2015/05/28(木) 15:31

修正です。

Private Sub ComboBox1_DropButtonClick()

    ComboBox1.ListFillRange = "リスト"
End Sub

で、良さそうです。

（ウッシ） 2015/05/28(木) 15:43

 >>報告シートのコマンドボタンChangeイベントが作動します

 これは、報告シートの【コンボボックス】のChangeイベントのことだと思いますが、
 元データシートのコマンドボタンルーティンのコードをアップされてはいかがですか。

（β） 2015/05/28(木) 17:20

 >Changeイベントを停止できればよいのですが

 ActiveXControlのイベントの制御は、フラグ変数を設けて　自分で処理するのですよ！！

 private ev_stop as boolean

 Private Sub ComboBox1_Change()
   if not ev_stop then
      '本来の処理
   end if
 End Sub

 イベントを発生させたくない処理の前に

 ev_stop=true

 と設定し、

 処理後に　ev_stop=false

 と元に戻します。

（ichinose） 2015/05/28(木) 21:19

 長らく放置してしまい申し訳ございません
 急にこの作業の前段階で大幅な変更を言い渡され、その対応でまったく今まで確認できませんでした
 ただいまより至急確認させていただきます

 >ウッシさん
 リストに名前を付けるのですね！
 なるほど…
 やってみます！

 >βさん
 申し訳ございません
 まだ確定していないこともたくさんありましたので、省略してしまいました
 見苦しいですが以下です
 スタッフ名と店舗名を判断基準として、列を変えたり、データを日付型に直したり、数字を別表から文字に直したりしておりますが、その部分はカットしております

 Private Sub ComboBox1_Change()
    Dim i As Long, k As Long
    Dim Shop As String, S_Name As String
    Dim a, m(1 To 300, 1 To 82)

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    If ActiveSheet.Name <> "報告" Then Exit Sub

    '--前のデータクリア
    If Range("B" & Rows.Count).End(xlUp).Row > 8 Then
        Range("B8:BH" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents
    End If

    '--とりあえずスタッフ名と店名確定
    '*---(コンボボックス連動させるか要確認)
    S_Name = Range("D4").Value
    With ThisWorkbook.Sheets("設定")
        Shop = .Cells.Find(What:=S_Name).Offset(, -1).Value
    End With
    Range("D3").Value = Shop

    '--貼付けデータ読み込み
    With ThisWorkbook.Sheets("元データ")
        a = .Range("A1:CD" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With

    '--該当データ探し
    '*---(同名異店舗除くのか要確認)
    k = 1
    For i = 2 To UBound(a, 1)
        If a(i, 11) = S_Name Then
            If a(i, 10) = Shop Then
                m(k, 2) = k
                m(k, 3) = a(i, 3)
                '---以下ダラダラとaからmへ必要データ書き込み
                k = k + 1
            End If
        End If
    Next i

    '--データ展開
    Range("A8:BH300").Value = m
    ActiveSheet.PageSetup.PrintArea = "$A$1:$BH$" & _
                                       Range("B" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = True
    Application.EnableEvents = True
 End Sub

 > ichinoseさん
 自分でするんですね！
 開発者に頼りきりになっておりました…
 ウッシさんの名前の定義で試した後、やってみます！

（さいき） 2015/06/01(月) 16:28

 >>自分でするんですね！

 ichinoseさんからわかりやすいコード付き回答がありますので、付け足すことはなく【蛇足】ですが。

 今回はシート上の ActiveXですけど、今後、ユーザーフォームも経験されていくと思います。
 ユーザーフォームのコントロールにも同じことがいえます。

 シートやセルは【エクセル一家】の構成員ですから、親分（エクセル:Application)が、
 「何がおきてもなりをひそめてじっとしてろ！」つまり、Application.EnebleEvents = False ですが
 そう一喝すると、子分たちは、そのいいつけを守ります。

 ところが、ActiveXは、エクセル一家の構成員ではなく【MsForms一家】に所属していて、たまたまエクセル一家に
 わらじをぬいでいるところなので、なかなか親分の統制がききません。（というか、親分は東北弁、ActiveXは鹿児島弁）
 なので、かちこみがあった場合、エクセル一家の構成員たちはおとなしくしていますが、ActiveX君は、『すわ！！天下の一大事！！』
 とばかりに、動き回ります。

 なので、親分は、子分たちに対しては東北弁で指示しますが、客分のActiveX君には鹿児島弁で指示する必要が出てきます。
 方法はいろいろありますが、代表的なのもが、ichinoseさんがアップされた、フラッグ、これを【葵の印籠】にして制御するということです。
（β） 2015/06/01(月) 17:42

 確認いたしました

 >ウッシさん
 ListFillRangeの範囲内の値が変更されれば、ComboBox1_Changeにとんでしまうようです
 私の説明が悪く、申し訳ございません 
 名前定義、覚えておきます!  ありがとうございました！

 >ichinoseさん
 するっと完了いたしました
 ありがとうございました！

 >βさん
 すんごいわかりやすい説明ありがとうございます！
 今までユーザーフォームにつっこんだこともあるんですが、ほぼ確定しているリスト時々イレギュラ…な入力用のものだったので、ListFillRangeを更新することがなかったんです
 なんとなくぼんやりと【エクセル一家】外だという認識だったので、言うこときいてくれなくて半泣きでした
 とっても勉強になりました！

 午前にできあがった前段階のものと合わせてブラッシュアップしていきます
 ありがとうございました！！

（さいき） 2015/06/01(月) 17:53

コメント返信：

[ 一覧(最新更新順) ]

[[20150528134527]] 『ListFillRangeを更新する際に、Changeイベントを秩x（さいき） ページの最後に飛ぶ

[[20150528134527]]　『ListFillRangeを更新する際に、Changeイベントを秩x（さいき）　ページの最後に飛ぶ