Excel VBA コードを実行する時だけシートの保護を解除する方法

シェアする

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)。

家族は妻子供2人の4人家族。
喫茶店(特にコメダ珈琲)が好き。カラオケが好き。

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら

Excel VBA でプログラミングしたコードを実行する時に 「シートの保護」を解除したい場合があります。

「シートの保護」を設定することでセルへの誤入力を防止できるので、必要な場合には使用したい機能です。

ただ、Excel VBA では保護されたセルの値を読み込む、あるいは保護されたセルへ値を書き出すということができません。

そこで、Excel シート上で「シートの保護」を設定しつつもコードを実行する時だけは解除したいわけですね。

今日は、その方法についてお話しします。

レクタングル大 広告

1、シートの保護とは

まず、シートの保護についてざっとお話しします。

一言で言えば、セルを編集できなくする機能のことです。

設定するとセルへの値の入力もできませんし、セルの色を変えることもできなくなります(部分的に編集を可能にする方法もありますが)。

シートの保護の設定

「校閲」タブの「シートの保護」を選択しパスワードを入力します。一度入力すると確認の意味で再入力を求められるので、同じパスワードを再入力します。これでシートの保護が設定できました。

Image(28)

逆にシートの保護を解除する場合には、「校閲」タブの「シートの保護の解除」を選択し最初に設定したパスワードを入力します。

これでシートの保護が解除され元の状態(保護されていない状態)に戻ります。

Image(29)

なお、パスワードを忘れると「シートの保護」を解除できなくなってしまいますので忘れないように注意しましょう。

特定のセルのみにシートの保護を設定する

さて、このシートの保護は特定のセルのみに行うこともできます。

正確には一部のセルだけにはシートの保護がかからないようにすることができるということです。

一例として次のようなシートを取り上げたいと思います。

次のシートのクリーム色のセルE6〜17 に月々の売上高(税込)を入力します。そして、セルF6〜17 では税抜金額を計算しています(E列の金額を 1.08 で割っている)。

Image(30)

さて、最終的には F列の売上高(税抜)につき VBA を使って以下を計算するものとします。

・平均値(セル F21 に書きだし)
・最大値(セル F22 に書きだし)
・最小値(セル F23 に書きだし)

※実際には VBA を使う必要のないことですが今回は事例として敢えて使います。

F列のセルには、売上高を税抜金額にするための計算式が埋め込んであるので、これを誤操作(Deleteキー、BSキー)でうっかり消してしまわないようにしなければなりません。

そこで、「シートの保護」により、入力が必要な特定のセル(E6〜17)以外のセルの編集ができないようにしておきます。

Image(31)

逆に言えばセルE6〜17 だけは編集(入力)ができるようにしておくということですね。

セルE6〜17 を選択した状態で「Ctrl + 1」を押して「セルの書式設定」を出します。

そして、「保護」のロックのチェック(✔︎)を外します。

Image(32)

続いてシートの保護を設定します。

「校閲」タブの「シートの保護」を選択しパスワードを2回入力しましょう。

これにより、セル E6〜17 以外は編集(入力)不可になりました。試しに何か入力を試みると以下のメッセージが出て拒まれます。

Image(33)

なおこちらの記事にも同様のことを書いていますので参考にして下さい。

2、コード実行時だけ「シートの保護」を解除する方法

さて、先に書いたようにF列の売上高(税抜)について VBA を使って平均値、最大値、最小値を計算します。

売上高(税抜)を F列から読み込んでこれらを計算しますが、「シートの保護」をした状態では数値を読み込むことはできません。

「シートの保護」をしていなければ読み込むことができますが、それでは誤入力防止という元々の目的を果たせません。

そこで

「シートの保護」をした状態で、コード実行時だけ解除する

という方法を取りたいと思います。

上記方法を取りつつ、平均値、最大値、最小値を計算するプログラミングコードを紹介します!

プログラミングコード

コードを以下に示します。

コード説明

コードを説明します。

シートの保護の解除(14行目)、シートの保護(68行目)

今回のコードの一番のポイントですね!

シートの保護のパスワードは「123456」と設定しているものとします。

シートの保護の解除(14行目)↓

コード実行直後にまずシートの保護を解除することによって、セルE6〜17から数値を読み込めるようになります。

シートの保護(68行目)↓

最後には再びシートを保護しておきます。

こうすることでコードの実行が終了したら元どおりにシートの保護がかかった状態に戻っています。

最初と最後にこれらのコードを書いておき、間に目的とする計算を行うコードを書くことになります。

今回最も大事なことですので、きっちり押さえて下さい。

平均値計算(20~28行目)

売上高(税抜)を12ヶ月分読み込みながら加算していき合計額を計算しています。

最後に合計額を 12 で割って平均値を計算しています。

最大値計算(30~44行目)

売上高(税抜)の配列データを順番に比較して行き、金額が最も大きいもの(最大値)が配列の最後の番号(12)の要素となるように並べ替えています。

最小値計算(46~60行目)

売上高(税抜)の配列データを順番に比較して行き、金額が最も小さいもの(最小値)が配列の最後の番号(12)の要素となるように並べ替えています。

計算結果の書き出し(62~65行目)

セル「F21」に平均値、「F22」に最大値、「F23」に最小値を書き出しています。

3、まとめ

Excel VBA でコードを実行する時だけシートの保護を解除する方法を説明しました。

シートの保護は誤入力の防止に使えますが、VBA を使ったときには保護したセルからは値を読み込めないという問題があります。

今回の方法は、シートを保護を維持しつつ VBAでの値の読み込みも可能にしています。

用途に応じて活用して頂ければと思います。

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
*リンク先は私が勤務する松井会計事務所の HP です。

◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆経理業務効率化コンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) の講義、マンツーマンレッスン
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

セミナー開催要望がありましたら、以下のリンク先にある個別のフォームよりお願い致します。
◆セミナー開催要望
税理士試験まとめ、税法論文の書き方

————————————————
税理士試験についてのまとめはこちらです!

税法2科目免除大学院についてのまとめはこちらです!

kindle電子書籍を出版しました。

税理士試験の税法2科目免除のために大学院で税法論文(修士論文)を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓

大学院での税法論文の書き方

本書籍の内容についてはこちらの記事をご確認下さい。

お問い合わせ・フォローボタン

———————————

よろしければフォロー下さい↓

Twitter フォローボタン:

Feedly フォローボタン:
follow us in feedly

Facebookページ:
*いいね!を押して頂くと更新情報が届きます。

関連コンテンツ



レクタングル大 広告
レクタングル大 広告

シェアする

フォローする