Компьютерный форум OSzone.net  

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)
-   Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)
-   -   [Решено] Защита "от дурака" в Excel (http://forum.oszone.net/showthread.php?t=284731)

OSWin777 08-07-2014 13:33 2373601
[Решено] Защита "от дурака" в Excel
 
Вложений: 1
  • Bug.rar (112.30 KB, скачиваний: 21)
Здравствуйте!

Прошу помощи у специалистов!
Приложен файл с ошибками в 327-й строке. Стоит защита листа (изначально - с паролем). Файл сделан в расчете на пользователей, НЕ УМЕЮЩИХ пользоваться компьютером в принципе.
КАК могла возникнуть подобная ошибка??
Как модифицировать файл, чтобы избежать подобных ошибок в дальнейшем?

Заранее большое спасибо!

a_axe 08-07-2014 14:17 2373613
OSWin777, не очень понятно в чем заключается ошибка.
Приложите файл с этой строчкой без ошибок, чтобы было понятно, как выглядит строка в исходном варианте. Тогда станет ясно, как избежать этого в дальнейшем.
PS Речь идет только об условном форматировании?

a_axe 08-07-2014 14:39 2373622
Если вопрос только в форматировании, то проблема была в следующем:
Условное форматирование (красная заливка строки) работает на основании значения ячейки в столбце F. Соответственно, пользователь судя по всему неосознанно схватил ячейку F327 за рамку и перетащил ее в ячейку C327. Ячейка была незащищаемой.
Решение на первый взгляд такое:
1. Снять защиту с листа.
2. Указать в свойствах всех ячеек, что они защищаемые.
3.Кнопкой "Разрешить изменение диапазонов" указать диапазон(ы) тех ячеек, в которые пользователь будет вбивать данные
4. Включить защиту листа.
если проблема в ином ракурсе, отпишитесь.

Ох ты, я эти ошибки вообще не увидел)

Ну получается что ячейка С327 была заменена ячейкой F327. Решение то же.

OSWin777 08-07-2014 20:27 2373815
a_axe
Большое спасибо за предложенный способ!
Но появляется одна проблема. При включении защиты листа необходимо будет указывать выделение заблокированных ячеек. Т.о. вместо перемещения только по нужным пользователю для заполнения белым пустым ячейкам он будет перемещаться по всем, что увеличивает продолжительность его работы и способно вызвать дополнительные затруднения.
Нет ли такого способа, чтобы исключить и перетаскивание ячеек (и, желательно, вообще такие пункты меню, как копирование/вставка и т.п.) в ячейках для ввода данных и оставить выделяемыми только эти ячейки?

Заранее большое спасибо!

П.с. Насчет переноса F327 в C327 и появления из-за этого ошибки #ССЫЛКА - это Вы прекрасно подметили! Но интересно, как могли появиться прочие ошибки в данной строке, как то выделение всех границ ячеек жирными рамками, изменение форматов чисел, выделение красным цветом вводимых значений не только в ячейке C327 и т.п.?

П.п.с. Еще столкнулся с необъяснимой ошибкой ранее. Пользователь, предположительно, работал с зажатой посторонним предметом клавишей левый Shift. После этого возникло следующее: во всех незаполненных строках формулы считали значения ТОЛЬКО после сохранения документа, а до сохранения клетки результатов оставались пустыми! Что является возможной причиной данной ошибки? Можно ли заблокировать для пользователя ввод всех команд Excel с клавиатуры?

П.п.п.с. Все строки файла одинаковые, так что и сверху и снизу от 327-й полностью исправные строки.

a_axe 08-07-2014 22:24 2373858
OSWin777, сложно посоветовать что-то толковое.
Цитата:
Цитата OSWin777
в ячейках для ввода данных и оставить выделяемыми только эти ячейки »
Можно потратить время и найти элегантное решения, но лично я бы не парился и разделил бы ваш лист на два - с исхдоными данными и с результатами, для каждого листа доступны свои разрешения:
1. Переименовал бы ваш существующий лист в "Исходные данные"
2. Создал бы копию этого листа и назвал "Результат"
3. На листе "Результат" в ячейках, данные в которые вносит пользователь - сделал бы ссылки на соответствующие ячейки листа "Исходные данные"
4. Убрал бы с листа "исходные данные" все расчетные ячейки, оставил бы только те, в которые пользователь заносит данные.
5. Выставил бы разрешения на оба листа - в листе "Результат" вообще ничего нельзя выделять и менять, на листе "Исходные данные" соответственно запретил что не надо, плюс разрешил бы изменения диапазонов.
6. Защитил бы листы.
7. Возможно - скрыл бы ярлычок листа "Результат" - если пользователь не в состоянии понять, что он делает, ему этот лист вообще не нужен.

Как результат - люди работают с первым листом, результат отображается на втором в полном объеме как у вас было изначально.

На 100% уверен, что в конечном итоге лучше выучить людей работе, все подобные меры все равно не помогают. Думаю, что любому человеку можно объяснить, что вбивать можно только в ячейки белого цвета, если объяснить не получается - это уже клиника какая-то. Ну еще кнопку "проверка данных" нажать из вкладки "Данные", там выбрать любое значение", а в "сообщение для вывода" вбить что-нить ругательное типо "Эта ячейка не белая, поищи белую", и при выделении ячейки пользователь получит предупреждение с этой фразой. Когда-нибудь ему надоест это читать.
Цитата:
Цитата OSWin777
как то выделение всех границ ячеек жирными рамками, изменение форматов чисел, выделение красным цветом вводимых значений »
Цитата:
Цитата OSWin777
работал с зажатой посторонним предметом клавишей левый Shift »
Соответственно, если таскать ячейку за рамку с зажатым шифтом - ячейка перемещается, да еще сдвигает остальные ячейки вправо или вниз (я этого не знал кстати, любопытно), разумеется вместе с форматированием. Соответственно - если перетащить любую ячейку из первого столбца, вместе с ней вы перетащите жирную рамку границы вместе с ячейкой, красный шрифт у вас на листе тоже где-то встречался. Форматирование ваше же родное, просто ячейки перемешаны. Возможно, какие-нибудь сочетания клавиш сработали.
Цитата:
считали значения ТОЛЬКО после сохранения документа
На этот счет даже нет предположений. Кроме того, что уже говорил - лучше научите людей экселю, вам в конечном итоге дешевле выйдет :)

OSWin777 08-07-2014 23:29 2373885
a_axe, большое спасибо за столько интересных предложений!

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

a_axe 09-07-2014 00:55 2373897
OSWin777, на VBA можно достичь того, чего вы хотите.
Код приблизительно следующий:
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
j = Application.ActiveCell.Column
Select Case j
    Case 2 To 5, Is = 7, Is = 8
    i = -1
    Case Is = 12
    i = -1
  Case Else
    i = 0
End Select

ActiveCell.Offset(0, i).Select
End Sub
Вставьте в редакторе VBA в расчетный лист, соответственно в операторе case вставьте через запятую номера столбцов Is=1, где вместо единицы номер столбца, который не должен выделять пользователь. В примере это номера от 2 до 5, 7, 8 и 12.
Выделение будет смещаться влево до первой разрешенной ячейки.

OSWin777 09-07-2014 01:30 2373905
a_axe, супер, большое спасибо!

Как модифицировать скрипт, чтобы не выделялись одновременно строки с 1-й по 5-ю?

А можно использовать данный скрипт для указания любых наборов диапазонов ячеек, не только столбцов или строк?
Мне бы здорово пригодилась такая вещь.
Какая команда на задание диапазона для j, и как указывается диапазон?

П.с. Для чего в скрипте последний столбец указывается отдельно и строка i = -1 дублируется?

Заранее большое спасибо!

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

a_axe 09-07-2014 08:01 2373942
Цитата:
Цитата OSWin777
А можно использовать данный скрипт для указания любых наборов диапазонов »
На VBA можно реализовать практически любую прикладную потребность. Мое знание VBA очень поверхностно, хотя в диапазонах ничего сложного нет. Однако, тут гугл Вам в помощь, поищите, растите над собой)
Цитата:
Цитата OSWin777
Для чего в скрипте последний столбец »
Как пример вам, чтобы было понятно, как вбить номера всех ваших столбцов. Я честно говоря, номера проставил совершенно произвольно.
Цитата:
Цитата OSWin777
не все идеально »
Проблем на самом деле еще больше, код совсем простенький. По сути в процессе работы вылезет много нюансов. Вот вам еще экземпляр, там диапазоны перескакивает, однако при смене направления перехода требуется двойное нажатие на стрелку (то есть если идти все время вправо, а потом нажать влево, нужно два нажатия).
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
j = Application.ActiveCell.Column
Select Case j
   
    Case 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38, 39, 41, 42, 44, 45
    i = 1
 
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i

ActiveCell.Offset(0, i).Select
Jold = Application.ActiveCell.Column
End Sub

OSWin777 09-07-2014 17:06 2374266
Спасибо за код!

Попробовал в перечислении Case указать последнее значение Is >= 46 - получил ошибку.
Также вылезает та же ошибка при установке последним значением диапазона to > 100.
Что я делаю не так?

a_axe 09-07-2014 17:24 2374270
Ну, происходит следующее: когда вы выбираете ячейку с номером 46 или более, она попадает под условие Is>=46. По алгоритму эксель выделяет ячейку правее - номер 47. Она тоже попадает под ваше условие Is>=46. Выбирает 48, 49, 50... и так пока они не кончатся. После этого выпадает в ошибку. Думаю проще модифицировать код так (тут кстати и первые 5 строк учтены, как вы хотели):
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
If Application.ActiveCell.Row <= 5 Then Application.ActiveSheet.Cells(6, Application.ActiveCell.Column).Select
j = Application.ActiveCell.Column
Select Case j
   
    Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38, 39, 41, 42
    i = 1
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i
If j >= 44 Then i = -1
ActiveCell.Offset(0, i).Select
Jold = Application.ActiveCell.Column
End Sub

OSWin777 09-07-2014 18:01 2374293
Спасибо большое за очень полезную для меня информацию по VBA!

Но, видимо, это тупиковый в моем случае путь :(
Ведь получается, что нужно еще прописать в конце таблицы переход на другую строку, да еще в ее начало. А если еще и отдельно прописывать активным диапазон из 2-х белых клеток напротив слова ЗАКАЗ!?
Не слишком ли нецелесообразный код получится всего лишь для замены стандартного функционирования при разграничении выделяемых ячеек на защищенном листе?

Вот если бы можно было написать, например, код, просто запрещающий ВСЕ операции с незащищенными ячейками, кроме ввода данных... Но, скорее всего, это либо невозможно, либо слишком сложно :(

a_axe 09-07-2014 18:29 2374307
Цитата:
Цитата OSWin777
если бы можно было написать, например, код, просто запрещающий ВСЕ операции с незащищенными ячейками, кроме ввода данных... »
Вероятно, это возможно, но как я уже говорил - мои познания этого сделать не позволяют. Соответственно код станет таким:

Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
j = Application.ActiveCell.Column
If j >= 44 Then
Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1, 1).Select

ElseIf Application.ActiveCell.Row <= 5 And Application.ActiveCell.Address <> "$B$3" And Application.ActiveCell.Address <> "$A$3" Then
Jold = Application.ActiveCell.Column
Application.ActiveSheet.Cells(6, 1).Select

Else

Select Case j
   
    Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38, 39, 41, 42
    i = 1
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i
ActiveCell.Offset(0, i).Select
End If

Jold = Application.ActiveCell.Column
End Sub
Старый вариант кода:
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
j = Application.ActiveCell.Column
Select Case j
   
    Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38, 39, 41, 42
    i = 1
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i
If j >= 44 Then
Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1, 1).Select
Else
ActiveCell.Offset(0, i).Select
End If
If Application.ActiveCell.Row <= 5 And Application.ActiveCell.Address <> "$B$3" And Application.ActiveCell.Address <> "$A$3" Then Application.ActiveSheet.Cells(6, Application.ActiveCell.Column).Select
Jold = Application.ActiveCell.Column
End Sub

OSWin777 09-07-2014 18:41 2374308
Ух-ты, спасибо большое!

Не так все сложно, оказывается :). Главное, что такое постепенное усложнение мне наглядно продемонстрировало принципы создания и вложения конструкций в языке - это доходчивей и гораздо быстрее, чем по книге с нуля разбираться!

Остался интересный вопрос, почему в столбце AQ нужно сделать 2 нажатия курсора вправо, чтобы перескочить в строку ниже?

П.с. Разобрался сам: достаточно вместо If j >= 44 прописать If j > 42 !
Кроме того, добавил условие на конец таблицы:
Код:
If Application.ActiveCell.Row > 645 Then Application.ActiveSheet.Cells(645, 1).Select

a_axe 09-07-2014 20:15 2374351
OSWin777, у меня достаточно одного нажатия.
Код я изменил, смотрите пост 13.
Заметил еще вот что: если выделена ячейка В3 и нажать стрелку вправо, выделяется ячейка в 6-ой строке. Если нажать вправо еще раз - выделяется не соседняя ячейка, а через 2 ячейки правее. В новом пустом файле выделяется как положено - соседняя. Никуда не перескакивает. В вашем файле - перескакивает через 2 и вроде нормально дальше работает. В старом варианте кода еще при нажатии влево ячейка возвращалась с А6 на В3, если до этого стояла на В3 (код такого вообще не предусматривает и происходит это до того, как код что-то выбирает на листе). В пустом файле старый код также работает как положено. То ли у меня неучтенная ошибка, то ли в вашем файле.

PS Мда. В файле экселя нужно разъединить ячейка с надписью "Заказ" и "Сумма", тогда код работает корректно.
Их было не выделить из-за кода, и соответственно я не заметил, что они объединены.

OSWin777 09-07-2014 20:46 2374367
У меня, наоборот, с эти кодом работает некорректно: если была выделена ячейка В3, то после перехода стрелкой вправо на А6 дальше движения нет вообще (до этого просто переходила на С6 и двигалась дальше), изменение условия с If j >= 44 на If j > 42 не убирает двойного нажатия на столбце AQ.
Я сдуру удалил у себя предыдущий вариант :(. Можно его снова выложить?

П.с. Я вот переделал в такой вариант:

Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
j = Application.ActiveCell.Column
Select Case j
  Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38 To 42
    i = 1
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i
If j >= 44 Then
Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1, 1).Select
Else
ActiveCell.Offset(0, i).Select
End If
If Application.ActiveCell.Row <= 5 And Application.ActiveCell.Address <> "$B$3" And Application.ActiveCell.Address <> "$A$3" Then Application.ActiveSheet.Cells(3, 1).Select
Jold = Application.ActiveCell.Column
If Application.ActiveCell.Row > 645 Then Application.ActiveSheet.Cells(645, Jold).Select
End Sub
Работает без двойных нажатий, наиболее логично, по-моему. Есть только маленькая проблема с невозможностью выхода с ячеек А3-В3 с помощью курсора. Но и она, вроде, тоже вписывается как раз в логику работы (это спец. ячейки, применяемые не так часто).

П.п.с. Что касается объединения ячеек, то, кстати, как без объединения добиться такого вида представленной в них информации?

OSWin777 10-07-2014 14:22 2374695
Подкорректировал код вверху.
Двойное нажатие на AQ было связано со скрытыми столбцами, совсем забыл про них.

Или так на базе нового кода:
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
j = Application.ActiveCell.Column
If j >= 44 Then
Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1, 1).Select

ElseIf Application.ActiveCell.Row <= 5 And Application.ActiveCell.Address <> "$B$3" And Application.ActiveCell.Address <> "$A$3" Then
Jold = Application.ActiveCell.Column
Application.ActiveSheet.Cells(3, 1).Select

Else

Select Case j
   
    Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38 To 42
    i = 1
  Case Else
    i = 0
End Select
If Jold >= j And j <> 1 Then i = -i
ActiveCell.Offset(0, i).Select
End If

Jold = Application.ActiveCell.Column
If Application.ActiveCell.Row > 645 Then Application.ActiveSheet.Cells(645, Jold).Select
End Sub
Эффект аналогичный, не совсем понятно только, что дает такая "перемена мест слагаемых".

OSWin777 11-07-2014 21:17 2375409
Переделал код с учетом запоминания предыдущих ячеек. Теперь работает почти аналогично стандартной защите ячеек (переучиваться не надо :)) и даже удобнее в ряде моментов.
Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer, i As Integer
Static Jold As Integer
Static Rold As Integer

j = Application.ActiveCell.Column

If j = 44 Then
  Application.ActiveSheet.Cells(Application.ActiveCell.Row + 1, 1).Select
  Jold = Application.ActiveCell.Column
  Rold = Application.ActiveCell.Row

Else
  Select Case j
   
  Case 7, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 27, 29, 30, 32, 33, 35, 36, 38 To 42
    i = 1
  Case Else
    i = 0
  End Select
  If Jold >= j And j <> 1 Then i = -i
  ActiveCell.Offset(0, i).Select
 
  If Application.ActiveCell.Column > 44 Or Application.ActiveCell.Row > 645 Or (Application.ActiveCell.Row <= 5 And Application.ActiveCell.Address <> "$B$3" And Application.ActiveCell.Address <> "$A$3") Then
    If Jold > 0 Then
      Application.ActiveSheet.Cells(Rold, Jold).Select
    Else
      Application.ActiveSheet.Cells(3, 1).Select
    End If
  End If
End If

Jold = Application.ActiveCell.Column
Rold = Application.ActiveCell.Row
End Sub


Время: 20:49.

Время: 20:49.
© OSzone.net 2001-