Vba excel разрешить редактирование

Обновлено: 05.07.2024

Я использую Excel, где определенные поля разрешены для ввода пользователя, а другие ячейки должны быть защищены. Я использовал Tools Protect sheet, но после этого я не могу изменить значения в VBA script. Мне нужно ограничить лист, чтобы остановить ввод пользователя, в то же время разрешить код VBA изменять значения ячеек на основе определенных вычислений.

Если для параметра UserInterfaceOnly установлено значение true, код VBA может изменять защищенные ячейки.

Это лучшее решение, конечно. Если вы вернетесь и увидите этот комментарий, пожалуйста, исправьте опечатку в вашем коде - вместо «t» вместо «t» стоит «f»: UserInferfaceOnly Потребовалось несколько минут, чтобы выяснить, что не так после того, как я обработал ваш код , Что если я хочу редактировать только определенный диапазон ячеек? @Solver - если вы хотите разрешить пользователю редактировать ячейки на защищенном листе, вам нужно разблокировать эти ячейки (Range.Locked = False в VBA). если я пытаюсь использовать Activesheet.AutoFilter.ApplyFilter после защиты этим методом, я получаю ошибку 1004. Но без ошибки нет. У кого-нибудь есть идеи, почему это будет? Cells.AutoFilter работает как ни странно UserInterfaceOnly было бы прекрасно, если бы MS поддерживал его для новых функций. Если я попытаюсь обновить таблицу запросов с помощью VBA на защищенном листе с UserInterfaceOnly = true, произойдет сбой, мне придется снять защиту листа, обновить и снова защитить. Arghhhh .

Вы можете изменить лист через код, выполнив следующие действия.

В коде это будет:

слабость этого метода заключается в том, что если код прерван, а обработка ошибок не захватит его, рабочий лист можно оставить в незащищенном состоянии.

Код может быть улучшен, выполнив следующие действия

Этот код обновляет защиту на рабочем листе, но с параметром UserInterfaceOnly равным true. Это позволяет VBA-коду изменять рабочий лист, сохраняя личный лист защищенным от пользовательского ввода через пользовательский интерфейс, даже если выполнение прерывается.

Этот параметр потерян, когда рабочая книга закрыта и повторно открыта. Защита рабочего листа сохраняется.

Поэтому код "Повторная защита" должен быть включен в начале любой процедуры, которая пытается изменить рабочий лист или может быть запущена только один раз, когда рабочая книга будет открыта.

Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.

Уровень 0. Защита от ввода некорректных данных в ячейку

Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:

Уровень 1. Защита ячеек листа от изменений

Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:

Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:

Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.

Уровень 2. Выборочная защита диапазонов для разных пользователей

Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.

Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :

В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:

Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.

Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".

Уровень 3. Защита листов книги

Если необходимо защититься от:

удаления, переименования, перемещения листов в книге
изменения закрепленных областей ("шапки" и т.п.)
нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel

то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :

Уровень 4. Шифрование файла

При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:

Если вы работаете с книгами Excel древних форматов (таких, например, как Excel 4),
то в Excel 2010 вы можете столкнуться с проблемой, что редактирование этих файлов по-умолчанию запрещено.

В настройках Excel 2010 для этого предусмотрены специальные опции (скриншот)

Мало того, что Excel блокирует редактирование этих устаревших файлов,
так ещё и макросы начинают вести себя очень странно
(например, если активен файл Excel4, простейший макрос чтения пути текущего файла не запускается)

Избежать проблем при работе с такими файлами (а проблемы возможны, если вы обрабатываете макросом множество файлов, полученных от разных организаций), можно, если при открытии надстройки (файла с макросами) выполнить следующий код:

Достаточно поместить вызов этого макроса в процедуру Workbook_Open, - и ограничения будут сниматься при каждом запуске вашей надстройки.

Аналогично можно сделать макрос, включающий опцию «Доверять доступ к объектной модели проектов VBA»:

Этот код припишет в реестре Windows (в ветке с настройками безопасности Excel) необходимые параметры, и вы макросом сможете обращаться к свойствам проекта VBA
(например, чтобы выгрузить код макросов в текстовый файл, изменить кодовое имя листа или программно отредактировать код макросов, программно добавить элементы управления на форму, и т.д. и т.п.)

Отключить доступ тоже можно - достаточно поменять в макросе записываемое в реестр значение (с 1 на 0):

Многие пользователи надстроек (и других файлов Excel, содержащих макросы), при каждом запуске книги Excel во всплывающем окне жмут кнопку «Включить макросы».

Чтобы избавить пользователя от ежедневного уведомления об «опасных» макросах, можно использовать такой код:

После выполнения этого макроса, уровень безопасности в Excel будет изменен на «низкий» (при следующем запуске Excel), и уведомления об отключенных макросах (с предложением их включить) появляться не будут.

PS: C одной стороны, нехорошо без уведомления пользователя изменять настройки безопасности его программ.
Но, с другой стороны, макровирусов давно нет, а я сам не пишу вредоносные программы, так что считаю использование такого макроса допустимым.

Добрый день. Я собираюсь распространять некие рекомендации на каждый день в формате эксель.
Довольно частая проблема - блокирована работа макросов + старые версии эксель.
Пожалуйста напишите мне код в котором при открытии книги я разрешаю работу макросов для этой книги.
Для других, я так понимаю разрешения не будет. но, мне надо, что бы это работало "железно" и у всех.

И еще одна проблема. Одна из моих пользовательниц пользуется яблочным компьютером. Там макросы вообще не идут. Делать что?

Чтобы избавить пользователя от ежедневного уведомления об «опасных» макросах, можно использовать такой код:
-----------------------------------------
Sub Enable_AccessVBOM_and_Macro()
On Error Resume Next
Key$ = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & _
"\Excel\Security\"

' включаем программный доступ к объектной модели проекта VBA
CreateObject("WScript.Shell").RegWrite Key$ & "AccessVBOM", 1, "REG_DWORD"

' ставим низкий уровень безопасности (применится после перезапуска Excel)
CreateObject("WScript.Shell").RegWrite Key$ & "VBAWarnings", 1, "REG_DWORD"
End Sub
-----------------------
Достаточно поместить вызов этого макроса в процедуру Workbook_Open
======================================================================

Сам нашёл создаем параметр тут
[HKEY_CURRENT_USER\ Software\Microsoft\ Office\14.0\word\security]

Подскажите при добавлении в реестр строку [HKEY_CURRENT_USER\ Software\Policies\Microsoft\ Office\14.0\word\security]
"VBAWarnings"=dword:00000004 (при любом значении)
в самом ворде меню "параметры макросов" становиться не активной, как её оставить активной?

С хабра - "Исследователями в области информационной безопасности был обнаружен новый тип ransomwave — вредоносной программы, шифрующей пользовательские файлы и требующей выкуп в bitcoin. Новый криптовымогатель, который сами создатели назвали «locky», распространяется не совсем стандартным для подобного ПО способом — при помощи макроса в Word-документах. " , так что срочно закрываем макросы

Так! У мене була таже проблема! При програмном створенні нової книги потрібно було вставити в неї макрос і процедуру автозапуску макросу WorkBook_Open або Auto_Open цієї книги в подальшому при відкритті книги. Для цього потрібно встановити довірений доступ до проекту! Приклад функції Disable_AccessVBOM автора статті не деє належного результату: тобто після виконання тієї функції та подальшому зверненні до проекту книги для запису процедур (макросів) спливало повідомлення про помилку: "доступ до проекту не є довіреним . ". Запропонована автором статті функція не дає належного результату, як я припускаю, з тієї причини, що в Office встановлений подвійний ключ на управління доступом до проекту, пов'язаний з самою формою "Безпека макросів". Я вирішив проблему, з Божою допомогою, через запуск форми і її управління за допомогою функції SendKeys. Вибачте, я не буду приводити алгоритм у запобігання того. щоб він опинився в злих руках! Якщо у Вас гарні цілі. то експерементуйте.

Уважаемый админ. Снова Я. Недавно переставил Винду (пару часов назад), поставил 7 на всякий случай, чистую. Поставил Офис 2010, но так как должны выгружаться данные из програмки в Excel они не выгружаются все равно. (( Снова моргнет загрузка и тишина. Макросы разрешил, даже в устной форме))) но ничего.

Олег, я знаю в чем проблема (неверное значение 1-2 ключей в реестре Windows),
но мне лень писать вам инструкцию по поиску и правке этих ключей (на своём компе я бы разобрался, но с чужим компом проще не связываться)

Потому, самое простое решение в вашем случае, - снести все Office какие есть, и поставить только один (рекомендую Office 2010)
При этом все эти ключи перезапишутся, - и всё должно заработать.

Если не поможет, - то проблема в самой этой вашей программе, запускающей файл Excel (недостаточно универсальная)

Спасибо большое за внимание к моей проблеме!
Дело вот в чем. До 2010 у меня стоял 2007 Office, была такая же ситуация, я подумал так же что проблема в установке и установил 2010. но как видно это не помогло.
Что может еще так влиять на такого рода действия?
Спасибо.

Олег, в вашем случае макросы ни при чём.
Ваша программа формирует файл Excel, и даёт Windows команду открытия этого файла.
Если файл не открывается, - значит, Excel некорректно установлен
(не прописаны 1-2 параметра в реестре, отвечающие за команду открытия файлов Excel)

Кто подскажет? Ситуация такая. Есть программа в которой делаю различные операции, а когда нажимаю кнопку Print, то должна запуститься Excel 2010 и все данные туда выкладываются . Так вот что, на других компах все норм работает, а у меня жмешь и ничего (на 1сек знак загрузки моргнет и все). Excel не запускается вообще. Макросы разрешены в безопасности. Что здесь может быть, куда необходимо нажимать. )) В Excel я как видите не силен. ((

а если даже после нажатия на "Включить содержимое", макросы не запускаются?

Есть другой вариант (если не хотите пересохранить файл после первого открытия):
код помещаете в модуль временного активного листа, процедуру запуска - в событие активации листа
После выполнения макроса удаляете этот временный лист - он удалится вместе с кодом, и макросов в файле не останется.

Дело в том, что я сделал книгу ексель, в которой находяться: Private Sub Workbook_Open в ЭтаКнига и 2 модуля. Эта книга должна автоматически открываться на 100 разных компьютерах. Задача стоит в том, чтобы по завршении работы программы все макросы удалялись из этой книги (мне это нужно для того, чтобы пользователи, которые будут открывать эту книгу постоянно не сталкивались с вопросом: "Эта книга содержит макросы. ". Программно ставить високий или низкий уровень макросов я не хочу (может у пользователей есть какие-то нюансы работы с ексель)). Версий ексель на этих компьютерах я не знаю, поэтому вариант с сохранением в формате xlsx мне не подходит.

jaroslav, надо перезапустить Excel, чтобы измененные в реестре настройки безопасности вступили в силу
(впрочем, если вручную менять в интерфейсе Excel - часто тоже перезапуск Excel требуется)
Т.е. не получится так, что макрос выполнили, я прям тут же начали программно работать с объектной моделью.

PS: а такие макросы, как Delete_Macroses, вообще не нужны
всё делается на порядок проще, одной строкой кода, - сохранением файла в формате XLSX
(Если нужен другой формат файла, но без макросов, то: создаете программно XLSX методом SAVEAS, закрываете созданный XLSX, открываете снова, сохраняете в нужном формате под нужным именем.)

А макросы, работающие с VBProject, советую использовать только на своем компе (для личных нужд) - где можно поставить галочку в настройках вручную.

Вы пишете:
"Аналогично можно сделать макрос, включающий опцию «Доверять доступ к объектной модели проектов VBA»:

Sub Enable_AccessVBOM() ' включает программный доступ к объектной модели проекта VBA
On Error Resume Next
Key$ = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & _
"\Excel\Security\AccessVBOM"
CreateObject("WScript.Shell").RegWrite Key$, 1, "REG_DWORD"
End Sub

Этот код припишет в реестре Windows (в ветке с настройками безопасности Excel) необходимые параметры, и вы макросом сможете обращаться к свойствам проекта VBA".

Действильно этот макрос прописивает в реестре Windows (в ветке с настройками безопасности Excel) необходимые параметры, но галочку возле Доверять досту к Visual Basic Project в меню Надежные издатели меню Безопасность макросов вкладки безопасность меню Сервис/Параметры в Excel 2003 он не ставит. И при запуске макроса:
Sub Delete_Macroses()
Dim oVBComponent As Object, lCountLines As Long
If ActiveWorkbook.VBProject.Protection = 1 Then
MsgBox "VBProject ." & vbCrLf & _
" "
Exit Sub
End If

For Each oVBComponent In ActiveWorkbook.VBProject.VBComponents
On Error Resume Next
With oVBComponent
Select Case .Type
Case 1
.Collection.Remove oVBComponent
Case 2
.Collection.Remove oVBComponent
Case 3
.Collection.Remove oVBComponent
Case 100
lCountLines = .CodeModule.CountOfLines
.CodeModule.DeleteLines 1, lCountLines
End Select
End With
Next
Set oVBComponent = Nothing
End Sub
выдает ошибку 1004: "Отсутствует доверие к программируемому доступу к проекту Visual Basic". Подскажите, пожалуйста как программно поставить галочку в екселе возле Доверять досту к Visua Basic Project в меню Надежные издатели

По какой-то причине Enable_AccessVBOM() не выполняет, так сказать, полноценный запуск "доступа к объектной модели проекта VBA".
Т.е. макрос срабатывает, ошибок не выдает, но "доступа к объектной модели проекта VBA" все равно не предоставляет. Хотя галочка в соответствующем пункте меню проставляется.
При закрытии файла и последующем его открытии галочки опять нет.
А при запуске макросов в текущем файле выдает ошибку, что "доступ к объектной модели проекта VBA" не предоставлен.
Единственный путь полноценного выполнения макроса, который я нашел, это зайти в настройки, где галочка пункта "доступ к объектной модели проекта VBA" уже стоит и подтвердить нажатием "ОК".
Только тогда все срабатывает как надо.

Protsiv, конечно же, ваш макрос не сработает.

Для того и отключают в Excel макросы, чтобы они не запускались.

Для чего был написан этот код: Если пользователь, запустив ваш файл, увидит предупреждение о макросах, и нажмёт кнопку «Включить макросы для этого файла»,
то тогда (и только в этом случае) код сработает - и в следующий раз пользователь не будет видеть предупреждения о макросах.

Ну а если пользователь откажется от включения макросов - тут ничего не сделать, придётся запускать аналогичный код из скрипта VBS или приложения EXE.

Лично я в таких случаях поступаю проще: скрываю заранее все листы файла, оставляя только один видимый лист - инструкцию по включению макросов.
Тут у пользователя выбора нет - либо работать с файлом с макросами, либо вообще не пользоваться файлом.

Вставил этот макрос в Private Sub Workbook_Open()
Но ведь он даже не начинает выполняться, поскольку установлен высокий уровень безопасности, не говоря уже об автоматическом перезапуске Экселя.
Может я что-то делаю не так?

Александр, проблема решается путем исправления макроса.
Если макрос нормально написан - для него не должно быть разницы, защищены файлы, или нет.
Если есть необходимость - можете заказать доработку того макроса, или написание нового, под ваши нужды.

Суть проблемы - есть более 100 файлов excel, в каждом по одному листу, на книге установлена защита без пароля, при обработке макросом сбора листов из всех этих книг в одну книгу, он не работает ссылаясь на то, что книги защищены. Вопрос в том как это победить?

В статье речь про другую защиту.
Включить макросы из вашего файла не получится.
Тот код, что вы показали - это скрипт VBS (т.е. отдельный файл)
На ПланетеExcel неоднократно этот вопрос обсуждали - воспользуйтесь поиском.

PS: Если надо распространять файл с макросами, а объяснять способ их включения вы не хотите, - укомплектуйте ваш файл установщиком (файл exe), который скопирует файл Excel в нужную папку, и ярлык для него поместит в папку автозапуска Excel (или в папку Addins)
Тогда макросы будут работать без изменения уровня безопасности Excel.

Нашел вот такой вот СКРИПТ

Sub test()
Dim objXL
Dim Secur
Set objXL = CreateObject("Excel.Application")
objXL.Visible = TRUE
secur = objXL.AutomationSecurity
objXL.AutomationSecurity = 1
objXL.Workbooks.Open ( mid(Wscript.ScriptFullName,1,len(Wscript.ScriptFullName)-8) & "test.xls")
objXL.AutomationSecurity = secur
End Sub

Суть вопроса - в файле есть юзерформа, которая активизируется при открытии файла, но если защита включена, то соответственно форма не появится и необходимо будет сначала выключить защиту (т.е. включить макросы), а уж потом нажать на кнопку "Меню", чтобы отобразилась форма. Программа будет запускаться в большой Холдинг и по этому, чтобы не учить всех сотрудников сети включать макросы - хотелось бы их включать автоматически при открытии файла. Если есть такая возможность дописать код - буду очень Вам благодарен. Т.к. у меня не выходит. Заранее спасибо!

Смотря зачем отключать, и из какого файла.
Если вы хотите получить доступ к коду файла, при открытии которого будет выполняться снятие защиты,
то не знаю, применятся ли настройки сразу
(возможно, придётся макросом закрыть текущий файл, и открыть его снова)

А возможно ли если включена защита - отключить её сразу при открытии файла?

Можно. Это запуск cmd.exe. И запуск из нее Excel после двухсекундной задержки. Если быть точным, не Excel, а предварительно сохраненной рабочей области.

Я извиняюсь, а можно прокомментировать следующую строку кода:
Shell "cmd.exe /c" & "ping -n 2 localhost > null&&start C:\Temp\resume_3.xlw&&del C:\Temp\resume_3.xlw -f"

Пожалуйста!
У этого способа есть явные слабые места. Просто написал первое, что пришло в голову.

Я тоже думаю, что перезапускать Excel в данном случае смысла нет.

Но за код перезапуска Excel - огромное спасибо.
Сам бы не догадался до такого способа.
Осталось только придумать ситуацию, когда подобный перезапуск приложения может быть полезен.

Вообще, я подумал в спокойной обстановке и получается, что нет смысла передергивать Excel.

ОТВЕТЫ

Ответ 1

Если для параметра UserInterfaceOnly установлено значение true, код VBA может изменять защищенные ячейки.

Ответ 2

Вы можете изменить лист через код, выполнив следующие действия.

В коде это будет:

Код может быть улучшен, выполнив следующие действия

Этот параметр потерян, когда рабочая книга закрыта и повторно открыта. Защита рабочего листа сохраняется.

Ответ 3

~~Я не думаю, что вы можете установить любую часть листа для редактирования только VBA~~, но вы можете сделать что-то, что имеет в основном тот же эффект - вы можете снять защиту листа в VBA прежде чем вам нужно внести изменения:

и повторно защитите его после того, как вы закончите:

Edit: Похоже, что это обходное решение, возможно, решило ближайшую проблему Dheer, но для тех, кто сталкивается с этим вопросом/ответом позже, я ошибся в первой части моего ответа, как указывает Джо. Вы можете защитить лист, подлежащий редактированию только VBA, но, похоже, опция "UserInterfaceOnly" может быть установлена только при вызове "Worksheet.Protect" в коде.

Ответ 4

Основной, но простой для понимания ответ:

Ответ 5

В качестве обходного пути вы можете создать скрытый рабочий лист, который сохранит измененное значение. Ячейка на видимом, защищенном листе должна отображать значение со скрытого листа с использованием простой формулы.

Вы сможете изменить отображаемое значение с помощью скрытого рабочего листа, в то время как ваши пользователи не смогут его редактировать.

Ответ 6

Я выбрал ячейки, которые я хотел заблокировать в листе 1, и поместил предлагаемый код в функцию open_workbook() и работал как шарм.

Читайте также: