質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.55%

空白セルは保護せず指定の行と最終列から上の列を保護したい

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,014

green88008800

score 11

VBA初心者です。すみませんが教えてください。
このファイルは「管理簿」「ログインID認識」の2つのシートがあり、「ログインID認識」でログインIDを取得します。
それをもとに「ログイン認識」シートにVLOOK関数で管理者の名前を抽出させ、
「ログイン認識」VLOOK関数で抽出させたユーザーネームを「管理簿」シートの黄色の列に、入力規則によりプルダウンで選択させ、管理者がログインした時だけ管理者のユーザーネームがプルダウンで表示されるようになっています。
Excel2013で作成

1. B列を基準にして、B列の最後の値(書式ではなく)から上の行を保護したいのですが、
書式は関係なく最終の値を認識してその上の行を指定する方法が分からずに困っております。
(イメージ図で言えば「E12」が最後の値なので11行目~15行目までです)
下方向に最終行を検索するべきなのかと思うのですが、検索方法は
Dim maxrow
maxrow=Range("E65536").End(xlUp).Row
で合っていますか?これだと最終セルだけが認識されているような気がして間違っているような気がします。
その上の行を全部指定する方法なのですが、どのように行全体を指定したらいいのかが分からず困っております。
保護していない行の空白セルは自由に記述したいため(イメージ図では17行目以降の空白セルです)
UsedRange.SpecialCells(Type:=xlCellTypeBlanks)で空白セルを認識させています。
これだと、空白セルだけの認識なので、「行の保護」と相反していてどのように指定したらいいのか分からない
のですがどのように指定したらいいのか、教えていただけないでしょうか。
2.「保護」マクロを実行したら黄色の列も保護したい。(N列、P列)
単一列を指定するのでN列を指定するため
Columns(14).Interior.Locked = True
と書いて実行してみました。そうすると、保護されるセルと保護されないセルがあり、
(「N11」「N21」セルは保護されたのに「N18」は保護されない)
どうして同じ列なのに保護されるセルとされないセルがあるのか分からず悩んでいます。
3. ログインID認識シートをプロパティで「Vislible/0-xsheetHidden」で非表示に
して、更にパスワードかけたいのですが、
ActiveSheet.Protect Password:="●●●●●"
のようにパスワードを指定する方法などあるものでしょうか?
基本的な考え方も勉強中で、おかしいコードなどが混ざっているかもしれません。
すみませんが、ご教授いただけませんでしょうか。

  
イメージ説明

Sub 保護() '管理簿シートに記述
    If ActiveSheet.ProtectContents = False Then

             Cells.Locked = True
             ActiveSheet.UsedRange.SpecialCells(Type:=xlCellTypeBlanks).Locked = False

        ActiveSheet.Protect Password:="●●●●●●"
    End If
End Sub
コード
Private Sub Workbook_Open() 'ThisWorkbookに記述
    Application.ScreenUpdating = False 

            Dim environmentstring As String
            Dim username As String
            Dim i

                i = 1

            Do
            environmentstring = Environ(i)

        If Left(UCase(environmentstring), 9) = "USERNAME=" Then
            username = Mid(environmentstring, 10, Len(environmentstring))
            Sheets("ログインID認識").Cells(3, 1).Value = username

        Exit Do
        End If

        i = i + 1

        Loop Until Environ(i) = ""
    Application.ScreenUpdating = True

End Sub
コード
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • imihito

    2019/03/26 21:51 編集

    行、列の認識が誤っているため、質問文も正しい表現に修正してください。行は横方向の塊(1行目、2行目...)、列は縦方向の塊(A列、B列...)です。

    キャンセル

  • mattuwan

    2019/03/28 20:29 編集

    1)
    表の中のNo.の列って何が入るのでしょう?特に途中行が飛んで、急に99と100が出てきますが、99と100は特別な意味があるのですか?
    また、そのほかの番号は99以上にはならないということですか?

    2)保護を掛ける意味はなんでしょうか?
    特に管理者以外がその表を見ることがあるのでしょうか?
    ファイルを開くときにパスワードを入力させて、
    読み取り専用か編集も出来るようにするかを場合分け出来ないでしょうか?
    どんな人が利用して、だれなら編集可能にするのか、そして管理者とは何の管理をする人か、編集権限の範囲を明確にしましょう。
    (アプリ製作者も含めて)
    それから、プルダウンで管理者の名前を選択させる仕様のようですが、
    誰が今操作しているかを操作しているパソコンで判断するということはできないでしょうか?
    できれば、その辺も自動で出来た方が良いような気がします。
    また、管理者以外でも、セルに記入が出来そうな気がしますが、
    入力規則のリストで、一覧以外は入力できないよう設定しているということですか?
    ぼくなら、プルダウンめにゅうから選ぶくらいならキーボードで入力させてもらった方が楽かも。
    手がマウスに行ったりキーボードに行ったりするのは煩雑です。
    3)
    基本的に入力中の行以外のセルはロックして触らせたくないということですか?
    4)
    提示の表で、入力が必須な列を教えてください。
    それが全部埋まらないと次の行に入力できないようにしたらいいと思うのですが。

    とにかく事細かく想定している仕様を説明しないと、
    期待した結果は得られないかなと思います。

    キャンセル

  • green88008800

    2019/03/28 23:14

    1)Noは通し番号が入ります。1から下にデータが500件以上の年間データが入っていきます。
     今は、データが入っていくものと仮定して作成した為分かりづらくなってしまいました。すみません。
    2)保護をかける意味ですが、基本的に表を編集するのは管理者ではない方々です。管理者がはデータを見てサインとする時に見ます。管理者も5~7人ぐらいおります。共有サーバーの中で誰でもファイルを開くことのできる環境にあります。管理者ではなく実際にデータを入力する担当者は20人前後です。この方々が主に編集を行いますが、サインをする管理者もサインするために入力できなければなりません。頻繁に使うファイルなのであまりに開くのが遅かったり、重かったりすると不便で多少面倒でも軽くしようと考えています。管理者がサインした後に担当者がそのサインを消去してしまったりしては困るのでロックしようとしています。誰が今操作しているかは「Excel登録ネーム(●●さん)が編集中です。読み取りで開きますか」と表示され誰かが開いていると他の人は読み取り専用でしか開けません。管理者以外がセルに記入できないように入力規則を設定したので、なりすましできないと思ったのですが、「山田」さん(管理者)の名前を「田中」さんがコピーして貼り付けすると入力規則に反しないため簡単になりすましできることが判明し「管理者サイン」列をロックしなければと考えています。プルダウンメニューはなりすまし防止の為に設定しましたがその意味がないので直接入力したほうがいいかもしれないと思っております。確かにマウスとキーボード行き来するのは無駄かもしれません。
    3)入力中の行以外は触らせたくないわけではなく、管理者がサインしたあとに改ざんされるのが困ります。管理者間でなりすましして、他人の名前を入力されても困ります。責任の所在が分からなくなるからです。
    4)担当者が入力の必要な列は「管理者サイン」(黄色の列)以外の列です。
    管理者が基本的に入力するのは「管理者サイン」N列、P列です。そして、その他は担当者が入力します。イメージとしては部下が作成したデータを管理者が許可するイメージです。

    キャンセル

回答 3

checkベストアンサー

+1

とりあえずやりたいことをごちゃっと全部並べあげてしまうと、収集がつかなくなってしまいます。
まずは機能単位に整理して考えましょう。

大きく2つの機能(ブックオープン時の機能/保護ボタン押下時の機能)がありますよね?
まずこの2つで、わけられる部分ははわけて考えましょう。


それぞれについて、具体的な機能をざっと洗い出すと
【ブックオープン時の処理】
・パスワードのチェック処理
・シートの表示/非表示
・ブックの保護(非表示シートを再表示させないため)

【保護ボタン押下時の処理】
・ロック対象範囲の取得(E列最終行の取得を含む)
・対象セル範囲のロック
・特定セル(空欄/サイン欄)のロック解除
・シートの保護
・保護状態の表示(B4セル)
※保護解除時に「解除」を表示する処理は【保護ボタン押下時の処理】とはタイミングが異なりますので別途処理を用意する必要があります。

といった具合でしょうか。

ただでさえ初心者さんがチョイチョイと作れる物量ではなさそうなのに、これをごちゃっとひとくくりに考えてしまっては、手の付けどころもわからなくなってしまいませんか?

細かな機能単位に絞って、ひとつひとつ実装を積み重ねていきましょう。

1について

>検索方法は
>Dim maxrow
>maxrow=Range("E65536").End(xlUp).Row
>で合っていますか?

検索方法はあっていますが、このコードの意味は理解できていますか?
これはE列最終セルRange("E65536")から[Ctrl+↑]で移動.End(xlUp)したセルの行番号.Rowを取得し、maxrow変数に格納しています。

あるセルから[Ctrl+↑]で移動すると、データが入力されているセルの端まで移動します。
つまりE列最終行までデータが詰まっていない状況であれば、このセルから[Ctrl+↑]操作を行うことで、E列のデータが入力されている最終セルまで移動できるというわけです。

上記コードでは、見つけたセルそのものではなく、セルの行番号を取得しています。
今回、E列の最終データセルそのものに何かをしたいわけではありませんので、セルを単体で持っていても意味はありません。
データ範囲の最終行を知るための素材として利用しているわけです。

今回ロックしたい範囲は、11行目から最終データ行の1行手前ですよね?
これを取得したmaxrowを使用して表すとRange(Rows(11), Rows(maxrow -1))が対象の行範囲ということになります。

2について

私の手元の環境ではColumns(14).Interior.Locked = Trueはエラーとなります。
Lockedプロパティは対象セル範囲(Range型)に対するプロパティですので、Columns(14).Locked = Trueのような記述になると思います。(Columnsは指定列を指すRange型を返す。)

この記述で処理してもロックの状態がばらけるのであれば、ロックに失敗しているけどエラーを無視して処理を進めている可能性くらいしか原因が思いつきません。

3について

シートを非表示にしてブックを保護すれば、保護を解除しない限りシートを再表示することはできなくなります。
ブックを保護する際にパスワードをかけることもできますが、やりたいことはそういったことでしょうか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/29 22:58

    ありがとうございます。やりとげたい!という気持ちばかりが前に進み、実力を伴わないまま無理矢理進んでいるような状態ですが、Jawaさんや皆さんに教えていただき少しずつでも前進したいと思っております。
    シートを非表示にする方法、こんな方法があったのですね。新たな発見です!
    教えていただきありがとうございます。
    N列/P列は管理者のみがパスワードを入力してシート保護解除することで編集可能にし、ロックして保護、列の非表示まではしないようにしたいです。
    >「ログインID認識」シートは常に非表示。パスワードを知る人のみが再表示可能にしたい。
    >という認識でいますがあっていますか?
    はい。あっております。
    教えていただいた最終行から上の範囲選択ですが、自分なりに考えてみたのですが、
    見ていただけますか?
    「最終行から1行上の範囲を取得」
      Dim maxrow
      Dim 対象範囲
    「E列最大セル「E65536」から↑で行番号を取得」
      maxrow = Range("E65536").End(xlUp).Row
    「11行目以降「maxrow - 1」最終行から1個手前を取得し「対象範囲」に格納」
      対象範囲= Range(Rows(11), Rows(maxrow - 1))
    「N列とP列をロックする」
      Columns(14).Locked = True
      Columns(16).Locked = True

    キャンセル

  • 2019/04/01 22:02

    やはりうまくいきません。対象範囲が格納されていないようです。
    Dim maxrow
    Dim 対象範囲
    maxrow = Range("E65536").End(xlUp).Row
    対象範囲= Range(Rows(11), Rows(maxrow - 1)).Locked = True
    「Set」「Slect」を使うべきでしょうか?
    離れた列を指定したいときは、どのように書いたらいいでしょうか?
    Columns(14).Locked = True
    Columns(16).Locked = True
    と続けて書いてもエラーになってしまいます。
    Columns(14,16).Locked = True
    Range("N:N","P:P").Locked = True も駄目でした。

    キャンセル

  • 2019/04/02 20:49

    Jawaさん、詳しく分かりやすく教えていただきありがとうございました。
    もっと勉強します。もしよければ、また教えてください。
    回答していただきありがとうございました。

    キャンセル

+1

んと、なんとなくイメージがつかめましたが、
長文が多すぎて何が要点か解りません。
まずはご自分が、今何をすべきかを把握するため、
パソコンを離れ手書きで作業の流れを整理してみてはいかがでしょうか?
それで、ご自分の意図が回答者に伝わるように説明できるようになれば、
その説明をVBA語に翻訳すればエクセル君にも伝わる(=意図通り動いてくれる)と思います。

で、、、、
まず、このアプリを作る目的は、
「誤入力を出来る限り防ぎたい。(特に入力済みのデータの上書きを防ぎたい。)」
ですかね?
こういうアプリでは、「データをユーザーに直接触らせない。」が基本になります。
つまり、本来のデータベースとして使うシートに直接ユーザーに書き込ませないように作ります。
よくあるのは、一件一件のデータを入力させていく方法がとられます。
参考URL>>
ユーザーフォームの作り方

ただし、このやり方だと、複数行を一度に編集できるエクセルのいい点を消すことになりますので、
個人的に好みではありませんが、方法として一案ではあります。

そこで僕がお勧めしているのは、「シートをユーザーフォームのように使う」という方法です。
使い慣れたエクセルのシートを設定していくだけですからそれほど苦にはならないかと思います。
ただし、VBAで制御するのは少し難しくなるかもしれません。
でも、シート上で入力フォームを作ると、エクセルの機能がふんだんに使えますので
(もしかしてテーブル機能で入力をより楽にできるかも?)、
そういう面では開発が楽になると思います。

いま、やりたいのは、
「データ入力者によって編集できるセル範囲を変えたい。」
という事だと思いますので、
データ入力者により別の(同じに見えるが実質は違う)シートを使ってもらうようにすれば、
シート毎に設定を変えればいい(背景色を変えれば、どのシートを使っているかわかりやすいかも?)ので、プログラムでちょこまかセルの保護範囲を制御するより、
シートの表示・非表示を切り替えるだけなので、開発が楽になるのではないでしょうか?

と、いうことで、まずは、入力者のレベル分けと、
それぞれが編集できるセル範囲を整理するところから始めてはいかがでしょうか?

例えば、
業務のデータを入力する人(作業者)→B列からM列
データのチェックをする人(管理者?上司?)→B列からN列
最終にデータチェックチェックする人(部長?)→P列
(A列とO列はVBAで自動で入力。他の日付列も自動で入っちゃえばいいのかな?)
アプリ管理者(データベースの管理者)→すべてのデータ

こんな感じで制御したいのかなと思いますがいかがでしょうか?

これに、操作者の一覧を作り選択してもらい、
操作者の氏名を保持しておけば、
他の名前を使えなくなります。とは言っても手入力は出来ちゃうので、
その辺は別途制御を考えないといけないですが、それは後回しでいいのではないでしょうか?

まずは、運用してもらえるアプリを作り、
機能は後から少しづつ足していく感じでいいでしょう。
(ただし、全体の設計が上手くできてないと、途中で全改修することにはなると思いますが、
開発の全てを今から勉強するものに任せたつけでしょう。)
あまり凝ったものに最初から挑戦しても、完成せずにプロジェクトがとん挫することになるだけです。

とりあえず、

0)ファイルを開いたときに名前一覧シートを表示する
1)一覧シートから名前を選択
2)名前によりアクティブなシートを切り替える
3)アクティブなシート(それ以外のシートは非表示)にデータを入力してもらう
4)ブックを閉じるときに追記されたデータでデータベースを更新

という流れをまずは作ってみてはいかがでしょうか?

ファイルを開くときに重いとかどうとかよりは、
まずはこのアプリが稼働することの方が重要かと思います。
エクセルを開くとき2回目からは初回より起動が速くなりますし、
閉じるときも、ブックだけ閉じてアプリはそのまま起動した状態を保てば、
エクセルを起動する時間を省けます。ファイルを開くマクロをブックに用意して、
開くファイルと別にすると、エクセルを終了しないでブックを閉じる制御が簡単になるかもです。
データ数をみる限りそんな作業に支障が出るほど、重くなる可能性はないと思いますが。。。。
どうしても素早く起動したいなら、プログラムの改修や、ファイルの容量を抑えることに
時間をかけるより、ハイスペックなPCを導入したほうが対費用効果が出ると思います。
(案外違いがないかもですが、うちにあるパソコンは電源入れて5秒くらいで使える状態になりますが、
それからエクセルを起動しても30秒かからないんじゃないかなぁ。。。2003だから速いのかな^^;
会社で支給されたパソコンは1分以上待たないと使える状態にならない><エクセルも合わせると2分以上待っているのかなぁ。。。アドインソフトを不要だけど入れてるからめっちゃ遅い。)
(ついでに家のパソコンはエクセル2003^^;でも500行しか使わないなら2003でまったく不便がないのですが・・・・^^;;)

そんなこんなで、
作りかけてみて、躓いたらそのテーマで質問しながら開発していってはどうでしょうか?
いまの質問では、あれもこれもそれも思いつくことを羅列されていて、非常に答え難いです。

でも、質問しながら作っていては、1年掛かっても完成しないかもしれませんが。。。^^;
それくらい他人に使ってもらえるアプリを作るのは難しいです。
ここのサイトに上げるサンプルコードでもモノによっては数時間かけて書いているものもあるし、
本当に他人に使ってもらうように作るのなら、エラー回避などを含めた、動作確認や不具合の修正など、
そして、1年後に読んでも自分でわかるよう精査したりコメントを入れたりするのに、
1週間ぐらいかけたいのが本音です。
(実際、1年後に一部修正しようとして読んだら、自分で書いたコードが意味解らなくて、
2週間かけて全部新たに作り直したことがあります^^;;)

参考になれば幸いです。

P.S.
他人には簡潔にと言っておきながら、
とりとめもない長文すみません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/29 23:15

    回答していただきありがとうございます。
    なるほど!ユーザーフォームで入力させると便利ですね。
    入力者により表示するシートを切り替える方法、思いつきませんでした。
    名前一覧で表示するシートを分けていけば確かに簡単で便利ですね。
    人によって表示するシート分ける方法を試してみます。
    とても参考になりました。教えていただきありがとうございます。

    キャンセル

0

直接な回答ではなく、テクニック面の話です。


保護プロシージャ内の処理ですが、プロシージャが記述されている「管理簿シート」に対して操作をしたいのだと思われます。

そのように、「プロシージャが記述されている自分自身」に対して処理をしたい場合はMeキーワードを使うと良いです。

Sub 保護() '管理簿シートに記述
    If Me.ProtectContents = False Then
        Me.Cells.Locked = True
        Me.UsedRange.SpecialCells(Type:=xlCellTypeBlanks).Locked = False
        Me.Protect Password:="●●●●●●"
    End If
End Sub

Meを使うメリットとして、(今回の例では)「どのシート」を操作しているのか、がわかりやすくなります。

また、ActiveSheetのあとに.を打っても何も出てきませんが、Meの後ろなら、使える処理の一覧が出てきます(ProtectContentsなど)。
そのため、処理を記述する際の打ち間違えなどが減り、素早く正確に記述できるようになります。


Workbook_Openの中でUSERNAME環境変数の値を取得しようとしていますが、予め名前が分かっている場合はEnviron(環境変数名)で取得できます。
このことを踏まえると以下のように書けます。

Private Sub Workbook_Open() 'ThisWorkbookに記述
    Dim wsログインID認識 As Excel.Worksheet
    Set wsログインID認識 = ThisWorkbook.Worksheets.Item("ログインID認識")

    wsログインID認識.Cells.Item(3, 1).Value = VBA.Environ$("USERNAME")
End Sub

wsログインID認識の変数は無くてもよく、以下のように1行で書いてしまっても良いのですが、Worksheetsの後から入力補完が切れてしまうため、それを嫌って変数に入れています。

' 以下のどの書き方でも動作は同じ。
Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME")
Me.Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME")
ThisWorkbook.Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME")

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/31 21:46

    回答していただきありがとうございます。
    「Me」の使い方、大変勉強になりました。
    「Me」を活用して、打ち間違いを減らしていこうと思います。
    「Environ(環境変数名)」で取得するとこんなに簡潔にすっきりと書けるのですね。
    驚きました!ありがとうございます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.55%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる