リストボックスのクリックイベントについて

いつもお世話になっております。

初心者です。

リストボックスのダブルクリックイベントから他のシートを開くことは難しいでしょうか？

A列にシート名の一覧を表示するマクロを組んでいます。
そしてそのシートの特定のセルをB~F列に反映させています。

A列 　　 B列 　　 C列 　　 D列 　　 E列 　　 F列
sheet1 りんご　 すいか　 メロン　　 バナナ　　みかん
sheet2 苺　　　 梨

検索のマクロを組み、リストボックスに検索結果を表示させることはできました。
「すいか」と検索をかければリストボックスに「すいか」が表示されます。
そこから、リストボックスの「すいか」をダブルクリックしてA列にあるシート名のシート（sheet1)に飛びたいのですが、可能でしょうか？

最初はユーザーフォームを使うつもりがなくハイパーリンクを貼っていました。
（=IFERROR(HYPERLINK("#"&A1&"!A1",),"INDIRECT($A1&"!E80")"))

他の者からユーザーフォームを使ってほしいとの要望があり、作成しているのですが、
私一人の力ではできそうもなく…

お力を貸していただけると幸いです。宜しくお願い致します。

No.5 回答者： WindFaller 回答日時： 2015/08/18 14:49

#4の回答者です。

>ImageListというものを知らなかったので再度どういった形がいいのか検討してみます！

昨日、画像は取っておいたけれども、アップロードしてありませんでしたので、本日上げておきます。とりあえず、今のものを完成させていくのが一番だと思います。
UserForm も作り方によっては、思った以上になります。

No.4 回答者： WindFaller 回答日時： 2015/08/17 22:13

#2の回答者です。

実際の所、ご質問者さんの提示された文章では、そのままVBAマクロには出来上がりません。UserForm のListBox がどのようになっているとか、分からないままに、こちらは、自分勝手な判断の元にマクロコードにしただけです。ただ、そういう状況は、決して好ましい状態であるわけではありません。

したがって、#2で書かれたものが、そのままご質問者さんの状況に適用できるはずもありません。それだけは、あらかじめ、分かっておいてください。得てして、私はこういう時、質問者さんの思っているものに近づけようとして、大半は、裏目にでることが多いのです。

ListIndex というのは、あくまでも、0,1,2,3,4,...と続く数字です。ListBox の列を増して使えばよいという考えもあろうとは思いますが、あまり見栄えがよくありません。ImageList側はアドレス等を埋め込むことはできますが、あまり数が増えたりすると、また見栄えがよくありません。

>行番号が取得できればできそうなのですが、リストボックスの行番号ではなく、シートのセルのアドレスを取得する方法はありますでしょうか？

配列変数側にアドレス
Listbox のItem に、名称を入れる
－ListIndex と、配列変数とは、添字で、つながりをもたせます。

後は、どの程度、理解していただけるかによって違ってくると思います。

この回答へのお礼

ありがとうございます！
ImageListというものを知らなかったので再度どういった形がいいのか検討してみます！

お礼日時：2015/08/18 09:33

No.3 回答者： hallo-2007 回答日時： 2015/08/17 19:25

No1です。

ん～
シートを移動する為に、わざわざユーザーフォーム起動して
検索して、ユーザーフォーム閉じて
希望したシートへ自動で移動させる。

あまり、かっこいいとも思えないのですが

別案です。
シートにあるシートイベントをご存知ですが。一例です。
シート名のタブを右クリックして
コードの表示を選択してVBエディターを起動
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Taregt.Address="$A$1" Then
Sheets(Range("A1").Value).Activate
End　If
End Sub
を張り付けて閉じると
A1セルを　ダブルクリックすると、A1セルに表示されているシートへ移動します。
チェンジイベントを使うと
Private Sub Worksheet_Change(ByVal Target As Range)
If Taregt.Address="$E$80" Then
Sheets(Range("A1").Value).Activate
End　If
End Sub

とかして、E80セルに検索する値を入れて
その結果で移動する事も可能です。

もし、ユーザーフォームでかっこよく作業をしたいなら
シートを移動した後の作業まで、ユーザーフォームで出来るようにします。

この回答へのお礼

ありがとうございます！
シートの移動の他にもシートの追加などの他の機能も一応つけてあります。
確かに入力までユーザーフォームでできたらかっこいいですよね。
でも私の技術ではそこまでできそうにありませんT_T
参考にさせていただいて頑張ってみます！

お礼日時：2015/08/18 09:27

No.2 回答者： WindFaller 回答日時： 2015/08/17 16:21

こんにちは。

お話のようすからすると、どうも、Listbox ではなくて、ImageList のような気がします。
まあ、それは、その場に適したものを使えればどちらでも良い話ですが。

>検索のマクロを組み、リストボックスに検索結果を表示させることはできました。
>「すいか」と検索をかければリストボックスに「すいか」が表示されます。
>そこから、リストボックスの「すいか」をダブルクリックしてA列にあるシート名のシート（sheet1)に飛びたいのですが、可能でしょうか？

Listbox に、検索結果といっても、検索したものは、ひとつしかないものなら、ListBox に出す意味がないような気がします。

こちらが理解した範囲で、やってみました。
なお、UserFormを出しっぱなしでワークシートの作業をする時、UserFormのモードレスモード以外の方法としては高等技術ですが、方法はあります。


>A列 　　 B列 　　 C列 　　 D列 　　 E列 　　 F列
>sheet1 りんご　 すいか　 メロン　　 バナナ　　みかん
>sheet2 苺　　　 梨

現在、以下のマクロは、この文字そのものを探しています。
例えば、Sheet1 とか、Sheet2 とか選択すると、それぞれのリストが出てくるということも考えられるのですが、質問の内容からは、それ以上は、読み取れませんでした。


'//ユーザーフォームモジュール

Private arSrch() As Variant
Private cnt As Long
Private Sub CommandButton1_Click()
　Dim srchTxt As Variant
　Dim c As Range
　Dim FoundAddress As String
　Dim ws As Worksheet
　If Me.TextBox1.Text = "" Then Exit Sub
　srchTxt = Me.TextBox1.Text
　For Each ws In ThisWorkbook.Worksheets
　　With ws.UsedRange
　　　Set c = .Find(What:=srchTxt, LookIn:=xlValues, LookAt:=xlWhole)
　　　If Not c Is Nothing Then
　　　　FoundAddress = c.Address(0, 0, , True)
　　　　Me.ListBox1.AddItem c.Value
　　　　TextBox1.Value = ""
　　　　ReDim Preserve arSrch(cnt)
　　　　arSrch(cnt) = FoundAddress
　　　　cnt = cnt + 1
　　　　Exit For
　　　End If
　　End With
　Next ws
　If c Is Nothing Then
　　MsgBox srchTxt & "は見つかりませんでした。", vbExclamation
　　TextBox1.Value = ""
　Else
　　Set c = Nothing
　End If
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
　Dim i As Long
　Dim adr As String
　Dim dst As String
　Cancel = True
　i = ListBox1.ListIndex
　adr = arSrch(i)
　dst = Replace(adr, "[" & ActiveWorkbook.Name & "]", "")
　If dst <> "" Then
　If MsgBox(ListBox1.Value & "に飛びます。", vbOKCancel) = vbCancel Then Exit Sub
　　Application.Goto Range(dst)
　End If
End Sub
'///

'// '標準モジュール等(モードレスにて、UserForm起動)
Sub MyUserForm_Start()
　UserForm1.Show vbModeless　'モードレスモード
End Sub
'///

この回答へのお礼

ありがとうございます！

素人なため解読に時間がかかりそうなので、取り急ぎお礼を。
明日やってみます！
こんなに考えていただけるなんて本当に感謝です。

お礼日時：2015/08/17 16:41

No.1 回答者： hallo-2007 回答日時： 2015/08/17 15:09

>リストボックスのダブルクリックイベントから他のシートを開くことは難しいでしょうか？

>A列にあるシート名のシート（sheet1)に飛びたいのですが
ユーザーフォームが開いた状態ですよね。
ユーザーフォームが開いている間は、シートの操作は出来ないですよね。
つまり無理ですし、出来たとしてもシート上にユーザーフォームがあるので使いにくいだけでしょう。

＞他の者からユーザーフォームを使ってほしいとの要望があり
ユーザーフォームを使って何かをしてほしい、改善してほしい
わけですよね。
シート上でセルを直接操作したが、使いにくい？間違ったセルを操作してしまう？
もう少し、目的をはっきりさせた方が良いと思います。

この回答へのお礼

ありがとうございます。

目的は…かっこよくしたい、との事です…
私も式が壊されるのは困るのでできればユーザーフォームに切り替えたいところです。

検索をかけず、一覧で表示する場合は何とかできました。
listindexで行番号を取得しセルに書き出してoffset関数でシート名があるセルを拾って飛ぶことができました。
でも検索をかけると
りんご
すいか
メロン
と縦に表示されてしまい、listindexとエクセルのシートの行番号が一致せず、できませんでした。
行番号が取得できればできそうなのですが、リストボックスの行番号ではなく、シートのセルのアドレスを取得する方法はありますでしょうか？

お礼日時：2015/08/17 15:46