シートの保護状態でもVBAでセル操作する方法

シートの操作シートの操作

エクセル住所録や成績表などで誤って計算式が入っているセルを消されないように、シートの保護を設定していることありませんか?

これで安心と思ってユーザーフォームに配置された、テキストボックスの値をセルに転記しようとしたら、エラー表示!!

VBAシートの保護エラー表示
シートの保護したらエラー表示!なぜ?
<span class="fz-12px">とりっぷぼうる</span>
とりっぷぼうる

VBAの動作確認が終わり、仕上げにシートの保護したらエラーっていう経験思い出しますね。

 

まずは基本からおさらいしましょう。

スポンサーリンク

シートを保護・解除するVBAコード

まずは一番シンプルで、いつも使っているシートの保護・解除するVBAコードで、ユーザーフォームに配置したコマンドボタンに書いているVBAコードです。

シートの保護するVBAコード

シートの保護

Private Sub CommandButton1_Click()
ActiveSheet.Protect
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. 表示中のシートにシートの保護を設定する
  3. マクロ終了

シートの保護解除するVBAコード

シートの保護解除

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect
End Sub

VBA解説
  1. コマンドボタン2が押されたら
  2. 表示中のシートにシートの保護を解除する
  3. マクロ終了

スポンサーリンク

シートの解除で始まり保護で終える

VBAコードはSubで始まりEnd Subでプログラムが1行目から順番に作業を進めていきます。

シートの保護の状態では、セルにテキストボックスの値を書き写したり、並べ替えをしたりという作業(VBAコード)は一切できないのでエラーになってしまうので、下記のような手順でVBAコードを書きましょう。

  1. シートの保護解除をする
  2. 本来の作業を行うVBAコード
  3. シートの保護をする

このように本来のVBAコードの1行前にシート保護解除コード、1行後にシート保護コードを入れるだけで、直感的には常にシートの保護状態になっているようにエクセルを操作できますよ。

シートの保護状態でもVBAでセル操作する

Private Sub CommandButton1_Click()
ActiveSheet.Protect
Range(“S2”).Value = UserForm1.TextBox1.Value
MsgBox “本年度期間開始日が変更されました”
ActiveSheet.Unprotect
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. 表示中のシートにシートの保護を解除をする
  3. セルS2の値はユーザーフォーム1のテキストボックス1の入力値とする
  4. 「本年度期間開始日を更新しました」とメッセージを表示する
  5. 表示中のシートにシートを保護設定をする
  6. マクロ終了

これは、セルS2にテキストボックスの値を書き込みメッセージ表示するのですが、シートが保護されているとセルS2に書き込めないので、まず解除して本来の作業を行い、保護を設定して終了するようになっています。

<span class="fz-12px">とりっぷぼうる</span>
とりっぷぼうる

とても簡単なので、ぜひ活用してくださいね。

スポンサーリンク

パスワード設定してシートの保護・解除をする方法

最初は使っていたのですが毎回パスワードを求められるので、 手間が面倒になり今は使っていないのですが、シートの保護・解除の時にパスワードを設定することができます。

パスワード付きシートの保護・解除

パスワードは「tripbowl」にします。

パスワード付きシートの保護

Private Sub CommandButton1_Click()
ActiveSheet.Protect Password:=”tripbowl”
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. 表示中のシートをパスワード「tripbowl」で設定し保護する
  3. マクロ終了

パスワードを入力してシートの保護を解除する

エラー防止の為、「パスワードが違います」とメッセージを表示準備します

パスワード付きシートの解除

Private Sub CommandButton1_Click()
On Error GoTo errhandler
ActiveSheet.Unprotect
Exit Sub
errhandler:
mggsgbox “パスワードが違います”
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. パスワードが違う時はerrhandlerの処理をする
  3. 表示中のシートでシートの保護を解除する
  4. 一旦処理終了
  5. エラー処理は
  6. パスワードが違いますとメッセージを表示する
  7. マクロ終了
シートの保護関連VBAコード

スポンサーリンク


シートの操作
スポンサーリンク
参考になりましたらシェア宜しくお願い致します

山頂テラスデッキからの三段紅葉(2019年11月)

フリーパスって助成金つぎ込んでいいの?

コロナ禍のGo To トラベル以外にも助成金だらけでうらやましいね。

安くなったフリーパスで遊びに行きたいけど、給与も減って余裕ないよね。

Excel VBA