Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
3686 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【60620】リストボックスで選択した名前をセルに入れたいんですが みどり 09/3/5(木) 12:51 質問[未読]
【60621】Re:リストボックスで選択した名前をセルに... kanabun 09/3/5(木) 13:18 発言[未読]
【60622】Re:リストボックスで選択した名前をセルに... みどり 09/3/5(木) 15:33 質問[未読]
【60623】Re:リストボックスで選択した名前をセルに... kanabun 09/3/5(木) 15:50 発言[未読]
【60625】Re:リストボックスで選択した名前をセルに... Jaka 09/3/5(木) 16:28 発言[未読]
【60626】Re:リストボックスで選択した名前をセルに... Jaka 09/3/5(木) 16:33 発言[未読]
【60636】Re:リストボックスで選択した名前をセルに... Jaka 09/3/6(金) 9:19 発言[未読]
【60653】Re:リストボックスで選択した名前をセルに... みどり 09/3/6(金) 15:47 質問[未読]
【60654】Re:リストボックスで選択した名前をセルに... Jaka 09/3/6(金) 16:22 発言[未読]
【60656】Re:リストボックスで選択した名前をセルに... みどり 09/3/6(金) 16:58 質問[未読]
【60690】Re:リストボックスで選択した名前をセルに... Jaka 09/3/9(月) 9:43 発言[未読]
【60696】Re:リストボックスで選択した名前をセルに... みどり 09/3/9(月) 16:41 質問[未読]
【60697】Re:リストボックスで選択した名前をセルに... Jaka 09/3/9(月) 17:30 発言[未読]
【60700】Re:リストボックスで選択した名前をセルに... みどり 09/3/9(月) 20:03 質問[未読]
【60706】Re:リストボックスで選択した名前をセルに... Jaka 09/3/10(火) 9:15 発言[未読]
【60709】Re:リストボックスで選択した名前をセルに... みどり 09/3/10(火) 11:34 お礼[未読]
【60713】Re:リストボックスで選択した名前をセルに... Jaka 09/3/10(火) 11:47 発言[未読]
【60714】Re:リストボックスで選択した名前をセルに... みどり 09/3/10(火) 14:41 お礼[未読]
【60620】リストボックスで選択した名前をセルに入...
質問  みどり  - 09/3/5(木) 12:51 -
引用なし
パスワード
   はじめまして、よろしくお願いします。

シート2のC2〜C1000のセルのどれを選択しても
ユーザーフォーム1が表示されるようにしてあります。
そのユーザーフォームにはリストボックスがあり
リストボックスにはシート1に登録されている営業担当者名が
表示されます。

上記シート2の範囲の任意のセルを選択してユーザーフォームの
リストボックスで選択した名前をシート2の選択されているセルに
入れたいのですが、またこの作業をC2〜C1000まで繰り返し行ないたい
のですが、初心者でぜんぜん分からなくて困っています。

どなたか教えていただけないでしょうか。
【60621】Re:リストボックスで選択した名前をセル...
発言  kanabun  - 09/3/5(木) 13:18 -
引用なし
パスワード
   ▼みどり さん：
>そのユーザーフォームにはリストボックスがあり
>リストボックスにはシート1に登録されている営業担当者名が
>表示されます。
>
>上記シート2の範囲の任意のセルを選択してユーザーフォームの
>リストボックスで選択した名前をシート2の選択されているセルに
>入れたいのですが、またこの作業をC2〜C1000まで繰り返し行ないたい
>のですが、初心者でぜんぜん分からなくて困っています。

　　ActiveCell.Value = ListBox1.Value

とかでしょうか？

別法で、
UserForm使わず、C2〜C1000 に入力規則をセットしておけば
マクロレスで行けますけど？

(1)シート1 の営業担当者名リスト範囲にあらかじめ 名前定義をしておく
　（リスト範囲を選択して Ctrl+[F3]　 名前はたとえば「担当者リスト」とする)
(2)シート2の [C2:C1000] を選択して、[データ]-[入力規則]
　 -[リスト] で 範囲を
　　=担当者リスト

とするわけです。
【60622】Re:リストボックスで選択した名前をセル...
質問  みどり  - 09/3/5(木) 15:33 -
引用なし
パスワード
   ▼kanabun さん：

kanabunさんアドバイス有難う御座います。
マクロレスでも良いのですが、営業担当者が
100名以上いますので、50音別にユーザーホームを
作ろうかと考えましたもので、このご質問を
させていただきました。

マクロレスで出来る方法でもいいので教えていただけませんか？
よろしくお願いします。
【60623】Re:リストボックスで選択した名前をセル...
発言  kanabun  - 09/3/5(木) 15:50 -
引用なし
パスワード
   ▼みどり さん：

>マクロレスでも良いのですが、営業担当者が
>100名以上いますので、50音別にユーザーホームを
>作ろうかと考えましたもので、このご質問を
>させていただきました。
>
>マクロレスで出来る方法でもいいので教えていただけませんか？

マクロレス（一般機能）でぼくが思いつく方法が「入力規則」なので
そのほかの一般機能はちょっとありません。
でも、100人ものリストなら、当初のUserForm案のほうがよさそうですね
入力規則ですと、フォントサイズとか、ドロップダウン▼ のサイズを
カスタマイズできないので。。。
リストボックスがいいか、ComboBoxが いいかは状況によりますけれど、
一長一短ありますので、比較検討してください。
【60625】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/5(木) 16:28 -
引用なし
パスワード
   リストの数が多いのでトロイ私のＰＣだとかなり遅いです。

該当シートモジュール

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Cel As Range
If Target.Column > 1 Then Exit Sub
Cancel = True
With Application.CommandBars.Add(Position:=msoBarPopup, temporary:=True)
　　For Each Cel In Range("F1:F120")　'営業担当者の入ったセル範囲
　　　　With .Controls.Add(Type:=msoControlButton)
　　　　　　.Caption = Cel.Value
　　　　　　.FaceId = 45
　　　　　　.OnAction = "マクロ1"
　　　　End With
　　Next
　　.ShowPopup
End With
End Su


標準モジュール

Sub マクロ1()
Dim BBt As CommandBarControl
Set BBt = Application.CommandBars.ActionControl
ActiveCell.Value = BBt.Caption
Set BBt = Nothing
End Sub

それと、営業担当者の入ったセル範囲は、あらかじめソートして置いてください。
【60626】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/5(木) 16:33 -
引用なし
パスワード
   >If Target.Column > 1 Then Exit Sub
これ消しておいて下さい。
アイコンなどは好きな番号に変えてください。
表示が遅いので使わないと思うけど。
【60636】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/6(金) 9:19 -
引用なし
パスワード
   なんか見てないみたいだけど、まあ良いや。

今までできないと思って、やったこと事がなかったのですが、
夜中にやってみたらできたので、中途半端なコードを載せます。
右クリックで起動します。

事前に用意して置いてください。

名簿シートをSheet2とします。
あ行の名簿を A1:A5
か行の名簿を B1:B5
さ行の名簿を C1:C5
た行の名簿を D1:D5
とします。

標準モジュールは前回と同じ。

シートモジュールだけ変更。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Cel As Range
'If Target.Column > 1 Then Exit Sub
Cancel = True
With Application.CommandBars.Add(Position:=msoBarPopup, temporary:=True)
　　With .Controls.Add(Type:=msoControlPopup)
　　　　 .Caption = "あ行"
　　　　 For Each Cel In Sheets("Sheet2").Range("A1:A5")　'営業担当者の入ったセル範囲、あ行
　　　　　　 With .Controls.Add(Type:=msoControlButton)
　　　　　　　　　.Caption = Cel.Value
　　　　　　　　　.FaceId = 80
　　　　　　　　　.OnAction = "マクロ1"
　　　　　　 End With
　　　　 Next
　　End With
　　With .Controls.Add(Type:=msoControlPopup)
　　　　 .Caption = "か行"
　　　　 For Each Cel In Sheets("Sheet2").Range("B1:B5")　'営業担当者の入ったセル範囲、か行
　　　　　　 With .Controls.Add(Type:=msoControlButton)
　　　　　　　　　.Caption = Cel.Value
　　　　　　　　　.FaceId = 90
　　　　　　　　　.OnAction = "マクロ1"
　　　　　　 End With
　　　　 Next
　　End With
　　With .Controls.Add(Type:=msoControlPopup)
　　　　 .Caption = "さ行"
　　　　 For Each Cel In Sheets("Sheet2").Range("C1:C5")　'営業担当者の入ったセル範囲、さ行
　　　　　　 With .Controls.Add(Type:=msoControlButton)
　　　　　　　　　.Caption = Cel.Value
　　　　　　　　　.FaceId = 98
　　　　　　　　　.OnAction = "マクロ1"
　　　　　　 End With
　　　　 Next
　　End With
　　With .Controls.Add(Type:=msoControlPopup)
　　　　 .Caption = "た行"
　　　　 For Each Cel In Sheets("Sheet2").Range("D1:D5")　'営業担当者の入ったセル範囲、た行
　　　　　　 With .Controls.Add(Type:=msoControlButton)
　　　　　　　　　.Caption = Cel.Value
　　　　　　　　　.FaceId = 99
　　　　　　　　　.OnAction = "マクロ1"
　　　　　　 End With
　　　　 Next
　　End With
　　.ShowPopup
End With
End Sub

追伸
前回、遅いと思っていたのは遅いＰＣのせいだったみたいです。
Pen4 1.6Gでやってみたら、ストレスなくあっさり表示されました。
【60653】Re:リストボックスで選択した名前をセル...
質問  みどり  - 09/3/6(金) 15:47 -
引用なし
パスワード
   Jaka さん、お返事が遅くなって申し訳御座いませんでした。
凄いです、ユーザーフォームを使わずに
出来るんですね。
有難う御座いました。
ただ、教えていただいた方法を私が理解できずに
うまく動かせなくて悪戦苦闘していましたのでごめんなさい。

もう一つ質問なんですが、教えていただいたものを繰り返し
行なうにはどうしたらいいのでしょうか？


>なんか見てないみたいだけど、まあ良いや。
>
>今までできないと思って、やったこと事がなかったのですが、
>夜中にやってみたらできたので、中途半端なコードを載せます。
>右クリックで起動します。
>
>事前に用意して置いてください。
>
>名簿シートをSheet2とします。
>あ行の名簿を A1:A5
>か行の名簿を B1:B5
>さ行の名簿を C1:C5
>た行の名簿を D1:D5
>とします。
>
>標準モジュールは前回と同じ。
>
>シートモジュールだけ変更。
>
>Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
>Dim Cel As Range
>'If Target.Column > 1 Then Exit Sub
>Cancel = True
>With Application.CommandBars.Add(Position:=msoBarPopup, temporary:=True)
>　　With .Controls.Add(Type:=msoControlPopup)
>　　　　 .Caption = "あ行"
>　　　　 For Each Cel In Sheets("Sheet2").Range("A1:A5")　'営業担当者の入ったセル範囲、あ行
>　　　　　　 With .Controls.Add(Type:=msoControlButton)
>　　　　　　　　　.Caption = Cel.Value
>　　　　　　　　　.FaceId = 80
>　　　　　　　　　.OnAction = "マクロ1"
>　　　　　　 End With
>　　　　 Next
>　　End With
>　　With .Controls.Add(Type:=msoControlPopup)
>　　　　 .Caption = "か行"
>　　　　 For Each Cel In Sheets("Sheet2").Range("B1:B5")　'営業担当者の入ったセル範囲、か行
>　　　　　　 With .Controls.Add(Type:=msoControlButton)
>　　　　　　　　　.Caption = Cel.Value
>　　　　　　　　　.FaceId = 90
>　　　　　　　　　.OnAction = "マクロ1"
>　　　　　　 End With
>　　　　 Next
>　　End With
>　　With .Controls.Add(Type:=msoControlPopup)
>　　　　 .Caption = "さ行"
>　　　　 For Each Cel In Sheets("Sheet2").Range("C1:C5")　'営業担当者の入ったセル範囲、さ行
>　　　　　　 With .Controls.Add(Type:=msoControlButton)
>　　　　　　　　　.Caption = Cel.Value
>　　　　　　　　　.FaceId = 98
>　　　　　　　　　.OnAction = "マクロ1"
>　　　　　　 End With
>　　　　 Next
>　　End With
>　　With .Controls.Add(Type:=msoControlPopup)
>　　　　 .Caption = "た行"
>　　　　 For Each Cel In Sheets("Sheet2").Range("D1:D5")　'営業担当者の入ったセル範囲、た行
>　　　　　　 With .Controls.Add(Type:=msoControlButton)
>　　　　　　　　　.Caption = Cel.Value
>　　　　　　　　　.FaceId = 99
>　　　　　　　　　.OnAction = "マクロ1"
>　　　　　　 End With
>　　　　 Next
>　　End With
>　　.ShowPopup
>End With
>End Sub
>
>追伸
>前回、遅いと思っていたのは遅いＰＣのせいだったみたいです。
>Pen4 1.6Gでやってみたら、ストレスなくあっさり表示されました。
【60654】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/6(金) 16:22 -
引用なし
パスワード
   繰返しの意味が解ってませんけど、こんな感じ。
たぶん。

ActiveCell.Value = BBt.Caption
　　↓
Range(ActiveCell.Address & ",C2:C10").Value = BBt.Caption
【60656】Re:リストボックスで選択した名前をセル...
質問  みどり  - 09/3/6(金) 16:58 -
引用なし
パスワード
   Jaka さん早速のお返事有難う御座います。
繰り返しの意味の説明不足で大変申し訳御座いませんでした。
下記のプロシャージャですと選択したセルに
営業担当者の名前を入れると全部の範囲が同じ担当者名になるんですよね。

私がしたかったのは、セルC5に営業担当者Aを入力した後
C6に営業担当者Bをその後範囲内の任意のセルにリストから
選択した担当者名を継続的に入力していきたかったのですが
どうしたらいいのか、ぜんぜん分からずご質問させていただきました。
宜しくお願いします。


>繰返しの意味が解ってませんけど、こんな感じ。
>たぶん。
>
>ActiveCell.Value = BBt.Caption
>　　↓
>Range(ActiveCell.Address & ",C2:C10").Value = BBt.Caption
【60690】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/9(月) 9:43 -
引用なし
パスワード
   こういうので良いんじゃないですか?

Dim Flg As Boolean

If ListBox1.ListIndex < 0 Then
　 Flg = True
End If
If ListBox2.ListIndex < 0 Then
　 Flg = True
End If
If ListBox3.ListIndex < 0 Then
　 Flg = True
End If
If ListBox4.ListIndex < 0 Then
　 Flg = True
End If

If Flg = True Then
　 MsgBox "選択されていないリストボックスがあります", vbExclamation
　 Exit Sub
End If

Range("A1").Value = ListBox1.List(ListBox1.ListIndex)
Range("A2").Value = ListBox2.List(ListBox2.ListIndex)
Range("A3").Value = ListBox3.List(ListBox1.ListIndex)
Range("A4").Value = ListBox4.List(ListBox1.ListIndex)
【60696】Re:リストボックスで選択した名前をセル...
質問  みどり  - 09/3/9(月) 16:41 -
引用なし
パスワード
   Jaka さん、お返事有難う御座います。

前回の質問で教えていただいたVBAがいちばん理想的な作業が出来
ありがたく使わせていただきたいと考えています。
しかし、下記の内容ですと、選択してないリストボックスが有るから
繰り返せないということじゃないんでしょうか？
私がお伺いしたかった内容の説明不足で何度も申し訳御座いませんが
前回教えていただいたVBAを使って仮にC10に営業担当者をAAAさんと
入力して次にC11（C10以外のセル）を選択してもリストが表示されない
ので営業担当者を入力できません。
一度エクセルを閉じてもう一度立ち上げるとまたもう一人は選択して
入力が出来るのですが、1000回近くもエクセルを閉じたり開いたりは
ちょっと苦しいとおもいますので
この作業をC2〜C1000にほとんどのセルで（たまに空白もありますが）
エクセルを開いたまま繰り返し作業をしたいのですが分かって
いただけたでしょうか？
是非、お力をお貸しください。
宜しくお願いします。


>こういうので良いんじゃないですか?
>
>Dim Flg As Boolean
>
>If ListBox1.ListIndex < 0 Then
>　 Flg = True
>End If
>If ListBox2.ListIndex < 0 Then
>　 Flg = True
>End If
>If ListBox3.ListIndex < 0 Then
>　 Flg = True
>End If
>If ListBox4.ListIndex < 0 Then
>　 Flg = True
>End If
>
>If Flg = True Then
>　 MsgBox "選択されていないリストボックスがあります", vbExclamation
>　 Exit Sub
>End If
>
>Range("A1").Value = ListBox1.List(ListBox1.ListIndex)
>Range("A2").Value = ListBox2.List(ListBox2.ListIndex)
>Range("A3").Value = ListBox3.List(ListBox1.ListIndex)
>Range("A4").Value = ListBox4.List(ListBox1.ListIndex)
【60697】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/9(月) 17:30 -
引用なし
パスワード
   　まだ良く解っていませんが。
>前回教えていただいたVBA
右クリックのポップアップメニューのことでしょうか?

もしそうなら、
>入力して次にC11（C10以外のセル）を選択してもリストが表示されない
>ので営業担当者を入力できません。
こうはなりません。

こういうのは、今組み込んでいるブックで試さないで
新規ブックでその部分だけで試してください。
他にマクロが書いてあると何をされるのかわからないから....。

多分、シートのイベントを中断したままにしているからだと思いますが、
処理が終わったら戻してやればいいです。
【60700】Re:リストボックスで選択した名前をセル...
質問  みどり  - 09/3/9(月) 20:03 -
引用なし
パスワード
   Jaka さん、すいませんでした。
新しいブックに教えていただいたVBAを記入して
試していたのですが、

If Intersect(Target, Range("C2:C1000")) Is Nothing Then Exit Sub

Application.EnableEvents = False

C2〜C1000だけでこの作業をしたい為、上記の記述を入れていました
この部分を削除したらどのセルを選択してもポップアップが出るようになりました
すいませんでした。

そこで、今度は新たな問題が発生してきたのですが
C2〜C1000以外のセルには違うデータを入力しなくてはなりません
セルをマウスで選択しても→キーで移動しても全てのセルで
ポップアップが表示されます。
上記範囲以外では出ないようには出来ないのでしょうか？
何度も質問して申し訳ございませんが、もう一度
教えていただけないでしょうか？
【60706】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/10(火) 9:15 -
引用なし
パスワード
   ▼みどり さん：
>If Intersect(Target, Range("C2:C1000")) Is Nothing Then Exit Sub
>
>Application.EnableEvents = False　　←不要なのはこの部分だけです。

ひょっとしたら、最初の質問はフォームをモードレスにすればすむ問題だったの
ではないでしょうか？
こうすれば、フォームを表示したままセルを選択できます。

UserForm1.Show vbModeless
【60709】Re:リストボックスで選択した名前をセル...
お礼  みどり  - 09/3/10(火) 11:34 -
引用なし
パスワード
   Jaka さん、こんにちは。
いろいろお世話になり有難うございました。
無事、考えていた作業ができるようになりました。
いえ、もともとわたしが考えていた以上の
事を教えていただき、有難う御座いました。
今後も、自分なりに知識を広げて行きたいと考えております
分からない事の方が多いと思いますので
その節には質問させていただきますので、宜しくお願いします。
本当に有難う御座いました。


【追伸】

これは、今後の向学のためにお聞きしたいのですが

For Each Cel In Sheets("Sheet2").Range("A1:A5")　'営業担当者の入ったセル範囲、あ行

教えていただいたこの部分がポップアップリストの表示範囲になると
思うのですが、人数が５人以上に増えればリストに新規追加をして
（"A1:A5")を変更すればいいと言うところまでは理解できました。
このとき担当者の人数が５人以上になっても自動でポップアップに
表示されるようには出来ないのでしょうか？
いろいろ、.End(xlUp)とかで出来ないか調べながら
やってみたのですが、コンパイルエラーが出たり
いろんなエラーになるだけで、理解していないと無理ですね。
【60713】Re:リストボックスで選択した名前をセル...
発言  Jaka  - 09/3/10(火) 11:47 -
引用なし
パスワード
   こんな感じ。

For Each Cel In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp))
【60714】Re:リストボックスで選択した名前をセル...
お礼  みどり  - 09/3/10(火) 14:41 -
引用なし
パスワード
   Jaka さん有難う御座います。
どうして、こんな難しいことが簡単に出来るのか
羨ましく思います。
わたしにはいつまでたっても理解できないかも分かりませんが
頑張って勉強してみたくなりました
今後も質問させて頂くと思いますので
どうぞ宜しくお願いします。


>こんな感じ。
>
>For Each Cel In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp))
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
3686 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free