【ExcelVBA】マクロでセル結合・解除

使うと便利、セル結合。
でも、VBAや関数を駆使するする人にとっては、鬼門のセル結合。

セル結合については、賛否両論で正直悩ましい処理なんです。

でも…
表の見た目を作るのに便利と言う事で、活用する機会が多いかと思います。

ただ、シートの保護を使うとセル結合が使えなくなります。
シートの保護中に使えるようにしたいんですが、
設定項目にも無くて、手動では設定できない。

となればマクロで組めるのかって事なんですが、
できます！

ただし！ロックしているセルでも結合ができてしまいます。

この辺りをクリアしないと、セル結合を使うのは危険なわけです。
なので、ロックセルを結合をさせない設定を踏まえながら説明しようと思います。

まず、前回作ったシートの保護機能プロシージャを呼び出します。
VisualBasicを開いて、標準モジュールで組んだので、Module1を開きます。
シート保護機能

Sub シート保護機能()

    Const cellName As String = "J1"
    Const protection As String = "保 護 中"
    Const notProtect As String = "解 除 中"
    Const yellow As Integer = 27
    Const green As Integer = 43
    Const pass As String = "1234"
    
    With ActiveSheet

        If .ProtectContents = True Then
            On Error GoTo ErrHandl
            .unprotect
 
            'キャンセルボタンを押した時'
            If .ProtectContents = True Then
                Exit Sub
                
            End If

            Range(cellName) = notProtect
            Range(cellName).Interior.ColorIndex = yellow
            
        Else
            Range(cellName) = protection
            Range(cellName).Interior.ColorIndex = green
        
            .protect Password:=pass, _
            AllowFormattingCells:=True

        End If

    End With
    
Exit Sub

ErrHandl:
    MsgBox "パスワードが違います。"
       
End Sub

詳しくは、前回記事を参照下さい。

セル結合は、シートの保護を掛けると、マクロからも操作できなくなります。
マクロから操作できるように、保護時にマクロが使えるように設定します。

If .ProtectContentsで、Else時にシート保護を掛けたのですが、
このActiveSheet.protectの項目に、保護中でもマクロが使えるように
UserInterfaceOnly:=Trueを追加します。

シートの保護後もマクロを受け付けるようになるので、
保護状態の表示を保護を掛けた後に設定して様子をみます。
シート保護機能

Sub シート保護機能()

    Const cellName As String = "J1"
    Const protection As String = "保 護 中"
    Const notProtect As String = "解 除 中"
    Const yellow As Integer = 27
    Const green As Integer = 43
    Const pass As String = "1234"
    
    With ActiveSheet

        If .ProtectContents = True Then
            On Error GoTo ErrHandl
            .unprotect
            
            'キャンセルボタンを押した時'
            If .ProtectContents = True Then
                Exit Sub
                
            End If
            
            Range(cellName) = notProtect
            Range(cellName).Interior.ColorIndex = yellow
                        
        Else
            .protect Password:=pass, _
            AllowFormattingCells:=True, _
            UserInterfaceOnly:=True
            
            Range(cellName) = protection
            Range(cellName).Interior.ColorIndex = green
            
        End If

    End With
    
Exit Sub
    
ErrHandl:
    MsgBox "パスワードが違います。"
       
End Sub

UserInterfaceOnly:=Trueは、protectコマンドの最後に設定すればOKでっす。

前回は、保護前に状態表示の設定をしましたが、
保護後に表示設定をさせます。

コーディングができたのでテストします。
シート保護⑮.jpg
無事に保護中の表示に切り替える事ができました。
これで、保護中でもマクロから命令できるようになったので、
セル結合・解除のマクロを作っていきます。

セル結合のボタンを押すと、結合・解除ができるのですが、
セルの結合状態で、ボタンの働きが変わります。

・選択範囲が結合される
・結合セルは解除される
・選択範囲内に結合セルがあるときは解除される

働きは、こんな感じになってます。

まずは、選択範囲内に結合セルがあるか無いかを確認します。
セル結合_解除

Sub セル結合_解除()
 
    Dim r As Long         '行のカウンター'
    Dim c As Long         '列のカウンター'
    Dim rowSt  As Long    '先頭セルの行番号'
    Dim columnSt As Long  '先頭セルの列番号'
    Dim rows As Long      '選択行数'
    Dim columns As Long   '選択列数'   
    
    
    With Selection
    
        rowSt = .row
        columnSt = .column
        rows = .rows.Count - 1
        columns = .columns.Count - 1

        
        For r = 0 To rows
            For c = 0 To columns
        
                If Cells(rowSt + r, columnSt + c).MergeCells Then
                    'セル解除'　　　
                
                End If
            
            Next c
        Next r   
    
        'セル結合'
    
    End With
         
End Sub

セルの選択範囲からセル結合してるかを判断するのですが、
選択範囲を設定して、各セルに結合セルがあるか確認する必要があります。

選択範囲を設定すには、
・先頭セルの行と列番号
・行と列の選択幅

この条件から計算する必要があります。

選択範囲の取得には、Selection.を使います。
行を取得
・Selection.row：選択範囲の先頭セルの行
・Selection.rows.Count：選択範囲の行数
列を取得
・Selection.column：選択範囲の先頭セルの列
・Selection.columns.Count：選択範囲の列数

ちなみに先頭セルとは、選択範囲の左上セルが先頭となります。

先頭セルの番号からFor ～ Toで行と列の数を設定して、
各セルの結合状態を調べます。
Cells（行、列）.MergeCells　で結合されていればTrueが返ってくるので、
If Cells(rowSt + r, columnSt + c).MergeCells Then　で判定して、
一つでもあれば、セル解除命令を実行します。

注意点としてなんですが、
先頭セルからループ回数を加算する事で、セル位置を割出しています。

本来、先頭セルが1行・1列目としてループを回すので、
For r = 1 To rows　For c = 1 To columns　とするのですが、
If文で、Cells(rowSt + r, columnSt + c)　と計算しているので、
開始行・列が1ずれてしまいます。

なので、ループ開始を0として処理しています。

行・列の数は、1行目から○○行目までとカウントしています。
0行・0列目は存在してないので、
rows = Selection.rows.Count - 1
columns = Selection.columns.Count - 1
とする事でループで増える0行・0列目のカウント数を減らしています。

ループが回り切ると、結合セルが無い事が分かるので、
ループを抜けたら結合させればOKとなります。

コマンドは、Mergeで結合、UnMergeで解除です。
セル結合_解除

Sub セル結合_解除()
 
    Dim r As Long         '行のカウンター'
    Dim c As Long         '列のカウンター'
    Dim rowSt  As Long    '先頭セルの行番号'
    Dim columnSt As Long  '先頭セルの列番号'
    Dim rows As Long      '選択行数'
    Dim columns As Long   '選択列数'
    
    
    With Selection
    
        rowSt = .row
        columnSt = .column
        rows = .rows.Count - 1
        columns = .columns.Count - 1
  
        For r = 0 To rows
            For c = 0 To columns
        
                If Cells(rowSt + r, columnSt + c).MergeCells Then
                    .UnMerge
                    Exit Sub
                
                End If
            
            Next c
        Next r
    
        .Merge
    
    End With
         
End Sub

結合セルが見つかったらセルの解除を命令します。
解除を掛けたら、プロシージャを続ける意味が無いので、Exit Subで終わらせます。

ループが回り切ったら結合セルが無いので、
ループ終了後に結合を命令しておけば問題なく結合してくれます。

これでコーディングができたので、
シートの編集に戻って、ボタンと編集できるエリアを作成します。

ありえない表になってしまいましたが、簡単な枠を組んでみました。
(;^o^) ＼(ToT )ｶﾝﾀﾝｽｷﾞﾔﾛ

セル結合・解除ボタンと業務報告欄を追加します。
セル結合①.jpg
業務報告欄は、誰でも編集ができる欄になります。

この部分だけセルの結合が適用できるようにしたいのですが、
現段階では、どのセルも結合できてしまいます。

そこで、シートの保護設定を変更します。
まず、ロックされてるセルは選択できないようにします。
シート保護機能

    With ActiveSheet

        If .ProtectContents = True Then
            On Error GoTo ErrHandl
            .unprotect
                        
        Else
            .protect Password:=pass, _
            AllowFormattingCells:=True, _
            UserInterfaceOnly:=True
            .EnableSelection = xlUnlockedCells
            
        End If

シートの保護を掛ける所で、ActiveSheet.EnableSelection = xlUnlockedCellsを追加します。

これは、
セル結合②.jpg
ロックされたセル範囲の選択の項目です。
コードを追加する事でチェックを外した事になります。

テストしてみます。
シートを保護状態にして、
ロックセルをクリックすると選択する事が出来ませんでした。ヽ(´▽｀)/～♪

これで、ロックしてないセルだけが選択できるので、
業務欄のセルをロック解除します。

これで完成！とは行かないんですよね～。
売上個数入力欄は、セルロックしてないので結合できてしまうんですよ。( ﾉД`)ｼｸｼｸ…

なので、こちらを選択して結合させようとしたら
エラー表示を設定します。
セル結合_解除

Sub セル結合_解除()
 
    Dim r As Long         '行のカウンター'
    Dim c As Long         '列のカウンター'
    Dim rowSt  As Long    '先頭セルの行番号'
    Dim columnSt As Long  '先頭セルの列番号'
    Dim rows As Long      '選択行数'
    Dim columns As Long   '選択列数'
    
    
    With Selection
    
        rowSt = .row
        columnSt = .column
        rows = .rows.Count - 1
        columns = .columns.Count - 1
        
        If rowSt < 14 Then
            MsgBox "ここは結合できません！"
            Exit Sub
            
        End If
        
 
        For r = 0 To rows
            For c = 0 To columns
        
                If Cells(rowSt + r, columnSt + c).MergeCells Then
                    .UnMerge
                    Exit Sub
                
                End If
            
            Next c
        Next r
    
        .Merge
    
    End With
         
End Sub

業務報告欄の先頭セルが14行目なので、
13行目以下を選択していたらメッセージをだして、
プロシージャを抜けるようにしました。

これで、入力欄のセル結合も回避できるので、
セル結合が悪さをする事は無いと思います…　　(- ．-)ヾ たぶん


セル結合と解除は、ボタン一つで作れるので簡単なんですが、
結合させてもいいセルと結合させないセルを把握して設定しないと、
事故が起こります。(;^_^A

セル結合をマクロで組みたい方の参考にでもなれば幸いです。

次回は、ボタンの配置や埋め込みについて、
いろいろと小技があるので、その辺りを記事にしてみようと思います。

それでは、この辺で(TωT)/~~~　BYE　BYE







この記事へのコメント

コメントを書く