別シートの文字をクリックして表示させたい

質問

仕事で取引先に送付する文書があるのでが、その文書作成をすばやく行えるようにしてほしいと要望がありました。

取引文書.xlsにはシートが2つあり、シート1は取引先に提出する文書で、宛先と担当者氏名の箇所が空白になっています。

シート2には取引先の名前、相手の氏名が一覧になっています。

今までは空白箇所に名前をコピーアンドペーストないし手入力していたのですが、使用者から、
一覧表からクリックするだけで名前を飛ばせるようにしてほしいと要望がありました。

シート1の空白箇所をクリックしたらシート2に移動し、シート2の名前をクリックしたらその名前が空白箇所に表示される、ということのようです。

リンク自体はできたのですが、リンク先の文字を空白箇所に表示させることができません。

取引先は200以上あり、そのすべてをリストボックスにすると見づらく検索しにくいので、リストボックスは不可だとのことです。

VBAでしょうか？

可能ならば、早めに教えていただけると非常に助かります。

大変もうしわけありませんが、教えていただけませんでしょうか？

回答No.1

tom04 · ベストアンサー

こんにちは！
具体的な表のレイアウトが判らないので、やり方だけ・・・

＞シート1の空白箇所をクリックしたらシート2に移動し、シート2の名前をクリックしたら
＞その名前が空白箇所に表示される

というコトですが、
＞ダブルクリックしたら！
という方法の一例です。

↓の画像のような配置だとします。
左側がSheet1でA2セルに「顧客名」・C2セルに「担当者名」を入れるための「空白セル」だとします。
右側のSheet2には「顧客名」と「担当者名」の表がA・B列に作成済みだとします。

それぞれのSheetにダブルクリックで実行されるイベントプロシージャ作ってみます。
まず画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてみてください。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'この行から
If Application.Intersect(Target, Range("A2,C2")) Is Nothing Then Exit Sub
Cancel = True
With Worksheets("Sheet2") '←「Sheet2」は実際のSheet名に！
If Target.Address = "$A$2" Then
.Activate
.Range("A1").Select '←Sheet2の顧客名列の1行目を選択
Else
.Activate
.Range("B1").Select '←Sheet2の担当者名列の1行目を選択
End If
End With
End Sub 'この行まで

次に同様にSheet2のSheet見出し上で右クリック　→　・・・中略・・・　コピー＆ペースト

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'この行から
Dim str As String
If Application.Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
Cancel = True
str = Target
Worksheets("Sheet1").Activate '←「Sheet1」は実際のSheet名に！
If Target.Column = 1 Then
ActiveSheet.Range("A2") = str '←A2セルはSheet1の記入したいセル番地に！
Else
ActiveSheet.Range("C2") = str '←C2セルはSheet1の記入したいセル番地に！
End If
End Sub 'この行まで

Sheet1の空白セルをダブルクリック　→　Sheet2の入力したいセルをダブルクリック
これでSheet1の空白セルにSheet2のデータが表示されると思います。

※　画像の配置はあくまで判りやすくするための配置ですので、実状のデータにあわせて
Sheet名・セル番地等の変更が必要です。m（_ _）m

お礼

難しくて自分ひとりではとてもできそうになかったので本当に困っていました……
無事に解決することができました！
本当にありがとうございました！
心から感謝します！

回答No.6

shorun

No5です
訂正します。
4.Sheet1の宛先に次の式を入力する。　=VLOOKUP(Sheet2!$A$1,Sheet2!$A$1:$C$4,3)
の宛先は誤っていました
正しくは、担当者氏名でした。

お礼

補足していただいてありがとうございます。
こちらで修正して使用させていただきます。
心から感謝申し上げます。

回答No.5

shorun

次の条件なら
1.VBA不可。　(取引先が拒否または、自社担当が移動するとメンテ困難など)
2.取引先に送付するのは、「印刷した紙の書類」であり、取引文書.xlsファイルではない。
3.Sheet2の加工が許される。

提案
Sheet2のデザインが記載されていないので、添付図青色部分の一覧表がある場合の例です。
1.先頭行を固定する。(1行目を表示したまま200行目まで検索できるようにするため)
2.A列を新しく挿入して2行目から、200以上のNoを入力する。
3.Sheet1の宛先に次の式を入力する。　=VLOOKUP(Sheet2!$A$1,Sheet2!$A$1:$C$4,2)
4.Sheet1の宛先に次の式を入力する。　=VLOOKUP(Sheet2!$A$1,Sheet2!$A$1:$C$4,3)
5.Sheet2の[A1]セルに該当するNoを入力する。
6.Sheet1の所定のセルに、取引先の名前と相手の氏名が表示されます。

参考、
実際のSheet2のデザインに合わせて、上記式3と4は修正してください。

お礼

VBA不可のVer,本当にありがとうございます。
実際のところ、1から3まで当てはまるんですよ。
試してみて、どういう形がベストかまた相談の上決定になると思います。
それにしても、いろんな形で再現できるんですね。

自分ひとりではまったくできなかったので、関心することしきりです。

本当にありがとうございました！

回答No.4

MSZ006

かなり手抜きですが・・・。
こちらはシングルクリックで、選択して行く方法になります。
sheet1のB1セルもしくはB2セルをクリックするとsheet2に切り替わり、セルをクリックするとsheet1のセルに入力されます。

（１）標準モジュールに下記を入力。

Public pos As Long

（２）sheet1に下記を入力。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'会社名の入るセル：B1
'担当者名の入るセル：B2
pos = 0
If ActiveCell.Address = "$B$1" Then
pos = 1
Worksheets("Sheet2").Activate
End If
If ActiveCell.Address = "$B$2" Then
pos = 2
Worksheets("Sheet2").Activate
End If
End Sub

（３）sheet2に下記を入力

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If pos = 1 Then
Worksheets("sheet1").Range("$B$1").Value = ActiveCell.Value
End If
If pos = 2 Then
Worksheets("sheet1").Range("$B$2").Value = ActiveCell.Value
End If
Worksheets("Sheet1").Activate
End Sub

お礼

いろいろな形があるんですね。
シングルクリックで以降できる形、となると一手間はぶけますし、また話をあげてみます。

こういった構文を深く知れば、エクセルで実現したいと思ったことはほとんどできてしまうんでしょうね。

職業上、エクセルの文書類を作成することが非常に多いので、教えてもらいながらも勉強指定校と思います。

ありがとうございました！

回答No.3

MarcoRossiItaly

200 件以上あるとのことですから、できればそういうデータは、通し番号などの記号で管理しておくのが望ましいです。「1 あるいは 0 から始まる連続した整数」が便利だと思うのですが、一意な記号なら何でもいいです。

記号があれば、それを「シート1」のどこかのセルに入力するだけで、社名や担当者名を「シート2」から拾ってきて「シート1」に自動的に表示させるということは、簡単にできますよね。VLOOKUP 関数などを、表示させたいセルに始めから記入しておけばいいだけです（記号が未入力のときは「#N/A」を表示してくれます）。

添付図では、次式。

シート1のB3　=vlookup($B$1,シート2!$D$2:$F$6,2,)

文字列を別シートから一つひとつコピペという方法だと、件数が多いときは、そもそもミスのもとですよね。少なければそれで問題ないですけどね。

記号の入力は、手入力であっても通し番号だけなら非常に大変ということはないですし、それこそ VBA で、入力から印刷まで一気に行ってもいいですね。一定の番号の範囲（全件など）について PDF なりに印刷し、途中に不要なページが含まれていれば、印刷後に捨ててしまえばいい。という感じかと思うのですが、いかがでしょうか？そのほうがコードも運用も、遥かにシンプルです。

もしも記号が表示されていない状態でページを印刷したいという場合は、印刷範囲から外したり、白色の文字に変更したり…といったことは、幾らでもできます。

どうしてもダブルクリックで名前の入力を…というシステムを考えるなら、No.1 さんが教えてくださっているようなコードを用意せざるを得ないかと思います。

お礼

いろいろなご意見が伺えて助かります。

今のところはまだ仮決定の段階ということですので、いろいろと試してみるつもりです。

エクセルは奥深くて便利ですけど難しいですね。

ありがとうございました！

回答No.2

tsubuyuki

その「行為」自体は＃１さんの回答で出来ると思います。

しかし。
おそらく、と言うか、ほぼ確実にＶＢＡです。

これ、相手に了承を取っておかないといけないかもしれませんよ。
ご質問の内容からおそらく大丈夫とは思いますが、
会社によっては「ＶＢＡ不可！」とおっしゃるところもありますから。

ついでに言ってしまうと、エクセルの通常の設定では
マクロを含むブックを開く時には警告（と言うか確認）が表示されます。
この確認を了承することで初めてマクロ（ＶＢＡ）が活きてきます。
（回避する策はいくつかありますが、これはお相手の事情によりますから割愛。）

「この一手間が惜しい」と言う人がいるのもまた事実です。
（現に「会社名・担当者名を打つのが面倒」とおっしゃる方のようですし。）
ですので、お相手との確認・了承は取っておいた方が良いです。

以上、直接の回答ではありませんが、老婆心による忠告とお納めくださいませ。
失礼いたしました。

お礼

ご忠告ありがとうございます。
確かにマクロが云々の表示がでてきますね。
とりあえず、問題はないとの上司の言葉に安心していますが、なんだかそれもどうにかしてほしいとの言葉が出てきたら困ってしまいますね。

また相談してみます。
ご意見ありがとうございました。