こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2006-08-31 13:19:51

QNo.2374388

困ってます

質問者が選んだベストアンサー

No4、No6です。

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

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

投稿日時 - 2006-08-31 15:28:41

お礼

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

投稿日時 - 2006-08-31 15:49:59

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

こんにちは。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 16:12:49

お礼

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

投稿日時 - 2006-08-31 19:21:24

ANo.6

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

ではいかが?

投稿日時 - 2006-08-31 15:16:21

お礼

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

投稿日時 - 2006-08-31 15:46:10

ANo.5

こんにちは。

イベント・ドリブン型のマクロです。シート2に設定するなら、シート2のシートをアクティブにすると、シート1側の書式が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 13:54:53

補足

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

投稿日時 - 2006-08-31 14:39:49

お礼

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

投稿日時 - 2006-08-31 14:15:18

ANo.4

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 13:53:24

補足

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

投稿日時 - 2006-08-31 14:36:31

お礼

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

投稿日時 - 2006-08-31 14:09:31

ANo.3

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

投稿日時 - 2006-08-31 13:45:24

お礼

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

投稿日時 - 2006-08-31 14:17:47

ANo.2

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

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

という手はあります。

投稿日時 - 2006-08-31 13:37:50

お礼

ありがとうございます。

投稿日時 - 2006-08-31 14:18:51

ANo.1

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

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

投稿日時 - 2006-08-31 13:26:06

お礼

ありがとうございます

投稿日時 - 2006-08-31 14:19:11

あなたにオススメの質問