Запрет на создание новых листов в excel
Обновлено: 04.07.2024
В Microsoft Excel 2003 есть возможность запрета доступа ко всему файлу с помощью пароля, но иногда требуется запретить доступ к определенному листу в Excel. В этой статье я опишу свой способ, как можно поставить пароль на определенный лист.
Для начала расскажу, что я хочу реализовать. Вы открываете файл Excel и попадаете на определенный лист, доступ к которому Вы имеете, и при переходе на другой лист, на котором стоит пароль, будет выскакивать формочка для ввода пароля, при этом содержимое листа будет скрыто. Другими словами пользователь, который не должен, иметь доступ к этому листу не будет видеть, что там есть, но если пользователь просто случайно нажал на этот лист, у нас будет кнопочка возврата на исходный лист. Все это будет реализовано на VBA (Visual Basic for Applications).
Ставим пароль на доступ к листу Excel
Ну, давайте начнем, откройте Excel и сохраните его, допустим под названием «Пароль на лист». Затем на первом листе, в центре напишите «Секретные данные», а на Листе2 напишите «Общедоступный лист».
После этого нажимайте ALT+F11 для перехода в редактор Visual Basic. Затем нажмите Insert->UserForm (для добавления в проект формы). Добавьте на форму две кнопки и одно текстовой поле.
Измените свойство Caption кнопки CommandButton1 на «Ввод», а кнопки CommandButton2 на «Вернуться назад». Также измените значение Caption нашей формы (UserForm1), скажем на «Для продолжения введите пароль». Измените свойство TextAlign, нашего текстового поля TextBox1, на 2, т.е. выравнивание текста по центру. Также можете изменить шрифт и размер текста в свойстве Font. А еще в свойстве Text (объекта TextBox1) впишите «Введите пароль», чтобы по умолчанию загружался этот текст.
Если Вы все сделали правильно, то у Вас внешний вид должен получиться примерно вот таким (за исключением размера объектов):
Код VBA для запрета доступа к листу Excel
Затем впишите вот такой код в окно кода формы:
Теперь осталось вызывать эту форму при активации листа, для этого в редакторе VB выберите лист, на который Вы хотите поставить пароль, щелкните два раза по нему, у Вас откроется окно кода, Вы вставляете туда вот это:
Еще, для того чтобы по умолчанию всегда открывался общедоступный лист, также в редакторе выберите «Эта книга» откройте редактор кода (двойным щелчком) и вставьте вот это:
Можно еще запретить изменять пароль пользователем, т.е. допустим, с этим файлом работает человек, который немного разбирается в Excel, и он легко может зайти в редактор Visual Basic и изменить пароль. Для того чтобы этого избежать поставим пароль на редактирования VBA проекта. В редакторе Visual Basic нажмите правой кнопкой на VBAProject, откроется меню, в нем выберите Properties. Далее в следующем окне перейдите на вкладку Protection, поставьте галочку как на картинке и введите пароль.
После этого редактировать код всех макросов, т.е. код Visual Basic сможете только Вы, или тот человек, который знает пароль на проект VBA.
Если Вы все сделали правильно, то у Вас при открытие файла будет открываться «Лист2», а при попытке перейти на «Лист1» будет запускаться форма для ввода пароля, а при нажатие кнопки «Вернуться назад», Вы будете возвращаться на «Лист2».
Программка достаточно примитивная, но начинающим программистам VBA она может помочь в развитие своих более сложных и более нужных программ. Так как Вы ее легко можете изменить, дополнить или использовать совершенно в других целях. Надеюсь, она Вам хоть немного помогла!
Предположим, у вас есть рабочая книга, которая будет отправлена другим пользователям для просмотра или проверки ее содержимого, однако вы не хотите, чтобы другие вставляли в эту книгу какие-либо новые рабочие листы, чтобы предотвратить беспорядок в книге. В этой статье я расскажу о том, как запретить другим пользователям вставлять листы в книгу.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Запретить другим пользователям вставлять лист с помощью функции защиты книги
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Excel предоставляет функцию - Защитить книгу с его помощью вы можете защитить структуру книги, которая не будет вставляться, удаляться, переименовываться или выполнять другие операции. Пожалуйста, сделайте следующее:
1. Перейти к щелчку Обзор > Защитить книгу, см. снимок экрана:
2. В Защитить структуру и окна диалоговое окно, отметьте Структура вариант и введите свой пароль в текстовое поле, нажмите OK Показать Подтвердите пароль диалоговое окно и повторно введите свой пароль. Смотрите скриншоты:
 |  |  |
3. Затем нажмите OK , чтобы закрыть диалоговые окна, и теперь, когда вы вставляете новый рабочий лист в эту книгу, функция вставки рабочего листа будет недоступна.
Примечание: с помощью этой функции следующие серые элементы также можно использовать в этой книге.
Запретить другим пользователям вставлять лист с кодом VBA
Если вы просто хотите запретить другим вставлять листы, но также можете выполнять другие операции, у вышеуказанного метода есть свои ограничения. Здесь следующий код VBA может оказать вам услугу.
1. Откройте книгу, которую вы хотите отключить, чтобы вставлять листы.
2. Затем удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
3. Слева VBAProject панель, дважды щелкните ThisWorkbook для открытия Модуль, затем скопируйте и вставьте следующий код.
Код VBA: запретить другим вставлять лист
4. Затем сохраните и закройте этот код, вернитесь в книгу, и теперь, когда вы попытаетесь вставить новый лист в эту книгу, появится окно с предупреждением, напоминающее, что вы не можете вставлять листы. Смотрите скриншот:
Отключение в рабочей книге команды Сохранить как
Можно сделать так, чтобы все рабочие книги сохранялись в режиме «только для чтения». Для этого в настройках Файл → Сохранить (File → Save) нужно установить флажок Только для чтения (Read-only recommended). Так вы запретите пользователю сохранять изменения, которые он может внести в файл. Пользователь сможет сохранить измененный файл только под другим именем или в другой папке.
Иногда, однако, необходимо запрещать пользователям сохранять копии вашей рабочей книги в другом каталоге или папке, независимо от того, будет ли использоваться то же имя или другое. Другими словами, нужно разрешить пользователям сохранять файл поверх существующего, но запретить сохранять копию в другом месте. Это особенно удобно, когда несколько человек сохраняют изменения рабочей книги, и вам не придется собирать множество различных копий одной книги с одинаковыми именами в разных папках.
Событие Before Save, которое вы будете использовать в следующем коде, было впервые создано в Excel 97. Как предполагает его имя, это событие происходит перед сохранением рабочей книги, позволяя вам поймать действия пользователя еще перед этим фактом, выдать предупреждение и запретить Excel сохранять файл.
Чтобы написать код, откройте рабочую книгу, правой кнопкой мыши щелкните значок Excel слева от меню Файл (File) в строке меню рабочего листа и выберите пункт Исходный текст (View Code).
Введите в VBE следующий код и нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться в Excel.
// Листинг 1.3 Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim lReply As Long If SaveAsUI = True Then lReply = MsgBox("К сожалению, вы не можете сохранить эту книгу под этим имеенм. " _ & "Сохранить под другим именем?.", vbQuestion + vbOKCancel) Cancel = (lReply = vbCancel) If Cancel = False Then Me.Save Cancel = True End If End Sub
Запрет печати рабочей книги
Иногда возникает необходимость запретить пользователям печатать рабочую книгу, устраняя, таким образом, вероятность попадания ее в мусорную корзину или куда-нибудь на всеобщее обозрение. При помощи события Excel Before Print можно отследить и предупредить действия пользователя. Как и ранее, введите в VBE код из листинга 1.4.
// Листинг 1.4 Private Sub workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "К сожалению, вы не можете печатать этот файл", vbInformation End Sub
Если вы хотите, чтобы пользователи не могли печатать только определенные листы в книге, вместо предыдущего кода используйте тот, который показан в листинге 1.5.
// Листинг 1.5 Private Sub workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Sheet1", "Sheet2" Cancel = True MsgBox "К сожалению, вы не можете распечатать лист из этой книги", vbInformation End Select End Sub
Обратите внимание, что печать будет остановлена только для листов Sheet1 и Sheet2. Конечно, имена листов вашей рабочей книги могут быть любыми. Чтобы добавить их в список кода, запишите их в кавычках, разделяя запятыми. Если вы хотите запретить печать только одного листа, укажите только одно название в кавычках, не ставя запятую.
Запрещение добавлять рабочие листы
Excel позволяет защищать структуру рабочей книги, чтобы пользователи не могли удалять листы, изменять их порядок, переименовывать и т. д. Иногда, однако, возникает необходимость запретить пользователям добавлять рабочие листы, одновременно разрешив другие изменения структуры книги. Сделать это поможет код из листинга 1.6.
// Листинг 1.6 Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "К сожалению, вы не можете добавить больше листов в эту книгу", vbInformation Sh.Delete Application.DisplayAlerts = True End Sub
Еще один способ запретить пользователям добавлять листы — выбрать команду Сервис → Защита → Защитить книгу (Tools → Protection → Protect Workbook). Удостоверьтесь, что флажок Структуру (Structure) установлен, и щелкните на кнопке ОК. Однако, как мы уже упоминали в начале этого раздела, стандартная защита рабочих листов в Excel — это весьма грубый инструмент, который одновременно блокирует и множество других возможностей Excel.
Очень часто на своих тренингах и в форумах я слышу вопрос: как защитить доступ к книге так, чтобы для каждого пользователя был доступен только свой лист/листы? А другие ячейки или листы были недоступны для изменения или просмотра? Или скрыть отдельные столбцы с глаз пользователя? Часть подобного функционала предоставляется стандартными средствами Excel, а другая(например, доступность просмотра только конкретных листов) достигается только через макросы. В этой статье хочу привести несколько примеров реализации подобных разграничений прав между пользователями, их плюсы и минусы.
Что важно: не следует указывать здесь пароль, который совпадает хотя бы с одним из паролей для отдельных диапазонов. Думаю, понятно почему: чтобы защиту не могли снять те, кому этого не положено делать.
Теперь остается сообщить сотрудникам отделов их пароли: производственный - 2222 , коммерческий – 1111 .
При первой попытке изменить данные в ячейках C8:N11;C13:N14 - будет запрошен пароль на изменение ячеек созданного диапазона "коммерческий" ( 1111 ):
Если пользователю известен пароль для диапазона – его необходимо будет ввести лишь один раз. В дальнейшем для ввода данных в ячейки этого диапазона вводить пароль не придется до тех пор, пока файл не будет закрыт. После повторного открытия файла пароль необходимо будет указать заново.
Однако, если сотрудник другого отдела попытается изменить ячейки производственного отдела и пароль ему неизвестен – изменить данные этих ячеек не получится.
Также ни сотрудники коммерческого отдела, ни сотрудники производственного отдела не смогут изменить данные столбцов А и В(№ и наименование статьи), заголовки таблицы(строки с 1-ой по 7-ю) и строки с итоговыми формулами (12, 15 и т.д. – закрашенные зеленым). Они смогут изменять только те ячейки, которые перечислены в назначенных каждому отделу диапазонах. Внести данные в другие ячейки(не перечисленные в разрешенных диапазонах) можно будет исключительно сняв общий доступ с книги, а после этого защиту с листа –Рецензирование (Review) -группа Изменения (Changes) -Снять защиту листа (Unprotect sheet) . Но снять общую защиту сможет только тот, кто её создавал и кому известен "главный" пароль. Как правило это администратор или некий "смотрящий" файла и другие пользователи этот пароль не знают.
Плюс подобного метода в том, что такая защита может быть установлена для книги в общем доступе(подробнее про книги с общим доступом можно прочитать в статье - Ведение журнала сделанных в книге изменений).
Что необходимо учитывать для книг с общим доступом: создавать диапазоны для пользователей и устанавливать защиту на лист необходимо ДО назначения книге общего доступа, т.к. после того, как книге будет назначен общий доступ изменять параметры защиты листов и книги запрещено. При этом запрещены как установка защиты так и её снятие.
Минус данного метода в том, что нет дружественного интерфейса снятия защиты. Например, при попытке изменить какие-то ячейки одного из назначенных диапазонов нет никакой информации о том, что это за диапазон(коммерческий или производственный). Что в свою очередь может запутать пользователя. Так же данным методом невозможно скрыть листы, либо отдельные строки и столбцы. Можно лишь запретить изменение ячеек.
Разграничение прав доступа при помощи VBA
Самый большой минус всех методов ниже: они не будут работать при отключенных макросах (Что такое макрос и где его искать?). Во всех приложенных к статье файлах это предусмотрено и если макросы будут отключены, то пользователь увидит лист, предлагающий включить макросы. Подробнее см. в статье: Как запустить файл с включенными макросами?
Плюс подобного подхода - мы практически не ограничены в правилах: можем скрывать от отдельных пользователей любые листы, строки и столбцы, защищать отдельные ячейки и т.п. Я ниже приведу несколько вариантов реализации защиты кодами, а вам останется лишь выбрать тот, который больше подходит под задачу. Первые три больше демонстрационные, чтобы показать что можно сделать. А вот последний пример - Практический пример с использованием администратора - наиболее приближен к задачам, применяемым в работе и наиболее удобен для распространения среди пользователей.
Проект VBA во всех файлах открыт для просмотра и изменений . Однако перед распространением решений в реальности лучше его закрыть от просмотра и изменений - Как защитить проект VBA паролем.
Важно: приведенные ниже решения могут работать некорректно в книгах с общим доступом. А те решения, в которых устанавливается защита на листы вообще не будут работать, т.к. для книг с общим доступом невозможно изменять параметры защиты листов и книг.
В форме необходимо выбрать пользователя и указать пароль, соответствующий этому пользователю. Важно: Пароли и список доступных листов можно редактировать на очень скрытом листе "Users". Для каждого пользователя можно указать несколько листов. Указывать имена листов необходимо в точности такие же, какие они на самом деле. Это значит, что и регистр букв и каждый пробел должен быть учтен. Для разделения записей с несколькими листами используется точка-с-запятой(Лист1;Лист2;Лист3).
На листе "Main" перечислены имена пользователей, пароли для них и доступные для просмотра листы. Данная информация указаны только для ознакомления и тестов. Менять данные для реальных задач необходимо на листе "Users".
Важно: файл может работать нестабильно в книгах с общим доступом.
Читайте также: