Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Макрос VBA на скрытие/раскрытие листов
 
#1
02.09.2015 14:33:09
Добрый день, уважаемые разработчики!

Прошу помочь с макросом VBA...
Имеется книга Excel, в которой 7 листов, у последнего имя "MacrosTurningOn".

Задача:
1. При закрытии книги, макрос должен СКРЫВАТЬ (как можно лучше прятать) все листы, кроме последнего - "MacrosTurningOn". А лист "MacrosTurningOn", наоборот, раскрывать.
нарыл в гугле код... Но он требует доработки в части раскрытия листа "MacrosTurningOn"...
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Sheets
If wsheet.Name <> "MacrosTurningOn" Then wsheet.Visible = xlVeryHidden
Next
Application.ScreenUpdating = True
End Sub

2. При открытии книги, пользователь видит, соответственно только 1 лист - "MacrosTurningOn", на котором должна быть кнопка, например, "Готов к работе с книгой".
2.1. При нажатии на эту кнопку происходит следующее:
2.1.1. Раскрываются все скрытые листы (№№ 1-6)
2.1.2. Скрывается 7-ой лист "MacrosTurningOn".

При этом у меня в этой же книге имеется макрос, защищающий листы паролем:
Код
Private Sub Workbook_Open()
 Const MyPassword = "***********"
 With Sheets(1)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(3)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(4)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(5)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(6)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
  End Sub

Соответственно, нужно сделать:
а) макрос на раскрытие листа "MacrosTurningOn" и скрытие других листов при закрытии книги;
б) макрос на кнопку с вышеописанными функциями (пункт 2.1.)

Заранее благодарен за помощь!
Изменено: Slimka - 02.09.2015 14:44:06
 
 
 
#2
02.09.2015 14:51:45
по первому вопросу
Код
If wsheet.Name <> "MacrosTurningOn" Then wsheet.Visible = xlVeryHidden else wsheet.Visible = true
Редко но метко ...
 
 
 
#3
02.09.2015 15:35:02
Цитата
Slimka написал: защищающий листы паролем
.Unprotect   - команда выполняющая снятие защиты с листа, для редактирования
Код
''' макрос на раскрытие листа "MacrosTurningOn" и скрытие других листов при закрытии книги
''' модуль Эта книга
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim oSheet
ThisWorkbook.Sheets("MacrosTurningOn").Visible = -1
For Each oSheet In ThisWorkbook.Sheets
  If oSheet.Name <> "MacrosTurningOn" Then oSheet.Visible = 2
Next
Application.ScreenUpdating = True
End Sub

''' макрос на скрытие листа "MacrosTurningOn" и раскрытие других листов
''' устанавливающий на листах пароль:
''' общий модуль
Sub VisibleSheets()
Application.ScreenUpdating = False
Dim oSheet
Const MyPassword As String = "1" 'это временная константа для проверки кода. если у Вас пароль хранится в глобальной переменной уберите эту строчку
On Error Resume Next
  For Each oSheet In ThisWorkbook.Sheets
     If oSheet.Name <> "MacrosTurningOn" Then
        With oSheet
        oSheet.Visible = -1
        .Unprotect Password:=MyPassword 
        .EnableOutlining = True

        .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
                                       AllowFiltering:=True, UserInterfaceOnly:=True
     End With
    Else: oSheet.Visible = 2
    End If
  Next
Application.ScreenUpdating = True
End Sub
Изменено: TSN - 02.09.2015 16:07:40
 
 
 
#4
02.09.2015 15:55:36
Если лупить
Код
If oSheet.Name <> "MacrosTurningOn" Then oSheet.Visible = 2 Else oSheet.Visible = -1
то выдает ошибку, так как не остается открытых листов... Щас выложу то, что у меня работает, оцените. Может кто-то оптимизирует
 
 
 
#5
02.09.2015 16:03:19
Цитата
Slimka написал: о выдает ошибку, так как не остается открытых листов..
Это мой бок, давно не работал с книгами Ексель :).  Исправил код, посмотрите первый код (сообщения №3) с изменениями.
Изменено: TSN - 02.09.2015 16:09:22
 
 
 
#6
02.09.2015 16:14:58
Код для "ЭтаКнига":
Код
  
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Sheets
If wsheet.Name = "MacrosTurningOn" Then wsheet.Visible = xlSheetVisible
Next
For Each wsheet In ActiveWorkbook.Sheets
If wsheet.Name <> "MacrosTurningOn" Then wsheet.Visible = xlSheetVeryHidden
Next
Application.ScreenUpdating = True
End Sub

Код для кнопки "Modules":
Код
Sub Macros()
Application.ScreenUpdating = False
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Sheets
If wsheet.Name <> "MacrosTurningOn" Then wsheet.Visible = xlSheetVisible
If wsheet.Name = "MacrosTurningOn" Then wsheet.Visible = xlVeryHidden
Next
Application.ScreenUpdating = True
Const MyPassword = "_____ЗДЕСЬ ВАШ ПАРОЛЬ_____"
 With Sheets(1)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(3)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(4)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 With Sheets(5)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
 Sheets("____ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО СДЕЛАТЬ АКТИВНЫМ ПОСЛЕ РАСКРЫТИЯ СКРЫТЫХ_____").Select
End Sub
Правда, есть один минус... Листом № 6 у меня является сводная таблица, которая также макросом была защищена:
Код
With Sheets(6)
 .Unprotect Password:=MyPassword
 .EnableOutlining = True
 .Protect Password:=MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
 AllowFiltering:=True, UserInterfaceOnly:=True
 End With
Ее защиту пришлось удалить, в связи с тем, что выдавало такую странную ошибку:
Нельзя изменять "сводной таблицы" на защищенном листе

Если есть мысли, как этот вопрос решить, сохранив защиту на сводной таблице - буду очень рад. Также буду благодарен комментариям и предложениям по оптимизации кода.
 
 
 
#7
02.09.2015 16:19:24
Код
Sheets("____ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО СДЕЛАТЬ АКТИВНЫМ ПОСЛЕ РАСКРЫТИЯ СКРЫТЫХ_____").Select
верно будет так
Код
ThisWorkbook.Sheets("ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО АКТИВИРОВАТЬ").Activate
Правда, есть один минус... Листом № 6 у меня является сводная таблица, которая также макросом была защищена:
Ее защиту пришлось удалить, в связи с тем, что выдавало такую странную ошибку:
Во втором коде (Sub VisibleSheets) стоит обработчик ошибок "on error resume next"   который прогинорирует ее.

Еще раз повторюсь
Первый вопрос Код для "ЭтаКнига":  уже переделал работает отлично.
Второй вопрос  - ответ код №2 (Sub VisibleSheets) из сообщения №3 построен циклом с показом листов, установкой пароля и обходом ошибки.
Изменено: TSN - 02.09.2015 16:35:26
 
 
 
#8
02.09.2015 16:46:07
Вот
Изменено: Slimka - 02.09.2015 16:48:02
 
 
 
#9
02.09.2015 16:49:58
Эта ошибка вылетает при открытии файла, до каких-либо действий.Ваши коды залил полностью, добавив лишь
Цитата
TSN написал:
верно будет такКод?1ThisWorkbook.Sheets("ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО АКТИВИРОВАТЬ").Activate
 
 
 
#10
02.09.2015 16:53:23
"ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО АКТИВИРОВАТЬ" - укажите конкретное название своего листа которое хотите активировать.
Специально написал чтоб понятно было, что нужно вписать название листа.
Естественно будет выдавать ошибку. У Вас есть лист с названием  "ЗДЕСЬ НАЗВАНИЕ ЛИСТА КОТОРЫЙ НАДО АКТИВИРОВАТЬ"  ???
 
 
 
#11
02.09.2015 16:56:49
Чтобы погасить сообщение на экране нужно добавить в код  такую конструкцию Application.DisplayAlerts = False  
Код
Sub VisibleSheets()
Application.DisplayAlerts = False 
... здесь остальной код из процедуры

Application.DisplayAlerts = True
End sub
Изменено: TSN - 02.09.2015 16:57:27
 
 
 
#12
02.09.2015 17:18:13
Ну уж не настолько я тупой, название листа добавил, разумеется =)
Нет, окно все равно выскакивает...  При том, подчеркиваю, сразу при открытии файла! Даже не успевает прогрузиться кнопка, открывающая скрытые листы
 
 
 
#13
02.09.2015 17:22:00
Slimka, не нужно создавать сообщения с "сейчас попробую", а сразу послених писать "проверено". Свои сообщения можно редактировать.
 
 
 
#14
02.09.2015 17:23:37
Викктур, учту Ваши комментарии. Я делаю это не для того, чтобы "набивать статусики с количеством сообщений", а чтобы человек, который мне помогает, понимал, что я здесь и надеюсь на решение вопроса. У Вас нечего предложить по теме?
 
 
 
#15
02.09.2015 17:26:30
Цитата
Slimka написал: У Вас нечего предложить по теме?
Если Вы не заметили, то vikttur - модератор и имеет право нам делать замечания, даже если ему нечего или нет желания что-либо предложить по теме. Мне нечего предложить - не интересно.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
Страницы: 1
Читают тему
Наверх