常に参照もとのセルの書式に従う〔Excel〕

例えば、sheet2のA1セルがsheet1のA1セルを参照していたとします（単にsheet2のA1セルが「=sheet1!A1」となっているだけです）。このときに、sheet2のA1の書式設定を参照元であるsheet1のA1セルの書式に常に一致させたいのですが、何か良い方法はないでしょうか？
具体的にいうと、sheet1のA1セルの表示形式が円単位になったり、千円単位になったり、％表示になったりと変化するのですが、その度にsheet2のA1セルの書式も手作業で変えるのが面倒です。表示単位はその都度カスタマイズすることもあるのでマクロで登録することにも無理があります。
それか、VBAで参照もとの書式の情報を返して、それを参照先セルに反映させることができますでしょうか？
できれば、何もせずフルオートで対応できるとありがたいのですが。

No.7 ベストアンサー 回答者： merlionXX 回答日時： 2006/08/31 15:28

No4、No6です。

> いつもは標準モジュールに記述しているのですが、sheetに記述するとどう違うのでしょうか？

この回答が抜けてましたね。
Sheetのモジュールに記載したのは、そのSheetに対するイベント（シートが選択された、セルが選択された、入力された、ダブルクリックされた等）を判断するためです。
今回はPrivate Sub Worksheet_Activateですから、Sheet2がアクティブになったというイベントです。
そのイベントが起きて初めて記述したマクロが自動的に作動するのです。これをNo5さんがお書きになったように「イベント・ドリブン型のマクロ」といいます。

この回答へのお礼

イベント・ドリブン型のマクロ・・・。覚えておきます。
自分で書けるようになるまでにはもう少し時間が必要ですが、いつか挑戦してみます。とりあえず今回はコピーペーストで対応します。
私の周りにもここまでVBAができる人がいればよいのですが・・・
大変助かりました。

お礼日時：2006/08/31 15:49

No.8 回答者： Wendy02 回答日時： 2006/08/31 16:12

こんにちは。

Wendy02です。

罫線やパターンを除く書式コピー
>現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。

'シートモジュール
Private Sub Worksheet_Activate()
　'シートをアクティブにすると書式が設定させる
　Dim rng1 As Range
　Dim rng2 As Range
　Dim ar As Range
　Application.ScreenUpdating = False
　On Error Resume Next
　With Worksheets("Sheet1").UsedRange
　　On Error Resume Next
　　Set rng1 = .SpecialCells(xlCellTypeFormulas, 3) '定数-文字,数字
　　Set rng2 = .SpecialCells(xlCellTypeConstants, 3)　'数式-文字,数字
　　If rng1 Is Nothing And rng2 Is Nothing Then Exit Sub　'設定させるものがない
　　If rng1 Is Nothing Then　'定数がない場合
　　　Set rng = rng2
　　ElseIf rng2 Is Nothing Then　'数式がない
　　　Set rng = rng1
　　Else　'両方ともある
　　　Set rng = Union(rng1, rng2)
　　End If
　　Set rng1 = Nothing: Set rng2 = Nothing
　End With
　On Error GoTo 0
　For Each ar In rng
　　ActiveSheet.Range(ar.Address).NumberFormatLocal = ar.NumberFormatLocal
　Next
　Application.ScreenUpdating = True
　Set rng = Nothing
End Sub



>「Private」とプロシージャの先頭に記述して標準モジュールとしてではなくsheetコードとして入力することでフルオート実行なっているのでしょうか？

すでに、merlionXXさんから、ご説明されていますが、標準モジュールは、個別のオブジェクト（シートやブック)を持たない、つまり、[反応を受けるための器]がないので、フルオートには出来ません。個別のオブジェクトを持つと、自動的なマクロ（イベント・ドリブン型)にすることが出来ます。しかし、あくまでも、他から設定されないようにするために、Private というキーワードが必要になります。このキーワードをつけることで、[反応を受けるための器]は、そのシートのみで設定されることになります。

厳密に言うと、もう少し複雑になります。

この回答へのお礼

あれから、記述内容も検討してみたのですが、段々わかってきました。標準モジュールとシートへの記述の違いもだいたいわかりました。ありがとうございます。
（わざわざ、記述中に「'」をつかって注釈までつけて頂きありがとうございます）

お礼日時：2006/08/31 19:21

No.6 回答者： merlionXX 回答日時： 2006/08/31 15:16

No4です。

では、値と表示形式のみ持ってきますね。

Private Sub Worksheet_Activate()
With Sheets("Sheet2").Range("A1")
.Value = Sheets("Sheet1").Range("A1").Value
.NumberFormatLocal = Sheets("Sheet1").Range("A1").NumberFormatLocal
End With
End Sub

ではいかが？

この回答へのお礼

ありがとうございます。即解決です。
この辺まで自由自在にVBAの記述ができるとすごいですね！私の場合、マクロ的な記述は卒業しましたが、ここまで自由自在にはかけません。さすがです！

お礼日時：2006/08/31 15:46

No.5 回答者： Wendy02 回答日時： 2006/08/31 13:54

こんにちは。

イベント・ドリブン型のマクロです。シート2に設定するなら、シート2のシートをアクティブにすると、シート1側の書式が１瞬でコピーされます。

シート2 のシートタブを右クリックして、「コードの表示」で、出てきた白い画面に以下を貼り付けて、Alt + Q でVisual Basic Editor 画面を締めます。後は、シート1を開き、もう一度、シート2を開けば、設定されます。

Private Sub Worksheet_Activate()
'シートをアクティブにすると書式が設定させる
　Dim r As Range
　Application.ScreenUpdating = False
　Set r = ActiveCell
　Worksheets("Sheet1").UsedRange.Copy
　ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
　r.Select: Set r = Nothing
　Application.CutCopyMode = False
　Application.ScreenUpdating = True
End Sub


これで出来ているかと思います。

この回答への補足

教えていただいた記述ですと、書式情報の全てを取り込んでしまいますが、もし表示形式に関するもののみ（円や％など）の取り込みが可能でしたらアドバイスいただけないでしょうか？
現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。

補足日時：2006/08/31 14:39

この回答へのお礼

こちらもすごい！です。
一発解決です！
「Private」とプロシージャの先頭に記述して標準モジュールとしてではなくsheetコードとして入力することでフルオート実行なっているのでしょうか？

お礼日時：2006/08/31 14:15

No.4 回答者： merlionXX 回答日時： 2006/08/31 13:53

VBAです。

試してみてください。
何もせずフルオートで対応できますよ。
sheet2のタブを右クリックし、コードの表示。
以下をコピペ。

Private Sub Worksheet_Activate()
With Application
.EnableEvents = False
Worksheets("Sheet1").Range("A1").Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.EnableEvents = True
End With
End Sub

この回答への補足

教えていただいた記述ですと、書式情報の全てを取り込んでしまいますが、もし表示形式に関するもののみ（円や％など）の取り込みが可能でしたらアドバイスいただけないでしょうか？
現在、参照もとの罫線やセルの背景色まで取り込まれてしまいました。

補足日時：2006/08/31 14:36

この回答へのお礼

すごいですね！一発解決です。
ありがとうございます。
VBAは使用しているのですが、まだまだ使いこなせていません。いつもは標準モジュールに記述しているのですが、sheetに記述するとどう違うのでしょうか？
標準モジュールに記述するとどのシートでも実行できるというのはわかったのですが、本当の使い分けが良くわかっていません。
今回のようにsheetに記述すると、常にフルオート実行ということなのでしょうか？

お礼日時：2006/08/31 14:09

No.3 回答者： moon00 回答日時： 2006/08/31 13:45

＃2さんの回答にプラスアルファ。

Sheet2のA1セルの値を他で使いたい、ということであれば、
普通に「=sheet1!A1」としておいて、そのセルの上にさらに#2さんの方法で
図のリンク貼り付けをする、という方法があります。
下のセルが透けて見える場合は、図の書式設定で
「色と線」の「色」を自動にしてみてください。
（でも、結局Sheet2のA1セルの値ってことは、Sheet1のA1セルの値なので、
数式につかうなら、そちらを使えばいい話ですけど。）

この回答へのお礼

ありがとうございます。やはり手作業が必要ですね。

お礼日時：2006/08/31 14:17

No.2 回答者： himadakarane 回答日時： 2006/08/31 13:37

Sheet2のA1セルが表示にしか使われないのであれば、

・sheet1のA1セルをコピー
・Sheet2のA1セルを選択
・Shift押しながらメニュー「編集」→「図のリンク貼り付け」

という手はあります。

この回答へのお礼

ありがとうございます。

お礼日時：2006/08/31 14:18

No.1 回答者： CaveatEmptor 回答日時： 2006/08/31 13:26

関数では値のみしか参照できないと思います。

書式までをフルオートでということなら、値が変わったときに、マクロでセルごとコピーするという手がありますが、それではダメでしょうか？

この回答へのお礼

ありがとうございます

お礼日時：2006/08/31 14:19