Excelでコメントを多用する場面はありませんか？
普通に表の行追加や削除、行列サイズ変更、グループ化と解除、フィルタをかけた時などにコメントが表示状態だと、線がビヨ～～ンと伸びてて、とんでもない位置にコメントがあったりすることありませんか？
そんな時、その位置を調整する作業ってとんでもなくめんどくさいですよね。
すべてのコメントを綺麗に調整する簡単なマクロを作成してみました。
百聞は一見に如かず、一目瞭然、一発でイメージが湧き、使い方がわかるようなGIFアニメ付きです。
ぜひ、コピーしてご活用ください。
行追加・削除やフィルタ、グループ化でコメントが崩れまくる
私は仕事でコメントを多用しています。自分だけがコメントを入れるわけではないので、気が付いたらとんでもないことになっていることがよくあります。
まずは、コメントが崩れてしまう操作パターンを洗い出して一通り実験してみました。
上記画像からもわかるとおり、位置関係の設定がどんな状態かわからない崩れまくったすべてのコメントを整理するのは、かなりの苦行ですよね。
試した操作パターン
- 行のグループ化解除(行の非表示)
- 列のグループ化解除(列の非表示)
- 行のフィルタ
- 行のサイズ変更
- 列のサイズ変更
- 行の挿入
- 列の挿入 ⇒一部のパターンで元のコメント幅に戻らなくなった！！(壊れた)
- 行の削除
- 列の削除 ⇒一部のパターンで元のコメント幅に戻らなくなった！！(壊れた)
[コメントの書式設定]-[プロパティ]-[オブジェクトの位置関係]
①セルに合わせて移動やサイズ変更をする
②セルに合わせて移動するがサイズ変更はしない
③セルに合わせて移動やサイズ変更をしない
上記３パターン、いずれの設定にしても操作次第で位置がズレてしまいますが、何となく②が一番マシな気がします。感覚的にですが。
行追加や削除、行列サイズ変更、グループ化と解除、フィルタをかける操作は普通に当たり前のように行うので コメントがズレないように操作しない、という選択肢はありませんね。
私が知る限りでは、複数コメントの一括制御はショートカットキーや標準機能では無理かと思ってます。「ジャンプ」機能(F5)によるコメント選択ではせいぜい一括削除くらいでしょうか。
結局、複数のコメントを一括で簡単に手間をかけずに綺麗にするためには
マクロで何とかするしかない！！
という結論に至りました。
- 一発で綺麗にコメントの位置を調整したい
- 一発で表示／非表示を切り替えたい
- 一発でコメントのサイズを自動調整に設定したい
同じように感じている人はきっとたくさんいますよね。
そんな方達向けにまぁまぁ汎用的なマクロを作ってみました。
コメント一括調整VBAマクロの操作イメージ
マクロを活用して、下記の画像操作と同じようにやってみてください。
コメント一括調整VBAマクロの主な仕様
細かい処理はコメントで補足してるので、ザックリとした仕様を箇条書きで記載します。
- コメントがない単一セルを選択している場合にコメントを新規追加
- 既存のコメントセルから範囲選択している場合に全コメントを一括編集
- 実行判断の確認メッセージ制御（フラグon/offあり）
- フォント変更やセルに合わせての移動やサイズ変更などの位置関係の制御
- その他、範囲指定、位置変更、表示編集、表示／非表示、自動サイズ調整などの一括制御
Excelの列幅と罫線VBAマクロのソースコード
コメント調整効率を爆上げしたい方は、下記のソースコードを以下↓↓↓のリンクの内容に従って個人用マクロにコピーしてご使用ください。
【Excel】超便利な個人用マクロブックにVBAを記録追加作成して実行【初心者も簡単】
仕事や勉強を行う上で、Excelを使用するならば、絶対に知っておいた方がよい「個人用マクロブックの設定方法」をまとめました。マクロを頻繁に使用する人、今まで「作業中のブック」のみに個別設定していた人は、自分の作業に合った共通処理の...
caymezon.com
2019.03.10
マクロならば、痒いところに手が届きます。用途によっては目的に沿わない可能性がありますが、Const値を持たせてなるべく汎用的に作成しています。
コピーしやすいように無理やり一つのSub関数にまとめてますが、別関数化してパラメータや変数で使い分けるのが正しいやり方ですね。
自分に合った方法にカスタマイズしてみてください。VBAマクロの勉強にも少しは役立つと思います。
Sub コメント追加_一括編集()
''''''''''概要''''''''''
'コメントがない単一セルを選択している場合にコメントを新規追加
'既存コメントセルから範囲選択している場合に全コメントを一括編集
''''''''''定義''''''''''
Const 確認メッセージフラグ = 0 '0:確認メッセージを表示しない、1:確認メッセージを表示する
'''''単一セル／既存コメントセルの制御項目'''''
Const コメントデフォルト高さ = 59
Const コメントデフォルト幅 = 96
Const コメントデフォルトテキスト = ""
Const フォント変更区分 = 0 '0:フォント変更なし、1:フォント変更あり
Const 変更フォントサイズ = 11
Const 変更フォント名 = "ＭＳ ゴシック" '等幅フォント推奨（幅の計算に影響する)
Const 位置関係区分 = 1
'9:位置関係を変更しない
'2:xlMoveAndSize セルに合わせて移動やサイズ変更をする
'1:xlMove セルに合わせて移動するがサイズ変更はしない
'0:xlFreeFloating セルに合わせて移動やサイズ変更をしない(Default)
'''''既存コメントセルのみの制御項目'''''
Const 範囲指定区分 = 0 '0:全てのコメント、1:選択セルのみ、2:全有効範囲セル
Const 位置変更区分 = 1 '0:位置を変更しない、1:位置を変更する
Const 表示編集区分 = 0 '0:表示編集しない、1:表示編集する
Const 表示非表示区分 = 9 '0:非表示にする、1:表示にする、9:一律表示変更なし
Const 自動サイズ調整区分 = 1 '0:自動サイズ調整をoff、1:自動サイズ調整をon、
'2:サイズ1行目固定テキスト幅調整、9:自動サイズ調整変更なし
Dim 実行確認 As Integer
Dim メッセージ As String
Dim 選択セル As Range
Dim 処理区分 As Integer
Dim 複数範囲セル As Range
Dim 既存コメントセル As Range
Dim コメント文 As String
Dim コメント文先頭行 As String
Dim 先頭行サイズ幅 As Double
''''''''''処理内容判断''''''''''
'アクティブセル
Set 選択セル = cells(ActiveCell.Row, ActiveCell.Column)
If 選択セル.Comment Is Nothing Then
'コメントがない単一セルを選択している場合
処理区分 = 1 'コメントを新規追加
Else
'既存コメントセルから範囲選択している場合
処理区分 = 2 '全コメントを一括編集
End If
''''''''''確認処理''''''''''
If 確認メッセージフラグ = 1 Then
If 処理区分 = 1 Then
メッセージ = メッセージ & "＜単一セルにコメントを新規追加＞" & vbCrLf
ElseIf 処理区分 = 2 Then
メッセージ = メッセージ & "＜既存コメントセルからの範囲選択全コメントを一括編集＞" & vbCrLf
End If
メッセージ = メッセージ & vbCrLf
If フォント変更区分 = 1 Then
メッセージ = メッセージ _
& "・コメントのフォントを変更します。" & vbCrLf _
& "（フォント:" & 変更フォント名 & "、サイズ:" & 変更フォントサイズ & "）" & vbCrLf
End If
If 位置関係区分 <> 9 Then
If 位置関係区分 = 2 Then
メッセージ = メッセージ _
& "・位置関係を「セルに合わせて移動やサイズ変更をする」に設定します。" & vbCrLf
ElseIf 位置関係区分 = 1 Then
メッセージ = メッセージ _
& "・位置関係を「セルに合わせて移動するがサイズ変更はしない」に設定します。" & vbCrLf
ElseIf 位置関係区分 = 0 Then
メッセージ = メッセージ _
& "・位置関係を「セルに合わせて移動やサイズ変更をしない」に設定します。" & vbCrLf
End If
End If
If 処理区分 = 2 Then
If 範囲指定区分 = 0 Then
メッセージ = メッセージ & "・全てのコメントを対象とします。" & vbCrLf
ElseIf 範囲指定区分 = 1 Then
メッセージ = メッセージ & "・選択セルのみを対象とします。" & vbCrLf
ElseIf 範囲指定区分 = 2 Then
メッセージ = メッセージ & "・全有効範囲セルを対象とします。" & vbCrLf
End If
If 位置変更区分 = 1 Then
メッセージ = メッセージ _
& "・全てのコメントの位置を変更します。" & vbCrLf
End If
If 表示編集区分 = 1 Then
メッセージ = メッセージ _
& "・全てのコメントの表示を編集します。" & vbCrLf
End If
If 自動サイズ調整区分 <> 9 Then
If 自動サイズ調整区分 = 0 Then
メッセージ = メッセージ _
& "・自動サイズ調整をoffに設定します。" & vbCrLf
ElseIf 自動サイズ調整区分 = 1 Then
メッセージ = メッセージ _
& "・自動サイズ調整をonに設定します。" & vbCrLf
ElseIf 自動サイズ調整区分 = 2 Then
メッセージ = メッセージ _
& "・サイズ1行目を固定とし、テキスト内容から幅を自動調整" & vbCrLf _
& " （改行付の長文コメントを一括で1行コメントにしたい場合の使用を推奨）" & vbCrLf
End If
End If
End If
'実行確認
If メッセージ <> "" Then
実行確認 = MsgBox(メッセージ & vbCrLf _
& "実行してよろしいですか？", vbYesNo + vbQuestion)
If 実行確認 = vbNo Then Exit Sub
End If
End If
'''''''''''コメント追加と一括編集処理''''''''''
On Error Resume Next
If 処理区分 = 1 Then
'''''コメントを新規追加'''''
With 選択セル
'コメント追加
.AddComment
'コメント表示
.Comment.Visible = True
'コメント選択入力
.Comment.Shape.Select True
.Comment.Text Text:=コメントデフォルトテキスト
'フォント
If フォント変更区分 = 1 Then
.Comment.Shape.TextFrame.Characters.Font.Size = 変更フォントサイズ
.Comment.Shape.TextFrame.Characters.Font.Name = 変更フォント名
End If
'サイズデフォルト設定
.Comment.Shape.width = コメントデフォルト幅
.Comment.Shape.height = コメントデフォルト高さ
' オブジェクトの位置関係
If 位置関係区分 = 2 Then
.Comment.Shape.Placement = xlMoveAndSize 'セルに合わせて移動やサイズ変更をする
ElseIf 位置関係区分 = 1 Then
.Comment.Shape.Placement = xlMove 'セルに合わせて移動するがサイズ変更はしない
ElseIf 位置関係区分 = 0 Then
.Comment.Shape.Placement = xlFreeFloating 'セルに合わせて移動やサイズ変更をしない
End If
End With
ElseIf 処理区分 = 2 Then
'''''全コメントを一括編集'''''
'範囲指定
If 範囲指定区分 = 0 Then
Set 複数範囲セル = cells.SpecialCells(xlCellTypeComments) '全てのコメント
ElseIf 範囲指定区分 = 1 Then
Set 複数範囲セル = Selection '選択セルのみ
ElseIf 範囲指定区分 = 2 Then
Set 複数範囲セル = ActiveSheet.UsedRange '全有効範囲
End If
'複数範囲セル分ループ
For Each 既存コメントセル In 複数範囲セル
If Not 既存コメントセル.Comment Is Nothing Then
With 既存コメントセル
' フォント変更
If フォント変更区分 = 1 Then
.Comment.Shape.TextFrame.Characters.Font.Size = 変更フォントサイズ
.Comment.Shape.TextFrame.Characters.Font.Name = 変更フォント名
End If
' オブジェクトの位置関係
If 位置関係区分 = 2 Then
.Comment.Shape.Placement = xlMoveAndSize 'セルに合わせて移動やサイズ変更をする
ElseIf 位置関係区分 = 1 Then
.Comment.Shape.Placement = xlMove 'セルに合わせて移動するがサイズ変更はしない
ElseIf 位置関係区分 = 0 Then
.Comment.Shape.Placement = xlFreeFloating 'セルに合わせて移動やサイズ変更をしない
End If
'位置変更
If 位置変更区分 = 1 Then
'行の表示・表示、行の追加・削除などでズレまくった場合、コメントの位置をセルの近くに一律移動
'位置設定
.Comment.Shape.top = .top - 7.8
.Comment.Shape.left = .left + .width + 11.2
End If
'表示編集
If 表示編集区分 = 1 Then
'ちょっとカッコよく形を変えたい場合の処理です。お好みの形に変更ください。
'形状を角丸四角形に変更
.Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
'塗り色・線色 変更
.Comment.Shape.Line.ForeColor.RGB = RGB(128, 128, 128)
.Comment.Shape.Fill.ForeColor.RGB = RGB(240, 240, 240)
'影 透過率 30%、オフセット量 x:1px,y:1px
.Comment.Shape.Shadow.Transparency = 0.3
.Comment.Shape.Shadow.OffsetX = 1
.Comment.Shape.Shadow.OffsetY = 1
'太字解除
.Comment.Shape.TextFrame.Characters.Font.Bold = False
'中央揃え
.Comment.Shape.TextFrame.HorizontalAlignment = xlHAlignCenter
End If
'表示／非表示
If 表示非表示区分 = 1 Then
.Comment.Visible = True '表示
ElseIf 表示非表示区分 = 0 Then
.Comment.Visible = False '非表示
End If
'自動サイズ調整
If 自動サイズ調整区分 = 0 Then
.Comment.Shape.TextFrame.AutoSize = False '自動自動サイズ調整off
ElseIf 自動サイズ調整区分 = 1 Then
.Comment.Shape.TextFrame.AutoSize = True '自動自動サイズ調整on
ElseIf 自動サイズ調整区分 = 2 Then
'サイズ1行目を固定とし、テキスト内容から幅を自動調整
コメント文 = .Comment.Text
'改行があるか否かで2行以上のコメントかをチェック
If InStrB(コメント文, Chr(10)) > 0 Then
コメント文先頭行 = Mid(コメント文, 1, InStr(コメント文, Chr(10)) - 1)
Else
コメント文先頭行 = コメント文
End If
'幅・高さ調整
'※フォントの種類やウィンドウの表示倍率によって変わるので各環境によって要微調整
'1行目テキストのバイト長を算出
先頭行サイズ幅 = LenB(StrConv(コメント文先頭行, vbFromUnicode))
'テキスト長からちょうどいいコメント幅を算出（等幅の"ＭＳ ゴシック"だとわかりやすい）
'.Comment.Shape.width = (先頭行サイズ幅 - (先頭行サイズ幅 / 10)) * 6 + 5 '倍率85%用
.Comment.Shape.width = (先頭行サイズ幅 - (先頭行サイズ幅 / 10)) * 6 + 20 '倍率100%用
.Comment.Shape.height = コメントデフォルト高さ
End If
End With
End If
Next
End If
If Err.Number <> 0 Then
MsgBox Err.Number & ":" & Err.Description
On Error GoTo 0 'エラーオブジェクト初期化
End If
End Sub
最後に
Excelの使い方は人それぞれ、いろんなやり方があると思いますが、一例としてご紹介させていただきました。
ExcelVBAマクロはちょっとした向上心さえあれば、取っ付きやすいプログラムなのでショートカットキーなどと組み合わせてぜひ活用してみてください。
Excel全ショートカットキー一覧はこちら↓↓↓
Excelの使い方や機能がわかるショートカットキー全まとめ一覧【初心者こそ必見】
表計算ソフトとして王者であるExcel。たくさんの人が使っていると思います。まぁまぁ高額ではありますが、その分、高機能なので使い倒したいとは思いませんか？単に表データを扱うだけではなく、図を挿入できたり、設計書などのドキュ...
caymezon.com
2019.04.01
ちょっと工夫すれば、ちょっとした操作に１分かかっていた作業を１０秒でこなすことができるようになる可能性があります。
それだけでも、積み上げれば相当の工数を削減できるはずなので、ぜひ自分に合ったやり方を模索していきましょう。
コメント