Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Копировать в excel (откуда угодно) без изменения формата ячеек, Как сохранить форматирование ячейки
 
#1
03.03.2018 14:38:21
Всем доброго времени суток.
Итак, уверен, распространённая проблема, но универсального решения так и не нашёл.
Есть файл excel, подготовленный для заполнения неискушёнными сотрудниками. Этим сотрудникам сложно объяснить, что при копирование любой нужной информации с интернета комбинацией CTRL+C и затем вставка в нужную ячейку CTRL+V меняет желанный формат ячейки, что приводит впоследствии к полнейшей вакханалии в заполняемом файле.
Ещё сложнее объяснить, что чтобы этого избежать, нужно при вставке использовать "вставить значения", либо копировать нужный текст не тупо в ячейку, а в поле её формулы.
Короче, задача сделать так, чтобы условно "бабушки" могли без заморочек пользоваться привычной механикой CTRL+C/CTRL+V и при этом файл не превращался в ад перфекциониста.

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

Я так понимаю, это сейчас решается только макросами на VBA, но я в них не силён, а все, что встречал, написано для каких-то конкретных условий.
Есть ли какой-нибудь простенький универсальный макрос, который просто запрещает менять формат ячеек на всём листе при вставке?
Фаил для примера прикладываю.
 
 
 
#2
03.03.2018 15:00:02
В модуль листа:
Код
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim aa
    If Target.Count > 0 Then
       With Application
            .EnableEvents = False
            aa = .Calculation
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .Undo
            Target.PasteSpecial Paste:=xlPasteValues
            .ScreenUpdating = True
            .Calculation = aa
            .EnableEvents = True
       End With
    End If
End Sub
 
 
 
#3
03.03.2018 15:09:44
Доброе время суток.
Anchoret, с одной стороны - формулы массово не введёшь, с другой - можно же и одну ячейку что-попало вставить, учитывая ограничение If Target.Count > 0
Ещё, вариант, на том же приёме от Владимира (ZVI).
Код
Private Function hasFormulas(ByVal source As Range) As Boolean
On Error GoTo errHandle
    hasFormulas = source.SpecialCells(xlCellTypeFormulas).Count > 0
Exit Function
errHandle:
    hasFormulas = False
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not hasFormulas(Target) Then
        Dim vData
        vData = Target.Value
        Application.EnableEvents = False
        Application.Undo
        Target.Value = vData
        Application.EnableEvents = True
    End If
End Sub
 
 
 
#4
03.03.2018 15:14:36
Андрей VG, спасибо за вариант) Только "условные бабушки" врядли будут играться с формулами. Так мне думается...
 
 
 
#5
03.03.2018 15:20:39
Anchoret,Андрей VG,
господа, спасибо, только не понял, в чём отличие ваших макросов?
 
 
 
#6
03.03.2018 15:25:36
Anchoret, при вставке вашего макроса в код листа, при вставке новой строки почему-то вылазит ошибка:
Run-time error '424':
Object required
 
 
 
#7
03.03.2018 15:25:38
bobrovantig, первый отменяет вставку чего-либо и вставляет только значения из буфера. Второй проверяет на наличие формул, если это не они то делает по первому варианту.
 
 
 
#8
03.03.2018 15:28:36
Андрей VG, с этим макросом аналогичная ошибка. Я что-то не так делаю?
 
 
 
#9
03.03.2018 15:31:05
Цитата
bobrovantig написал:
Я что-то не так делаю?
Для того, чтобы ответить на этот вопрос, нужен файл, в котором видно, что вы сделали.
 
 
 
#10
03.03.2018 15:40:45
Андрей VG,
Вот два файла, куда вставил макросы.
В один файл вставил, нажав "просмотреть код" на листе. В этом случае ошибка не выскакивает, но и макрос не работает (проверил на примере копирования красных слов с сайта http://www.gorjkh.gomel.by/) - вставляет огромные красные буквы, а не так, как надо:)
В другой файл вставил, нажав на "разработчик-visual basic-insert-module". В это случае выскакивает ошибка.

А вообще есть разница, каким образом макрос вставлять? Я просто не особо в этом разбираюсь.
 
 
 
#11
03.03.2018 15:45:33
Цитата
bobrovantig написал:
В один файл вставил, нажав "просмотреть код" на листе. В этом случае ошибка не выскакивает,
Тогда, не знаю, что не так. Скачал, вставил кусок с форума - всё Ок.
 
 
 
#12
03.03.2018 15:53:37
Андрей VG,
А если в вашем файле этом вы строчку вставляете где-нибудь между существующими в таблице наверху, например, у вас ошибка не выскакивает?
 
 
 
#13
03.03.2018 16:04:37
Наверное, надо так модифицировать:
Код
Private Function hasFormulas(ByVal source As Range) As Boolean
On Error GoTo errHandle
    hasFormulas = source.SpecialCells(xlCellTypeFormulas).Count > 0
Exit Function
errHandle:
    hasFormulas = False
End Function
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not hasFormulas(Target) Then
        If Application.CutCopyMode > 0 Then
            Dim vData
            vData = Target.Value
            Application.EnableEvents = False
            Application.Undo
            Target.Value = vData
            Application.EnableEvents = True
        End If
    End If
End Sub

Хотя в таком виде "откуда угодно" не сработает, т.к. используется общий буфер...Хотя на форуме и такое обсуждалось - можно поиском найти. Более-менее рабочее решение было найдено. Но это всегда костыли.
Изменено: Дмитрий Щербаков - 03.03.2018 16:09:19
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#14
03.03.2018 16:06:11
Кажется уловил суть проблемы. Поставил заглушку на проверку типа Target. Почему то со сложных сайтов он бывает Nothing. Пробуйте.
P. S. Правда, если помимо текстовых данных будут и картинки скопированы, то они пропадут. Их тогда по отдельности через специальную вставку - Рисунок.
 
 
 
#15
03.03.2018 16:49:42
Цитата
bobrovantig написал:
при вставке новой строки почему-то вылазит ошибка
Я не знаю почему она не у всех вылезает, но вставка строки, в т. ч. и скопированной, это событие Worksheet_Change.
В коде есть vData = Target.Value (пока все хорошо).
Но Application.Undo... И где теперь Target?
А мы хотим Target.Value = vData, а куда? Вот здесь ошибка.
Но при этом Target не равна Nothing, кроме того VarType = 9, а TypeName = "Range"...
Т.е. отловить это можно, как я это обычно делаю, On error resume next и т. д. по алгоритму.
В данном случае просто не произойдет вставка.
 
 
 
#16
03.03.2018 16:59:44
Del
Изменено: Anchoret - 03.03.2018 17:19:04
 
 
 
#17
03.03.2018 17:02:47
Anchoret, в данном случае при "вставить скопированные ячейки" вставленное заместит данные строки которая была выделенной...
Изменено: AAF - 03.03.2018 17:04:46
 
 
 
#18
03.03.2018 17:06:46
Коллеги, спасибо за подсказку! Как-то подзабыл, что Range весьма виртуальный объект.
Цитата
AAF написал:
в данном случае при "вставить скопированные ячейки" вставленное заместит данные строки которая была над выделенной...
Это как?
 
 
 
#19
03.03.2018 17:09:39
Я про последний код от Anchoret
Он запомнил адрес, targetа уже нет, там другая строка, вот ее данные и заместит.
Цитата
AAF написал:
данные строки которая была выделенной...
Я там исправил :)
Просто если делалось undo, то верните то что взяли, а то проблемы... :)
Изменено: AAF - 03.03.2018 17:21:58
 
 
 
#20
03.03.2018 17:22:17
AAF, да, только через On Error. Удалил свои кракозябры)
 
 
 
#21
03.03.2018 17:24:26
Anchoret, просто автору темы надо запретить вставку/удаление ячеек, что весьма не сложно и OK.
 
 
 
#22
03.03.2018 17:30:11
Цитата
Anchoret написал:
да, только через On Error. Удалил свои кракозябры)
On Error - не нужен. Просто чуть подправить
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not hasFormulas(Target) Then
        Dim vData, sAddress As String
        sAddress = Target.address
        vData = Target.Value
        Application.EnableEvents = False
        Application.Undo
        Me.Range(sAddress).Value = vData
        Application.EnableEvents = True
    End If
End Sub

ТС не хотел запрещать.
Изменено: Андрей VG - 03.03.2018 17:31:15
 
 
 
#23
03.03.2018 17:32:37
Андрей VG, то же что описано в #19
Цитата
Андрей VG написал:
ТС не хотел запрещать
Тогда надо отслеживать.
Изменено: AAF - 03.03.2018 17:35:22
 
 
 
#24
03.03.2018 17:34:52
Цитата
AAF написал:
то же что описано в #19
Вы уверены, что столь скупой комментарий ТС одолеет, учитывая
Цитата
bobrovantig написал:
но я в них не силён, а все, что встречал, написано для каких-то конкретных условий.
Что переводится - знать не знаю и знать не хочу.
 
 
 
#25
03.03.2018 17:38:12
В данном случае не произойдет вставка, но ошибка не выпрыгнет.
Код
Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Not hasFormulas(Target) Then
        Dim vData, sAddress As String
        vData = Target.Value
        Application.EnableEvents = False
        Application.Undo
        on error resume next
        Target.Value = vData
        Application.EnableEvents = True
    End If
End Sub
Изменено: AAF - 03.03.2018 18:28:05
 
 
 
#26
03.03.2018 17:52:01
Присоединяюсь к мнению AAF! Нужно уговорить автора темы снять признак защиты с определенных ячеек (не формул) и защитить лист (разрешив выделение и форматирование ячеек). После этого указанные выше макросы надежно заработают (и проверка на наличие формул не нужна). Иначе придется отдельно бороться с удалением/вставкой строк (недавно ZVI показал, как подсматривать список действий для Undo), диапазонов ячеек со сдвигом и т.п.
Владимир
 
 
 
#27
03.03.2018 18:01:53
Цитата
AAF написал:
Target.Value = vData
Коллега, а често ли приводить это в коде после
Цитата
AAF написал:
А мы хотим Target.Value = vData, а куда?

Цитата
sokol92 написал:
Нужно уговорить автора темы
Привет, Владимир.
Ах, если бы. Извечное пользовательское - мне так удобнее, что важнее, чем правильнее.
Изменено: Андрей VG - 03.03.2018 18:04:25
 
 
 
#28
03.03.2018 18:06:21
sokol92, интересно, спасибо.
Для ряда случаев подойдет, но к сожалению не дает инфы о том как были вставлены ячейки.
1. всей строкой
2. часть со сдвигом вверх/вправо
Тогда можно было бы решить проблему

Цитата
Андрей VG написал:
а често ли
после on error resume next вполне :)
Изменено: AAF - 03.03.2018 18:09:31
 
 
 
#29
03.03.2018 18:11:53
Цитата
AAF написал:
после on error resume next вполне
Зачем нужно использовать недействительный Target?
 
 
 
#30
03.03.2018 18:13:46
Андрей VG, обязательно ли писать if err.number=0 then  Target.Value = vData ?
Ой, не то имел ввиду... Затормозил.
Тогда надо определять, что Target не действителен. Какие варианты?
Цитата
AAF написал:
при этом Target не равна Nothing, кроме того VarType = 9, а TypeName = "Range"...
Изменено: AAF - 03.03.2018 18:20:48
 
 
 
Страницы: 1 2 След.
Наверх