こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル2000VBAでオブジェクトの指定

エクセル2000VBAでオブジェクトの指定
ワークシート上にボタン、チェックボックス、コンボボックス等のコントロールがあります。(OLEオブジェクトではありません、フォームのオブジェクトです。)
そして、それらのすべてが表示されているわけではなく、中にはVisible=False で非表示にされているものもあります。
またフォーム以外にもワードアート、ピクチャー等のオブジェクトも配置されています。

このうち、現在表示されているボタン、チェックボックス、コンボボックス等のコントロールだけを非表示にし、その後再度表示させたいのです。(最初から非表示のものは表示させない)

一応、以下のようなVBAコードで目的は達成されます。

Sub TEST01()
  Dim ob As Object
  Dim buf As Boolean, myAry As Variant

  With ActiveSheet
  
    Application.ScreenUpdating = False '画面更新停止
    For Each ob In .DrawingObjects 'Shapesではダメ
      If ob.Visible = True Then '可視なら
        Select Case TypeName(ob) '以下に該当すれば選択
          Case "Button": ob.Select (False)
          Case "CheckBox": ob.Select (False)
          Case "DropDown": ob.Select (False)
          Case "Spinner": ob.Select (False)
        End Select
      End If
    Next ob
    
    If TypeName(Selection) <> "Range" Then '対象があれば
      buf = True
      Set myAry = Selection
      .Range("A1").Select
      myAry.Visible = False '非表示に
    End If
    Application.ScreenUpdating = True '画面更新停止解除
    
    If buf Then
      MsgBox "非表示にしました。"
      myAry.Visible = True '表示
      MsgBox "再度表示しました。"
      Set varAry = Nothing
    Else
      MsgBox "非表示にする対象はありません。"
    End If
    
  End With
End Sub



質問は2つですが、どちらかへの回答でもかまいません。

1.上記コードでは対象のオブジェクトをSelectしてから
Set myAry = Selection で変数を定義しましたが、いちいちSelectしなくともよい方法を知りたいのです。
多分、対象のオブジェクトを配列に取り込めばいいのでしょうが、やり方がわかりません。

2.上記コードではいちいち
Case "Button"
Case "CheckBox"
などと、コントロールの種類を列記していますが、これを列記しないでもコントロールだと識別する方法はないのでしょうか?

お知恵をお貸しください。

投稿日時 - 2010-05-31 16:33:44

QNo.5935056

暇なときに回答ください

質問者が選んだベストアンサー

エキスパートさん、お久です。

色んなシェイプ、フォームコントロールがシート上にある
フォームコントロールには、表示、非表示のものがある
表示のされてるフォームコントロールだけ非表示にする
そのあと、再表示する

ということですね。

'-------------------------------------
Sub test()
 Dim i As Integer
 Dim Obj As Object
 Dim myControls As New Collection

 For Each Obj In ActiveSheet.Shapes
    If Obj.Type = msoFormControl And Obj.Visible Then
       myControls.Add Obj
    End If
 Next Obj

 If myControls.Count > 0 Then
    For Each Obj In myControls
       Obj.Visible = False
    Next Obj
    MsgBox "非表示にしました"

    For Each Obj In myControls
       Obj.Visible = True
    Next Obj
    MsgBox "再表示しました"
 End If
End Sub
'--------------------------------------

以上です。

投稿日時 - 2010-05-31 22:37:28

補足

DoEvents
MsgBox "非表示にしました"

としないと非表示になりませんでした。
Obj.Visible = True
が直後にあるからでしょうか?
 

投稿日時 - 2010-06-01 11:29:00

お礼

myRangeさま、ご無沙汰でした。
遅い時間に回答をありがとうございます。
その時間は、もう酔っ払ってました。(///▽///)
なるほど、コレクションとは!
こういう方法があったんですね!!とても勉強になります。
また便利な新しい呪文を覚えました、ありがとうございます。

投稿日時 - 2010-06-01 11:18:54

ANo.2

このQ&Aは役に立ちましたか?

7人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

またまた、そしてまた、登場、myRangeです。

当方のもxl2000ですが意図したとおりの動作でした。
が、今、もしやと思いマクロ起動の方法を変えてみたところ
その珍?現象を再現することができました。

以下の結果は全て▲DoEventsなし▲です。

1)動作OK: VBEからの実行(■当方の試した方法)
2)現象再現: Excelメニュー>マクロ> からの実行
3)現象再現: ActiveX、CommandButtonからの実行

なぜこうなるのかは、?????です(^^;;;

●試しに当方のコードのShapesをDrawingObjectsに変えて
上記の方法で実行してみたところ、全てOKでした。


で、勝手に結論。
シート上の図形などはShapesコレクションより
隠しオブジェクトDrawingObjectsの方が相性がいい!!


-----------------------------------------------------------------------------
それから、
>Shapesでやると、入力規則のリストもmsoFormControlになってしまう
これについては仰るとおりですね。
ただ、質問のどこにも入力規則リストの文言がなかったので。。(^;;;


エクスパートさんの質問に回答していると色々な現象に出くわすので非常に勉強になります。
最近顔を出されれなかったcj_moverさんも今回の質問には触手が動いたのではと推測します。
ではまたスキルアップになるような質問をお願いします。
以上です。

投稿日時 - 2010-06-02 11:25:59

お礼

お師匠様ありがとうございます。
こちらでも再現しました。
なるほどVBA起動方法の違いですか!
ほんと珍現象ですねえ。

by 珍姫 (*^o^*)???

あと、入力規則だけじゃなくオートフィルターもmsoFormControlでした。
Obj.FormControlTypeで調べてもすべてDropDownでまったく識別できません。
これについては別途質問を立てされていただきますね。

投稿日時 - 2010-06-02 13:58:20

ANo.5

No.3の回答者です。
Xor、排他的論理和については、ご説明の通りです。
論理演算子(論理式に使うもの)としては、
And 、Or 、Not を使う頻度が高いですが、その次ぐらいにくるのが
Xorでしょうか。
(ビット演算に使われる例の方が目に触れる機会は多いかもしれませんね。)


例えば
Dim A As Boolean
Dim B As Boolean
ならば、
□前提
If A = True Then ...
これ、A は[True|False]論理値という前提ですから、
If A Then ...
この方が自然ですよね。
そもそも、If 論理値 Then ...という構文ですから。

ただしこれ、Sheets.Visible[True|False|xlVeryHidden]
のようにtristateのものだと成り立ちません。

If A <> B Then ...
If A = Not B Then ...
左辺も右辺も論理値ならば
If A Xor B Then ...
と書くのが
私には自然で、他のは遠回りしてるように感じてしまうのです。

で、今回私はうっかり
左辺はBoolean型だと思い込んでいましたが、[ -1 | 0 ]Long型でした。
実質[True|False]ですし、
そうでなければプロシージャー自体が成立しませんから、
構わないかなぁ、という気もするのですが、
もしかして遠い未来、第三のプロパティー値が設けられないとも限らないので
今回のはやはり、Xor を <> に直してあげてください。
私の勇み足、ってことでm(__)m

それから、
配列を使った場合、一括でアクセスできるフォームコントロールの
数(?)に制限があるようです。(詳しくは知りません)

今回は皆さんにお会い出来ただけでも、私は良かったです(^^)
ではでは

myRange さん、どうもです。

投稿日時 - 2010-06-01 15:22:54

ANo.4

再度の登場、myRangeです。

>DoEvents
>MsgBox "非表示にしました"
>としないと非表示になりませんでした。
>Obj.Visible = True
>が直後にあるからでしょうか?

当方では意図したとおりに動作しますが。。
該当コードをアップしてみてください、それを試してみませう。

それから、、
 >For Each ob In .DrawingObjects 'Shapesではダメ
この●Shapesではダメ!●という文言がちょと気になったので、
回答の主眼を
DrawingObjectsを使わなくてもShapesできるのでは?
という点に置いてみました。


------------------------------------
cj_moverさん、早速、お会いできました。
当方としても嬉しい限りです。
いつものようにお手本になる回答だと思います。
これからもちょくちょく勉強させていただければと。
以上です。
 

投稿日時 - 2010-06-01 13:27:53

補足

2003でためしました。
DoEventsを入れなくとも非表示になりました。
しかし、再表示の際は、Obj.Visible = Trueの後に書かれている
MsgBox "再表示しました" をOKしないと再表示されません。
その前にDoEventsを入れればOKする前に再表示されます。

Sub test()
 Dim i As Integer
 Dim Obj As Object
 Dim myControls As New Collection

 For Each Obj In ActiveSheet.Shapes
    If Obj.Type = msoFormControl And Obj.Visible Then
       myControls.Add Obj
    End If
 Next Obj

 If myControls.Count > 0 Then
    For Each Obj In myControls
       Obj.Visible = False
    Next Obj
    MsgBox "非表示にしました"

    For Each Obj In myControls
       Obj.Visible = True
    Next Obj
    DoEvents '←挿入
    MsgBox "再表示しました"
 End If
End Sub

投稿日時 - 2010-06-01 22:39:23

お礼

さっそくありがとうございます。
コードはテストなのでお師匠さまの書いたその通りコピペしたものです。
DoEventsの挿入位置は、
MsgBox "非表示にしました" の直前の行です。
DoEventsを挿入しなくともMsgBoxを削除して、
Application.Wait Now + TimeValue("0:00:02")
とやってみたらちゃんと非表示になりました。

また再表示の際もObj.Visible = Trueの後に書かれている
MsgBox "再表示しました" をOKしないと再表示されません。
2000だからの問題かもしれませんので夜にでも自宅の2003で試してみたいと思います。

>> For Each ob In .DrawingObjects 'Shapesではダメ
> この●Shapesではダメ!●という文言がちょと気になったので、

For Each~Next でShapesを使うとTypeNameがすべてShapeを返して、当初の列挙方式だとコントロールかどうかの判断ができなかったためです。
おかげさまでObj.Type = msoFormControlで識別できることがわかりました。

ただ、Shapesでやると、ワークシート上の入力規則のリストもmsoFormControlになってしまうんですよね。
やはり、For Each~Next ではDrawingobjectsでくくり、Obj.ShapeRange.Type = msoFormControlで識別するほうが安全かなと思いました。

投稿日時 - 2010-06-01 14:04:30

ANo.3

merlionXX さん、こんにちは。
myRange さん、Wendy02 さん、こんにちは。
またお会いできて嬉しいです。

参照文字列の配列化。試しに書いてみました。
 WorkSheets(Array("Sheet2", "Sheet3")).Visible = ?
みたいに、
文字列配列(実際はVariant)を利用して、
コレクションの一部をコレクションのように扱ってみます。

Sub test()
 Const bVis As Boolean = False
 ' ' ↑引数として書き換え易いように定数にしました
 Dim Obj As Object
 Dim sRef As String
With ActiveSheet
 For Each Obj In .Shapes
  If Obj.Type = msoFormControl Then
   If Obj.Visible Xor bVis Then sRef = sRef & "," & Obj.Name
  End If
 Next Obj
 If sRef = "" Then
  MsgBox "ないよ"
  Exit Sub
 End If
 sRef = Mid$(sRef, 2)
 With .DrawingObjects(Split(sRef, ","))
  .Visible = bVis
  MsgBox bVis
  .Visible = Not bVis
  MsgBox Not bVis
 End With
End With
End Sub

' ※おふたりのご回答も参考にさせていただきました。

投稿日時 - 2010-06-01 07:44:29

お礼

cj_moverさま、ご無沙汰です。
早い時間からご回答ありがとうございます。
Xorなんてみたことない記号が出てきて面食らいましたが、「排他的理論和」??
Obj.VisibleがTrueでbVisがFalseか、Obj.VisibleがFalseでbVisがTrue ならTRUEが返る(今回はConst bVis As Boolean = FalseになってるんでObj.VisibleがTrueのときのみTRUEが返る)という理解でいいですね?

これはオブジェクトの名前を文字列変数に取り込んで最後にSplitで配列に変えてるんですね。なるほどこういう方法もあるんですね。勉強になりました
ありがとうございます。

投稿日時 - 2010-06-01 11:21:22

ANo.1

一応、実験コードだと思います。実務では、Buttons や CheckBoxes で取得するでしょうから、FormControl で選択という方法はめったに使わないはずです。

>1.いちいちSelectしなくともよい方法を知りたいのです。
>対象のオブジェクトを配列に取り込めばいいのでしょうが

最後で、Selection したものを、Visible のプロパティを一括で処理しているのですから、配列やコレクションで取り込むことは可能ですが、その後の一括処理が出来ません。また、Select を使う特例は、こうしたオブジェクトには、発生します。代表的なものは、Worksheet の作業グループ化して処理する場合です。

>2.上記コードではいちいち~コントロールの種類を列記していますが、
>DrawingObjects
Ver.5 のヘルプで確認してみましたが、DrawingObjects で検索するのは間違いないのですが、その先は、ShapeRange.Type で、識別するのが早いと思います。

>If TypeName(Selection) <> "Range" Then '対象があれば
なるほど、Select していない時は、Range になるわけですね。すぐに意味が読みきれませんでした。
私は以下のようにしてみました。

'//
Sub TestMacro1()
  Dim obj As Object
  Dim o As Object
  Dim i As Long
  For Each obj In ActiveSheet.DrawingObjects
    If obj.ShapeRange.Type = msoFormControl Then
      If obj.Visible Then
        obj.Visible = True
        obj.Select False
        i = i + 1
      End If
    End If
  Next
  If i > 0 Then
    Set o = Selection
    o.Visible = False
    MsgBox "非表示にしました。", 64
    o.Visible = True
    MsgBox "再度表示しました。", 64
  Else
    MsgBox "非表示にする対象はありません。", 48
  End If
End Sub

投稿日時 - 2010-05-31 19:42:31

お礼

Wendy02さま、ご無沙汰いたしておりました。
今回もありがとうございます。
フォームのコントロールは、ShapeRange.Type = msoFormControl で判別できるんすね!
助かりました。これでいちいち列挙する必要がなくなりました。
ありがとうございます。
これからもご指導ください。

投稿日時 - 2010-06-01 11:16:45

あなたにオススメの質問