Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2517 / 13646 ツリー ←次へ | 前へ→

【67543】ActiveSheetにあるCombobxの値 ichi 10/12/11(土) 12:01 質問[未読]
【67544】Re:ActiveSheetにあるCombobxの値 kanabun 10/12/11(土) 17:05 発言[未読]
【67546】Re:ActiveSheetにあるCombobxの値 ichi 10/12/12(日) 9:29 質問[未読]
【67547】Re:ActiveSheetにあるCombobxの値 kanabun 10/12/12(日) 10:59 発言[未読]
【67548】Re:ActiveSheetにあるCombobxの値 ichi 10/12/12(日) 12:12 お礼[未読]
【67567】Re:ActiveSheetにあるCombobxの値 ichi 10/12/14(火) 11:20 質問[未読]
【67568】Re:ActiveSheetにあるCombobxの値 Jaka 10/12/14(火) 11:32 発言[未読]
【67569】Re:ActiveSheetにあるCombobxの値 kanabun 10/12/14(火) 13:08 発言[未読]
【67570】Re:ActiveSheetにあるCombobxの値 ichi 10/12/14(火) 14:15 お礼[未読]
【67545】Re:ActiveSheetにあるCombobxの値 kanabun 10/12/11(土) 17:09 発言[未読]

【67543】ActiveSheetにあるCombobxの値
質問  ichi  - 10/12/11(土) 12:01 -

引用なし
パスワード
   Excel上でComboboxを作成してそこにある値を変数に格納したいのですが
 以下のように書いてCombobxまではできるのですが
 変数の格納のしかたが解りません
 また、ここで出来たCombobxに名前を付けることができるのでしょうか
 よろしくお願いいたします 
   ActiveSheet.DropDowns.Add(180, 20, 70, 15).Select
    Selection.ListFillRange = "コード!$B$4:$B$7"
    Selection.DropDownLines = 8

【67544】Re:ActiveSheetにあるCombobxの値
発言  kanabun  - 10/12/11(土) 17:05 -

引用なし
パスワード
   ▼ichi さん:

> また、ここで出来たCombobxに名前を付けることができるのでしょうか

名前はNameプロパティに好きな名前をセットしてください。

Sub AddDropDown()

 With ActiveSheet.DropDowns.Add(180, 20, 70, 15)
   .Name = "myCombo"
   .ListFillRange = "コード!$B$4:$B$7"
   .LinkedCell = "$A$4"
   .DropDownLines = 8
 End With

End Sub


> 変数の格納のしかたが解りません

具体的にお願いします。

【67545】Re:ActiveSheetにあるCombobxの値
発言  kanabun  - 10/12/11(土) 17:09 -

引用なし
パスワード
   あとフォームコントロールについては
ここが参考になると思います。

ht tp://www.eurus.dti.ne.jp/~yoneyama/Excel/form.html

フォームコントロールというのはActiveXコントロールに比べ、
枯れたコントロールというのか安定しているのですが、
イベントがないので、目的により使い分けるのがポイントですね

【67546】Re:ActiveSheetにあるCombobxの値
質問  ichi  - 10/12/12(日) 9:29 -

引用なし
パスワード
   kanabun さん
ご回答有難うございました
   >> 変数の格納のしかたが解りません
   >具体的にお願いします
出来たComboboxの中に存在する値をクリックして取得し、変数に格納
この変数をExcel上のセルに書きたいのです
よろしくお願いいたします

【67547】Re:ActiveSheetにあるCombobxの値
発言  kanabun  - 10/12/12(日) 10:59 -

引用なし
パスワード
   ▼ichi さん:
>   >> 変数の格納のしかたが解りません
>   >具体的にお願いします
>出来たComboboxの中に存在する値をクリックして取得し、変数に格納
>この変数をExcel上のセルに書きたいのです


「変数」という言葉が、分かったようで分からないのですが、
つまり、↓こういうことですか?

フォームのComboBox(ドロップダウンのこと)でどれかのリストが
選択されたら、その選択アイテムをワークシート上のセルに表示する


▼もしそういうことだとすると、これはフォームのDropDownを
 使わなくても、「入力規則」でできることですよね?

手順1. 「コード」シートの範囲に名前を定義しておく
   「コード」シートのリスト範囲を選択
   Ctrl+[F3]で(名前定義)
      名前    myList ←好きな名前をつけてください
      参照範囲  =コード!$B$4:$B$8
手順2. 入力規則を設定するシートの目的のセル(たとえば
   Sheet1![A4] とします)を選択し、
   メニュ−[データ]-[入力規則]-[設定]タブ
    (入力値の種類)→ リスト
    (元の値)  → =myList

これだけで、[A4]セルがアクティブになれば myListという名前を
つけた範囲がDropDownにリストされていますから、そのどれかの
リストを選択すればただちに [A4]セルに選択されたアイテムが
代入されます。

▼これをシート上に置いた
 フォームツールボックスのドロップダウンComboを使ってやろうと
すると、DropDown内のリストのどれかのアイテムが選択されたとき、
発生するイベントがない(コントロールツールボックスのComboBoxには
それがあります)ので、なんらかの代替手段を考えないといけません。

DropDown内のアイテムが選択されたときというタイミングを考慮に
入れなくてもよいのであれば、
さきほど↓のようにして作ったDropDown
> Sub AddDropDown()
>  With ActiveSheet.DropDowns.Add(180, 20, 70, 15)
>    .Name = "myCombo"
>    .ListFillRange = "コード!$B$4:$B$7"
>    .LinkedCell = "$B$4"
>    .DropDownLines = 8
>  End With
> End Sub
の .LinkedCell = "$B$4" が [B4]なので、
このCellに現在DropDownの何番目のリストが選択されているか
の情報が数値で入っていますから、
標準モジュールに
Sub SetDropDownValue()
  Dim n As Long
  n = [B4].Value
  If n > 0 Then
    [A4].Value = Excel.Range(DropDowns("myCombo") _
           .ListFillRange).Item(n).Value
  End If
End Sub
のように書いておき、手動で Sub SetDropDownValue() を呼び
出すことにより、[A4]セルに選択されているアイテムが転記で
きます。


▼ただ、↑は実際におやりになりたいことではないですよね?
最初の 入力規則のようなことがやりたいのであれば、もうちょ
っと工夫が必要です。

手順1. 標準モジュールに以下のプロシージャを書いておきます。
Sub myCombo_Change()

  With ActiveSheet.DropDowns(Application.Caller)
   
    ActiveSheet.Range("A4").Value = .List(.ListIndex)

  End With
End Sub

手順2. シート上のDropDown (myCombo) を右クリック
  マクロの登録メニューより マクロ名をうえの
  「myCombo_Change」とします。

これだけです。で、DropDown内のどれかのアイテムを選択しま
すと、[A4]セルにその選択アイテムの値が代入されます。


いまやりたいことはどういうことかを再度吟味し、最適な方法を
選んでください。

【67548】Re:ActiveSheetにあるCombobxの値
お礼  ichi  - 10/12/12(日) 12:12 -

引用なし
パスワード
   kanabun さん
大変ご丁寧なご教示有難うございました
早速トライしてみます

【67567】Re:ActiveSheetにあるCombobxの値
質問  ichi  - 10/12/14(火) 11:20 -

引用なし
パスワード
   kanabun さんに教えて頂きましたようにトライしました
3点ほど再度教えていただきたいので
よろしくお願いいたします
以下のように書きました

Sub Make_DropDown()
    ActiveSheet.DropDowns.Add(200, 15, 70, 15).Select
 1) ここの座標値(200, 15, 70, 15).ですが座標値(ポイント値)
    の決め方が不明で 200,15 は適当に決め配置したい場所を
    探って決めました 
    座標値の基準が解りません
    Selection.Name = "myCombo"
    Selection.ListFillRange = "コード!$B$4:$B$7"
    Selection.LinkedCell = "$B$4"
    Selection.DropDownLines = 8   
    ActiveSheet.Shapes("myCombo").Select
    Selection.OnAction = "Mycomb_Change"    
    ActiveSheet.Shapes("myCombo").Select
    Range("A1").Select
End Sub

Sub Mycomb_Change()
  With ActiveSheet
    .DropDowns (Application.Caller)
    .DropDowns ("myCombo")
     ' ActiveSheet.Range("A4").Value = .List(.ListIndex)
 2)  ここでエラーがでましたので コメント にしました
    .Range("K4").Value = ActiveSheet.DropDowns("myCombo").ListIndex
 3)  Range("K4").Value には選択された項目の順番(2番目なら2が)
     書かれ 項目名を書きたいのですが   
  End With
    ActiveSheet.Shapes("myCombo").Select
    Selection.Cut
End Sub

よろしくお願いいたします

【67568】Re:ActiveSheetにあるCombobxの値
発言  Jaka  - 10/12/14(火) 11:32 -

引用なし
パスワード
   >    ActiveSheet.DropDowns.Add(200, 15, 70, 15).Select
> 1) ここの座標値(200, 15, 70, 15).ですが座標値(ポイント値)
>    の決め方が不明で 200,15 は適当に決め配置したい場所を
>    探って決めました 
>    座標値の基準が解りません
>    Selection.Name = "myCombo"
               ↑
   せっかく名前をつけているのに使わないのは、もったいない。

ht tp://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=157;id=FAQ

【67569】Re:ActiveSheetにあるCombobxの値
発言  kanabun  - 10/12/14(火) 13:08 -

引用なし
パスワード
   ▼ichi さん:
>Sub Make_DropDown()
プロシージャは 一回こっきりの処理なので、マクロでしなくとも
手動で作ってもいいのでは?と思いました。

どうしてもマクロでやりたい、何らかの理由がおありなら、
>    ActiveSheet.DropDowns.Add(200, 15, 70, 15).Select
> 1) ここの座標値(200, 15, 70, 15).ですが座標値(ポイント値)
>    の決め方が不明で 200,15 は適当に決め配置したい場所を
>    探って決めました
>    座標値の基準が解りません

セルでいうとどのあたりですか?
たとえば [B10]セルのうえにDropDownを置きたいのなら、
 Sub Make_DropDown()
  Dim c As Range
  With ActiveSheet
    set c = .Range("B10")
    With .DropDowns.Add(c.Left, c.Top, c.Width*1.5, c.Height*1.5)
     .Name = "myCombo"
     .ListFillRange = "コード!$B$4:$B$7"
     .LinkedCell = "$B$4"
     .DropDownLines = 8
     .OnAction = "Mycomb_Change"
    End With
  End With
End Sub
といった感じです。このばあい、DropDownは[B10]セルの左上から始まり、
横幅、高さが[B10]セルの1.5倍のサイズになります。

なんども言いますけど、一回だけの処理なので、手操作で作ったほうが
速いかと思いますが。

> Sub Mycomb_Change()
>   With ActiveSheet
>     .DropDowns (Application.Caller)
>     .DropDowns ("myCombo")
>      ' ActiveSheet.Range("A4").Value = .List(.ListIndex)
>  2)  ここでエラーがでましたので コメント にしました
>     .Range("K4").Value = ActiveSheet.DropDowns("myCombo").ListIndex
>  3)  Range("K4").Value には選択された項目の順番(2番目なら2が)
>      書かれ 項目名を書きたいのですが   
>   End With
>     ActiveSheet.Shapes("myCombo").Select
>     Selection.Cut
> End Sub

With の使い方がおかしいですね?
> Sub Mycomb_Change()
   With ActiveSheet.DropDowns(Application.Caller)
    ActiveSheet.Range("A4").Value = .List(.ListIndex)
>   End With
> End Sub
くらいでどうでしょう?

こう書いておくと、
DropDown(名前「myCombo」)には
>  .OnAction = "Mycomb_Change"
とマクロの登録がしてありますから、
▼ドロップダウンリストからいずれかのリストアイテムが選択されると、
標準モジュールの「Mycomb_Change」にプログラムの実行が移ります。
  ▼
> Sub Mycomb_Change()
>   With ActiveSheet.DropDowns(Application.Caller)
  Application.Caller には 呼び出し元のコントロールの名前が入っています。
  つまり、上の文は、
  With ActiveSheet.DropDowns("myCombo")
 と同じです。
  (じゃぁ、はじめからそう書いとけばいいじゃん?
 ということになりますが、複数のドロップダウンをシート上に配置し、
 それらすべてに .OnActionで同じマクロMycomb_Change() を登録して
 呼び出し元のコントロール名 Callerにより処理を分岐するといった
 使い方ができるようになるので、具体的なドロップダウンの名前で
  なく Application.Caller と名前を特定しない書き方をしています)

>   With ActiveSheet.DropDowns(Application.Caller)
>    ActiveSheet.Range("A4").Value = .List(.ListIndex)
>   End With

これだけで、シートの[A4]セルに いま選択されたドロップダウン内の
選択アイテム(値)が代入されるわけですが、これを With句を
使わないで日本語で書くと こういう構文になります:
  ActiveSheetの[A4]セルに、
   ActiveSheetのドロップダウン"myCombo"のいま選択されている
   行(.ListIndex) の内容(.List) を
  代入せよ
という意味になります。

日本語に置き換えてみれば、そんなに難しくないですよね

>  .Range("K4").Value = ActiveSheet.DropDowns("myCombo").ListIndex
> 3)  Range("K4").Value には選択された項目の順番(2番目なら2が)
>  書かれ 項目名を書きたいのですが 


実を言いますと、選択された項目番号.ListIndexは
>     .LinkedCell = "$B$4"
で設定しています。選択されたときの.ListIndexが何番か?は
 .LinkedCell に指定したセルの値を見ればいいのです。

 .LinkedCellプロパティを使った 選択アイテムの取得方法は
 .LinkedCell が [B4]のとき、
>  n = [B4].Value
>    [A4].Value = Excel.Range(DropDowns("myCombo") _
>           .ListFillRange).Item(n).Value
となることを以前、紹介しましたよね?

【67570】Re:ActiveSheetにあるCombobxの値
お礼  ichi  - 10/12/14(火) 14:15 -

引用なし
パスワード
   kanabun さん 毎回ご丁寧な回答有難うございます
再度 挑戦してみます
また、問題が出ましたら
よろしくお願いいたします

2517 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free