Уроки Excel 2007 для чайников

Наверняка вы видели и заполняли различные формы. То есть в небольшой панельке в нужные окошки записываете данные, потом нажимаете ОК или «Добавить», и эти данные сами встают на нужные места.

Точно так же формой можно воспользоваться при заполнении электронной таблицы.

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

На листе книги лучше помещать только одну таблицу.

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

В самой таблице пустых столбиков и строк быть не должно.

Заголовки лучше выделить шрифтом, написанием, цветом, выравниваем. Заголовки должны по формату отличаться от данных.

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

Перед данными в таблице не нужно делать лишние пробелы.

А теперь приступаем.

Наберите заголовки столбиков. Заполните первую строчку данными.

Кнопки «Форма» вы в меню не найдете. Ее надо вывести на панель быстрого доступа. Эта панель находится рядом с кнопкой «Office».

Щелкните левой кнопкой мыши на кнопке «Office».

Появится панель. В самом низу есть кнопка «ПараметрыExcel». Щелкните на этой кнопке левой кнопкой мыши.

Появится панель «ПараметрыExcel». В левой части этой панели есть список. Выберите из него команду «Настройки» и щелкните на ней левой кнопкой мыши.

Правая часть панели «ПараметрыExcel» изменится. Там будет заголовок «Настройка панели быстрого доступа». Вот именно сюда нам и надо.

Теперь установите настройки. В списке «Выбрать команды из» выберите пункт «Все команды» и щелкните на нм левой кнопкой мыши.

В левом списке-столбике появятся все возможные команды. Они расположены по алфавиту. Прокрутите этот список до команды «Форма». Щелкните левой кнопкой мыши на этой команде, а потом щелкните на кнопке «Добавить». Она расположена справа от списка.

Команда «Форма» добавится в список «Настройка панели быстрого доступа». который расположен справа. Проверьте еще, чтобы под названием списка было выбрано «Для всех документов».

В правом нижнем углу панели «ПараметрыExcel» есть кнопка ОК. Щелкните на ней левой кнопкой мыши. Панель закроется.

Теперь проверьте панель быстрого доступа. На ней должна появиться кнопка «Форма».

Щелкните левой кнопкой мыши на первой ячейке новой строки вашей таблицы.

Щелкните на кнопке «Форма». На экране появится форма, которая была создана по вашей таблице. У меня она получилась такая:

Вы видите, что все поля в форме уже заняты теми данными, которые вы записали в первую строку. Нажмите кнопку «Добавить». Форма очистится, а в таблице пока никаких изменений не произойдет.

Запишите в нужные окошки новые данные. Для перехода из поля в поле удобно пользоваться кнопкой Tab .

Снова щелкните на кнопке «Добавить». Новая запись встанет в таблицу.

Вместо кнопки «Добавить» можно пользоваться клавишей Enter .

Теперь давайте разберемся с кнопками на форме.

Кнопка «Добавить» добавит в таблицу новую запись. При этом форма остается открытой. В нее можно записывать новые данные.

Кнопка «Закрыть» добавит в таблицу новую запись. Форма закроется.

Кнопка «Удалить» удалит запись из формы и из таблицы.

Кнопка «Далее» будет показывать в форме каждую следующую запись, то есть переходить на одну запись вперед.

Кнопка «Назад» будет показывать в форме предыдущую запись, то есть переходить на одну запись назад.

Если у вас таблица получилась очень большая, то для выбора нужной записи можно воспользоваться кнопкой «Критерии». Щелкните на этой кнопке левой кнопкой мыши. Форма немножко изменится. У нее появится подзаголовок «Критерии».

Если вы передумали пользоваться критериями для отбора, нажмите кнопку «Правка».

Запишите в форму нужные критерии для отбора, например фамилию или номер дома. Чтобы в форме выводились отобранные записи, нажимайте кнопки «Далее» или «Назад».

Мир MS Excel

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

Я уже задавал вопрос по подобной проблеме, эта проблема вроде и похожа, но немного другая.
Есть книга, в ней 3 листа.
На второй книге имеется около 70 штучек самостоятельных таблиц, и при заполнении их, пользователь очень много времени использует на поиск нужной таблицы. Да и количество таблиц со временем растет (дополняется). Решил использовать макросы или формы. Но как оказалось, это не так-то и просто.

Как я вижу решение данной задачи.
1. На первой странице разместить кнопки с запуском форм для заполнения конкретной таблицы, Форма1 заполняет Таблицу1 и т.д.
2. Любая таблица может быть заполнена на любое количество строк, в любой день (сегодня внес в каждую таблицу по 5 строк, завтра в первые две по 3 строки, а там в первую и третью по одной и т.д.), каждое последующее заполнение, идет как продолжение предыдущих.
3. При заполнении, в каждой таблице есть ячейка дата, но это не текущая дата, а её нужно вносить по всплывающему календарику.
4. Есть ячейки с Данными (здесь идут произвольные данные там числа или текст введённый в ячейку формы).
5. Ну и ячейки с Выбором. Здесь нужно вносить лишь определённые значения (Наборы этих значений имеются на листе 3), я так понимаю что при нажатии на окно в форме, появлялось выезжающее меню с выбором данных.

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

7. Возможно ли в последующем просмотреть формой таблицу (форма для заполнения и форма для просмотра одна и та же?)

8. Форма для редактирования. (при внесении данных была допущена ошибка, можно ли перейти на ту строчку и исправить, или нужно будет в таблице искать и исправлять?)

Пример прилагается, но он на 4 таблицы.

Вот такая задача поставлена, но как её решить пока нет ни малейшего понятия.

Сообщение отредактировал serg-3-1977 - Среда, 04.03.2015, 11:44

Походу тема неподъёмная!

Так хоть поэтапно, или как отдельные элементы делать!

Можно вместо форм сделать гиперссылку на каждую таблицу. И не заморачиваться с формами.

Формы программировать не сложно, только муторно их ещё и нарисовать надо. Сделала вам маленький пример на три поля первой таблицы. Календарь отсюда.
Можете попробовать сделать остальное по аналогии. Что не понятно спрашивайте.

Как в VBA Excel сделать автоматизацию заполнения списка заказа в фирме, занимающейся ремонтом - Трюки и приемы в Microsoft Excel

Будем считать, что наша условная организация занимается поставками покупателям дорогостоящей строительной техники. Кроме непосредственно поставок, еще один из участков деятельности связан с ее ремонтом. Разрабатываемая далее книга Microsoft Excel будет включать несколько листов данных и несколько листов управления. Исходная ситуация такова: мы располагаем рядом клиентов (заказчиков), которые обращаются к нам по поводу ремонта проданной им техники (ремонт в течение гарантийного срока является вполне нормальным делом).

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

Итак, начнем работу с создания новой рабочей книги Microsoft Excel. Один из ее листов показан на рис. 3.1. В нем размещается справочная информация по нашим заказчикам (клиентам). Каждая строка на листе Клиенты представляет запись об одной из наших организаций-партнеров. Обратим внимание на то, что в дальнейшем в программных процедурах обращение к данному листу будет производиться по имени, поэтому следует присвоить ему имя Клиенты. Столбец Код предназначен для присвоения каждой фирме уникального кода (для того, чтобы однозначно идентифицировать каждую фирму). В целом информация, представленная в строках листа (см. рис. 3.1), достаточно стандартная: название, адрес, телефон, факс и ряд финансовых реквизитов.

Рис. 3.1. Справочная информация о клиентах

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

К кнопке ввода нового клиента мы еще вернемся, а пока разберем следующий шаг, связанный с созданием формы ввода, которая приведена на рис. 3.2. От пользователя требуется внести данные в необходимые поля и щелкнуть на кнопке Внести для фиксации введенной информации на листе Клиенты. Это приведет к тому, что в очередную свободную строку листа будут внесены сведения по новой фирме. При этом процедура обработки щелчка на кнопке Внести предварительно проверит, встречался ли уже такой код у фирм. Если да, то ввод будет отменен. Также ввод будет отменен, если пользователь оставит поле пустым. Кроме того, процедура посмотрит: не является ли данная запись дублирующей (если уже имеется строка, включающая данное название фирмы, а также указанный адрес). И в этом случае ввод информации на лист Клиенты будет отменен.

Рис. 3.2. Форма ввода информации о клиентах

Таким образом, наша ближайшая цель — создать форму ввода (рис. 3.2) и расположить на ней необходимые элементы управления. С формами мы еще не сталкивались, поэтому рассмотрим процесс их создания более подробно. Для создания пользовательской формы необходимо перейти в окно редактора Visual Basic. Здесь требуется воспользоваться разделом UserForm в меню Insert (рис. 3.3). В результате на экране появится новая форма, которая фактически представляет собой контейнер для размещения на ней необходимых элементов управления.

Рис. 3.3. Пользовательская форма в среде Microsoft Visual Basic

У формы, как и у любого элемента управления, есть свойства. Для того чтобы открыть окно свойств, следует воспользоваться разделом Properties Window из меню View. Изменим значение Name на Client. а также значение свойства Caption — Форма для ввода нового клиента. В результате в заголовке формы будет отражен новый текст. Что касается свойства Name. то оно нам понадобится при работе с формой (при активизации формы).

Далее расположим на форме необходимые элементы управления. Для этого сначала необходимо отобразить на экране панель инструментов (View > Toolbox). В соответствии с рис. 3.2 на форме нам следует разместить 7 элементов управления типа Label (надпись) и 7 элементов TextBox (текстовое окно). При этом значения свойства Name для надписей принципиального значения не имеют, так как программно мы обращаться к ним не будем. Для этих элементов выберите рассматриваемые значения произвольно (главное, чтобы не было повторяющихся имен — иначе среда Microsoft Visual Basic обратит на это внимание). Аналогичное свойство для текстовых окоп лам потребуется в программных процедурах, поэтому в табл. 3 приведены значения свойства Name текстовых окон, которые следует установить.

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

' Листинг 3.1. Обработка щелчка на кнопке Внести на форме Private Sub CommandButton1_Click() If Cod.Text = "" Then MsgBox ("Поле КОД необходимо заполнить") Exit Sub End If Nom = 0 While Worksheets("Клиенты").Cells(Nom + 2, 2).Value <> "" Nom = Nom + 1 Wend For i = 1 To Nom If CStr(Worksheets("Клиенты").Cells(i + 1, 2).Value = _ CStr(Firma.Text) And _ CStr(Worksheets("Клиенты").Cells(i + 1, 3).Value) = _ CStr(Adress.Text)) Then MsgBox ("Такой код фирмы уже встречался") Exit Sub End If Next Worksheets("Клиенты").Cells(i + 1, 1).Value = Cod.Text Worksheets("Клиенты").Cells(i + 1, 2).Value = Firma.Text Worksheets("Клиенты").Cells(i + 1, 3).Value = Adress.Text Worksheets("Клиенты").Cells(i + 1, 4).Value = Tel.Text Worksheets("Клиенты").Cells(i + 1, 5).Value = Fax.Text Worksheets("Клиенты").Cells(i + 1, 6).Value = Inn.Text Worksheets("Клиенты").Cells(i + 1, 7).Value = Kpp.Text MsgBox ("Информация внесена") Client.Hide End Sub

Новый момент связал с предпоследней строкой текста в листинге 3.1. Здесь для формы Client применяется метод Hide. который приводит к закрытию этой формы. Разумеется, ее необходимо сначала отобразить, и об этом мы еще не упоминали. В листинге 3.2 приведена процедура, которая выполняется по щелчку на кнопке Ввести нового клиента (см. рис. 3.1). Здесь для отображения на экране формы Client используется метод Show .

' Листинг 3.2. Обработка щелчка на кнопке Ввести нового клиента Private Sub CommandButton1_Click() Client.Show End Sub

На рис. 3.4 приведен результат заполнения формы данными об очередной фирме. Теперь щелчком на кнопке Внести это данные переносятся на текущий рабочий лист (в очередную свободную строку).

Рис. 3.4. Внесение информации о новом клиенте

Перейдем к разработке еще двух листов. Один из них под названием Номенклатура показан на рис. 3.5. В каждой строке листа располагается название конкретной запасной части. Значение в столбце Номер запчасти позволяет однозначно ее идентифицировать.

Рис. 3.5. Информация о номенклатуре

Лист Номенклатура является справочным и не содержит элементов управления. Технически пользователь просто вручную заполняет данный прайс-лист. Еще один лист, который также не содержит элементов управления, показан на рис. 3.6. Здесь содержится информация о заказах — названиях и их составе. Столбец А предназначен для уникального кода каждого заказа. Далее располагаются поля для отображения даты заказа и кода фирмы (по коду фирмы легко определить необходимые реквизиты организации).

Рис. 3.6. Лист для хранения информации о заказах

Начиная с пятого столбца размещается информация о запасных частях, которые входят в заказ. Структура этой информации выглядит следующим образом: в пятом столбце код запчасти, далее в шестом количество таких запчастей в заказе, затем в седьмом столбце код другой запчасти, и далее их количество. Таким образом, в каждой строке перечисляется все содержимое определенного заказа. Понятно, что вручную заполнять подобную информацию не слишком удобно. Для этого мы создадим специальный лист с необходимыми элементами управления. В результате будет обеспечено удобство для пользователя, а также контроль уникальности кода заказа. Лист, который будет выполнять необходимый «функционал», показан на рис. 3.7. В качестве названия листа выберем Бланк для нового заказа .

Рассмотрим организацию листа Бланк для нового заказа и техническую работу пользователя с ним. Ячейка C1 отводится для номера заказа. Ниже поясняющего текста Заказчик (расположенного в ячейке А1) располагается элемент управления «Поле со списком». В качестве значения свойства Name этого элемента управления выбрано Firma. Ниже поясняющего текста Список запчастей располагается еще один элемент управления «Поле со списком». В качестве значения свойства Name выбрано Spk .

Щелчком на определенной запчасти она включается в заказ, который формируется с 11-й строки на данном листе. Столбцы Номер запчасти. Наименование и Цена заполняются исходя из имеющейся информации на листе Номенклатура. Пользователю следует лишь внести количество единиц указанной детали в перечне запасных частей. Для этого предназначено текстовое окно (Name — Col) с надписью выше — Количество. После этого осталось щелкнуть на кнопке Включить (Name — InputNom), и в очередную свободную строку на данном листе запишется новая позиция заказа.

Рис. 3.7. Лист для формирования заказа

После того как список запасных частей таким образом заполнен и номер заказа указан, осталось щелкнуть на кнопке Включить в список заказов. В качестве значения свойства Name этой кнопки выбрано InputSpk. За счет программной процедуры это приводит к переносу информации о заказе на лист Названия заказов. Таким образом, теперь можно перейти к рассмотрению процедур, которые обеспечивают выполнение описанных действий. Когда пользователь в процессе работы с книгой переходит на лист Бланк для нового заказа. списки заказчиков и запасных частей должны быть заполнены. Проще всего это обеспечить с помощью процедуры, выполняемой при активизации листа, которая представлена в листинге 3.3.

' Листинг 3.3. Процедура, выполняемая при активизации листа Private Sub Worksheet_Activate() Firma.Clear N = 0 While Worksheets("Клиенты").Cells(N + 2, 1).Value <> "" N = N + 1 Wend For i = 1 To N Firma.AddItem Worksheets("Клиенты").Cells(i + 2, 2).Value Next Spk.Clear N = 0 While Worksheets("Nomen").Cells(N + 2, 1).Value <> "" N = N + 1 Wend For i = 1 To N Spk.AddItem Worksheets("Nomen").Cells(i + 1, 1).Value + _ "" + Worksheets("Номенклатура").Cells(i + 1, 2).Value + "" + _ CStr(Worksheets("Номенклатура").Cells(i + 1, 3).Value) + "руб." Next End Sub

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

' Листинг 3.4. Процедура обработки щелчка на кнопке Включить Private Sub InputNom_Click() Nom = Spk.ListIndex N = 0 While Cells(N + 11, 1).Value <> "" N = N + 1 Wend Cells(N + 11, 1) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 1) Cells(N + 11, 2) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 2) Cells(N + 11, 3) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 3) Cells(N + 11, 4) = Col.Text End Sub

Здесь в переменную Nom заносится индекс (номер) элемента, который выделен в элементе управления «Поле со списком», отведенном для запасных частей. Далее подсчитывается число уже заполненных позиций в заказе — они располагаются начиная с 11-й строки. После этого в следующую свободную строку заносится очередная запись. При этом количество единиц указанной позиции заказа извлекается из содержимого текстового окна Col .

Теперь на очереди другая процедура (листинг 3.5), которая переносит информацию с листа Бланк для нового заказа на лист Названия заказов. о котором мы уже говорили. В начале процедуры InputSpk_Click() предварительно проверяется — внесена ли информация в ячейку C1. Далее производится поиск присутствия заказа с указанным на листе номером. В случае положительного ответа на этот вопрос процедура не производит запись, а возвращает пользователя к работе с листом формирования нового заказа. В случае уникальности нового номера заказа процедура последовательно переносит имеющуюся информацию в каталог заказов. Для формирования даты используется стандартная функция VBA Date.

' Листинг 3.5. Процедура обработки щелчка на кнопке Включить в список заказов Private Sub InputSpk_Click() ' Номер потенциального заказа VerZakaz = CStr(Range("C1").Value) If VerZakaz = "" Then MsgBox ("Поле кода заказа необходимо заполнить") Exit Sub End If If Firma.ListIndex = -1 Then MsgBox ("Необходимо указать фирму") Exit Sub End If ' Извлечение информации о коде фирмы CodFirma = Worksheets("Клиенты").Cells(Firma.ListIndex + 2, 1).Value ' Подсчет числа имеющихся заказов N = 0 While Worksheets("Названия заказов").Cells(N + 2, 1).Value <> "" N = N + 1 Wend ' Проверка возможного повтора номера заказа For i = 1 To N CodList = Worksheets("Названия заказов").Cells(i + 1, 1).Value If CStr(CodList) = VerZakaz Then MsgBox ("Такой номер заказа уже встречался") Exit Sub End If Next ' Запись информации о данном заказе Worksheets("Названия заказов").Cells(N + 2, 1).Value = Range("C1").Value ' Использование стандартной функции для получения даты Worksheets("Названия заказов").Cells(N + 2, 2).Value = Date Worksheets("Названия заказов").Cells(N + 2, 3).Value = CodFirma ' Вводим переменную для подсчета суммы NDetal = 0 While Cells(NDetal + 11, 2).Value <> "" NDetal = NDetal + 1 Wend ' Подсчет числа деталей в заказе SymmaItog = 0 For i = 1 To NDetal Worksheets("Названия заказов").Cells(N + 2, 5 + (i - 1) * 2).Value = _ Cells(i + 10, 1).Value Worksheets("Названия заказов").Cells(N + 2, 5 + (i - 1) * 2 + 1).Value = _ Cells(i + 10, 4).Value SymmaItog = SymmaItog + CLng(Cells(i + 10, 4).Value) * _ CLng(Cells(i + 10, 3).Value) Next Worksheets("Названия заказов").Cells(N + 2, 4).Value = SymmaItog MsgBox ("Заказ включен") End Sub

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

Рис. 3.8. Пример формирования заказов

Перейдем к отчетам, и один из них показан на рис. 3.9. На этом листе часть информации статична (заголовок и подписи столбцов), а ряд ячеек заполняется исходя из содержания конкретного заказа, которое зафиксировано на листе Названия заказов .

Рис. 3.9. Отчет о заказе

Нам требуется программно обеспечить заполнение ячеек листа, отводимых для номера заказа, заказчика, а также заполнение адреса, телефона и факса. Табличная часть на рис. 3.9 используется в случае, если число позиций в заказе не превышает трех. В противном случае данная табличная часть не заполняется, а создается другой документ — Приложение (рис. 3.10). Наша задача — обеспечить данные функциональные особенности разрабатываемой книги.

Рис. 3.10. Приложение к отчету о заказе

Для заполнения отчета о заказе (и при необходимости приложения к нему) па листе Названия заказов создадим кнопку Создать отчет (см. рис. 3.8). По нажатию этой кнопки будет открываться форма, где от пользователя требуется выбрать заказ, по которому необходимо сформировать отчет. На рис. 3.11 показана данная форма ввода в окне редактора Visual Basic.

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

' Листинг 3.6. Процедура обработки щелчка на кнопке Создать отчет Private Sub CommandButton1_Click() Zakaz.Show End Sub

Рис. 3.11. Форма выбора заказа для отчета

В листинге 3.7 приведена процедура, которая выполняется при активизации формы. Ее назначение заключается в заполнении элемента управления «Поле со списком». При этом сначала подсчитывается количество заказов, уже имеющихся в базе данных. Информация о том, какая фирма его сделала, расположена на листе Клиенты. Поэтому для заполнения поля со списком на рис. 3.11 необходимо обратиться к информации о клиентах.

' Листинг 3.7. Процедура, выполняемая при активизации формы Private Sub UserForm_Activate() ' Подсчет числа заказов Nom = 0 While Worksheets("Названия заказов").Cells(Nom + 2, 1).Value <> "" Nom = Nom + 1 Wend ' Подсчет числа фирм Nfir = 0 While Worksheets("Клиенты").Cells(Nfir + 2, 1).Value <> "" Nfir = Nfir + 1 Wend ' Очистка и последующее заполнение поля со списком Spk.Clear ' Перебор числа заказов For i = 1 To Nom ' Извлечение кода фирмы NomFirma = Worksheets("Названия заказов").Cells(i + 1, 3).Value For j = 1 To Nfir If NomFirma = Worksheets("Клиенты").Cells(j + 1, 1).Value Then Firma = Worksheets("Клиенты").Cells(j + 1, 2).Value ' При нахождении фирмы выход из цикла Exit For End If Next Spk.AddItem CStr(Worksheets("Названия заказов").Cells(i + 1, 1).Value) _ + " " + CStr(Worksheets("Названия заказов").Cells(i + 1, 2).Value) _ + " " + CStr(Firma) Next End Sub

Теперь пользователь должен выбрать интересующую его фирму (рис. 3.12). Следующая процедура — обработка щелчка на кнопке Заполнить акт и приложение. Учитывая сложность полного программного кода, приведем его сначала для варианта заполнения только листа АКТ (листинг 3.8).

Рис. 3.12. Выбор заказа для отчета

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

' Листинг 3.8. Процедура обработки щелчка на кнопке Заполнить акт и приложение Private Sub CommandButton1_Click() ' Определяем индекс выбранного заказа в списке NomSpk = Spk.ListIndex ' Подсчет количества деталей в выбранном заказе ColDet = 0 While Worksheets("Название заказа").Cells(NomSpk + 2, _ 5 + ColDet * 2).Value <> "" ColDet = ColDet + 1 Wend ' Подсчет количества позиций деталей по прайсу NPrais = 0 While Worksheets("Номенклатура").Cells(NPrais + 2, _ 1).Value <> "" NPrais = NPrais + 1 Wend ' Подсчет числа фирм NFirm = 0 While Worksheets("Клиенты").Cells(NFirm + 2, 1).Value <> "" NFirm = NFirm + 1 Wend ' Извлекаем код фирмы в выбранном заказе CodFirm = Worksheets("Название заказа").Cells(NomSpk + 2, 3).Value For i = 1 To NFirm ' Нахождение реквизитов фирмы по ее коду If CodFirm = Worksheets("Клиенты").Cells(i + 1, 1).Value Then Название заказа = Worksheets("Клиенты").Cells(i + 1, 2).Value Adr = Worksheets("Клиенты").Cells(i + 1, 3).Value Tel = Worksheets("Клиенты").Cells(i + 1, 4).Value Fax = Worksheets("Клиенты").Cells(i + 1, 5).Value Exit For End If Next ' Внесение найденных реквизитов в поля на листе АКТ Worksheets("Акт").Cells(6, 3).Value = Nazv Worksheets("Акт").Cells(7, 3).Value = "ADRES:" + Adr Worksheets("Акт").Cells(8, 3).Value = "telefon:" + Tel Worksheets("Акт").Cells(9, 3).Value = "phaks:" + Fax Worksheets("Акт").Cells(6, 5).Value = _ Worksheets("Название заказа").Cells(NomSpk + 2, 1) ' Очистка области для перечня запасных частей Worksheets("Акт").Range("A13:F15") = "" ' Заполнение табличной части листа АКТ If ColDet < 4 Then For i = 1 To ColDet ' Формирование порядкового номера перечня Worksheets("Акт").Cells(i + 12, 1).Value = i Worksheets("Акт").Cells(i + 12, 2).Value = _ Worksheets("Название заказа").Cells(NomSpk + 2, _ 5 + (i - 1) * 2).Value For j = 1 To NPrais If CStr(Worksheets("Акт").Cells(i + 12, 2).Value) = _ CStr(Worksheets("Номенклатура").Cells(j + 1, 1).Value) Then Nazvanie = CStr(Worksheets("Номенклатура").Cells(j + 1, 2).Value) Tarif = CStr(Worksheets("Номенклатура").Cells(j + 1, 3).Value) Exit For End If Next Worksheets("Акт").Cells(i + 12, 3).Value = Nazvanie Worksheets("Акт").Cells(i + 12, 5).Value = Tarif Worksheets("Акт").Cells(i + 12, 4).Value = _ Worksheets("Название заказа").Cells(NomSpk + 2, _ 5 + (i - 1) * 2 + 1).Value Worksheets("Акт").Cells(i + 12, 6).Value = Tarif * _ Worksheets("Акт").Cells(i + 12, 4).Value Next End If Hide End Sub

На рис. 3.9 мы уже видели заполненный лист АКТ для небольшого (не превышающего трех) числа запасных частей в заказе. Следует обратить внимание на ряд фрагментов процедуры, приведенной в листинге 3.8. Так, из третьего столбца строки выбранного заказа извлекается код фирмы-заказчика:

Worksheets("Названия заказов").Cells(NomSpk + 2, 5 + (i - 1) * 2).Value.

После этого на листе Номенклатура по кодам запчастей производится поиск информации о запасных частях, данные о которых заносятся в табличную часть (см. рис. 3.9).

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

' Листинг 3.9. Изменение процедуры щелчка на кнопке Заполнить акт и приложение Private Sub CommandButton1_Click() ' Определяем индекс выбранного заказа в списке NomSpk = Spk.ListIndex ' Подсчет количества деталей в выбранном заказе ColDet = 0 While Worksheets("Названия заказов").Cells(NomSpk + 2, _ 5 + ColDet * 2).Value <> "" ColDet = ColDet + 1 Wend ' Подсчет количества позиций по прайсу NPrais = 0 While Worksheets("Номенклатура").Cells(NFirm + 2, 1).Value <> "" NPrais = NPrais + 1 Wend ' Подсчет числа фирм NFirm = 0 NFirm = NFirm + 1 Wend ' Извлекаем код фирмы в выбранном заказе CodFirm = Worksheets("Названия заказов").Cells(NomSpk + 2, 3).Value For i = 1 To NFirm ' Нахождение реквизитов фирмы по ее коду If CodFirm = Worksheets("Клиенты").Cells(i + 1, 1).Value Then Nazv = Worksheets("Клиенты").Cells(i + 1, 2).Value Adr = Worksheets("Клиенты").Cells(i + 1, 3).Value Tel = Worksheets("Клиенты").Cells(i + 1, 4).Value Fax = Worksheets("Клиенты").Cells(i + 1, 5).Value Exit For End If Next ' Внесение найденных реквизитов в поля на листе АКТ Worksheets("АКТ").Cells(6, 3).Value = Nazv Worksheets("АКТ").Cells(7, 3).Value = "Адрес:" + Adr Worksheets("АКТ").Cells(8, 3).Value = "Телефон:" + Tel Worksheets("АКТ").Cells(9, 3).Value = "Факс:" + Fax Worksheets("АКТ").Cells(6, 5).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, 1) Worksheets("Приложение").Cells(1, 6).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, 1) ' Очистка области для перечня запасных частей Worksheets("АКТ").Range("A13:F15") = "" Worksheets("Приложение").Range("A4:F100") = "" If ColDet < 4 Then For i = 1 To ColDet Worksheets("АКТ").Cells(i + 12, 1).Value = i Worksheets("АКТ").Cells(i + 12, 2).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, _ 5 + (i - 1) * 2).Value For j = 1 To NPrais If CStr(Worksheets("АКТ").Cells(i + 12, 2).Value) = _ CStr(Worksheets("Номенклатура").Cells(j + 1, 1).Value) Then Nazvanie = CStr(Worksheets("Номенклатура").Cells(j + 1, 2).Value) Tarif = CStr(Worksheets("Номенклатура").Cells(j + 1, 3).Value) Exit For End If Next Worksheets("АКТ").Cells(i + 12, 3).Value = Nazvanie Worksheets("АКТ").Cells(i + 12, 5).Value = Tarif Worksheets("АКТ").Cells(i + 12, 3).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, _ 5 + (i - 1) * 2 + 1).Value Worksheets("АКТ").Cells(i + 12, 6).Value = _ Worksheets("АКТ").Cells(i + 12, 4).Value * Tarif Next Else For i = 1 To ColDet Worksheets("Приложение").Cells(i + 3, 1).Value = i Worksheets("Приложение").Cells(i + 3, 2).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, _ 5 + (i - 1) * 2).Value For j = 1 To NPrais If CStr(Worksheets("Приложение").Cells(i + 3, 2).Value) = _ CStr(Worksheets("Номенклатура").Cells(j + 1, 1).Value) Then Nazvanie = CStr(Worksheets("Номенклатура").Cells(j + 1, 2).Value) Tarif = CStr(Worksheets("Номенклатура").Cells(j + 1, 2).Value) Exit For End If Next Worksheets("Приложение").Cells(i + 3, 3).Value = Nazvanie Worksheets("Приложение").Cells(i + 3, 5).Value = Tarif Worksheets("Приложение").Cells(i + 3, 4).Value = _ Worksheets("Названия заказов").Cells(NomSpk + 2, _ 5 + (i - 1) * 2 + 1).Value Worksheets("Приложение").Cells(i + 3, 6).Value = _ Worksheets("Приложение").Cells(i + 3, 4).Value * Tarif Next End If Hide End Sub

Рис. 3.13. Заполнение листа АКТ при большом перечне деталей

Рис. 3.14. Заполнение листа Приложение

На рис. 3.13 и 3.14 приведено, соответственно, заполнение листов АКТ и Приложение при четырех позициях в заказе. В этом случае детали заказа включаются на лист Приложение.