[Решено] Защита "от дурака" в Excel

[OSWin777]

Здравствуйте!

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

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

[a_axe]

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

[a_axe]

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

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

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

[OSWin777]

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

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

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

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

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

[a_axe]

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

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

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

[OSWin777]

a_axe, большое спасибо за столько интересных предложений!

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

[a_axe]

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]

a_axe, супер, большое спасибо!

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

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

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

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

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

[a_axe]

А можно использовать данный скрипт для указания любых наборов диапазонов »
На VBA можно реализовать практически любую прикладную потребность. Мое знание VBA очень поверхностно, хотя в диапазонах ничего сложного нет. Однако, тут гугл Вам в помощь, поищите, растите над собой)Для чего в скрипте последний столбец »
Как пример вам, чтобы было понятно, как вбить номера всех ваших столбцов. Я честно говоря, номера проставил совершенно произвольно.не все идеально »
Проблем на самом деле еще больше, код совсем простенький. По сути в процессе работы вылезет много нюансов. Вот вам еще экземпляр, там диапазоны перескакивает, однако при смене направления перехода требуется двойное нажатие на стрелку (то есть если идти все время вправо, а потом нажать влево, нужно два нажатия).
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]

Спасибо за код!

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

[a_axe]

Ну, происходит следующее: когда вы выбираете ячейку с номером 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]

Спасибо большое за очень полезную для меня информацию по VBA!

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

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

[a_axe]

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

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]

Ух-ты, спасибо большое!

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

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

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

[a_axe]

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

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

[OSWin777]

У меня, наоборот, с эти кодом работает некорректно: если была выделена ячейка В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]

Подкорректировал код вверху.
Двойное нажатие на 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]

Переделал код с учетом запоминания предыдущих ячеек. Теперь работает почти аналогично стандартной защите ячеек (переучиваться не надо :)) и даже удобнее в ряде моментов.
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