Vba excel listbox очистка
Очень часто при работе с формами(UserForm) приходится заполнять значениями КомбоБоксы(ComboBox) и ТекстБоксы(TextBox). Иногда однотипными данными. Или очистить их все после выполнения какого-либо действия. А может проверить, все ли элементы заполнены, прежде чем продолжать действие. Если КомбоБоксов/ТекстБоксов пару штучек - не проблема
и всех делов. А если их порядка двадцати? Или больше? Мало того, что писать это все долго, так еще и код растягивается, как портянка. Но это можно сделать гораздо проще и удобнее. Воспользоваться можно несколькими способами. Этот способ наиболее популярен, если необходимо произвести однотипные действия со всеми ТекстБоксами на форме:
Sub All_TextBoxes() Dim oControl As Control For Each oControl In UserForm1.Controls If TypeOf oControl Is MSForms.TextBox Then oControl.Value = "" Next oControl End Sub
В примере элементы формы очищаются, но в код можно подставить любое действие - добавление нового элемента списка(для ComboBox), поменять представление данных в TextBox, проверить наличие данных в проверяемом элементе и т.д. Тут уж зависит от поставленной задачи. Конечно, подобным способом можно проделать разные действия и с остальными элементами формы. Только надо будет заменить тип для проверки элементов:
Это не единственный способ проделывания однотипных действий с элементами формы. Код ниже использует имена элементов для обращения к ним:
Sub All_TextBoxes() Dim li As Long For li = 1 To 10 UserForm1.Controls("TextBox" & li).Value = li Next li End Sub
Недостаток данного метода: имена элементов должны строго соответствовать используемым в коде и лишь нумерация на конце имени должна различаться. За нумерацию отвечает переменная li и, конечно, цикл, в котором задается начальное и конечные значения. В примере ТекстБоксам с именами от "TextBox1" до "TextBox10" будут подставлены значения номеров самих ТекстБоксов. Но такой недостаток может быть очень полезным. Например, если необходимо проделать действия не над всеми ТекстБоксами, а лишь над некоторыми из них. Тогда все, что необходимо дать ТекстБоксам определенные имена с нумерацией("ToDB1","ToDB2" и т.д.) и в зависимости от имени можно проделывать различные действия: стирать значения, менять свойства элементов и т.д.
И есть еще один плюс такого подхода: когда необходимо заполнить значения ТекстБоксов значениями ячеек. Скажем надо заполнить 10 ТекстБоксов(с именами TextBox1 , TextBox2 , TextBox3 и т.д.) из ячеек диапазона A2:A11 листа с именем "Лист2"(т.е. из 10 ячеек, начиная с ячейки A2). Код будет выглядеть так:
Sub Fill_TextBoxes_FromCells() Dim li As Long For li = 1 To 10 UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Range("A" & li).Value 'или применить Cells вместо Range 'UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Cells(li, 1).Value Next li End Sub
Подробнее про обращение к диапазонам из VBA можно узнать из этой статьи: Как обратиться к диапазону из VBA
P.S. Небольшое дополнение: нужные элементы можно просто помещать внутрь объекта Frame. Тогда можно будет применять цикл исключительно по элементам внутри этого Frame(предположим, что Frame называется Frame1):
Sub All_TextBoxes_InFrame() Dim oControl As Control For Each oControl In Frame1.Controls If TypeOf oControl Is MSForms.TextBox Then oControl.Value = "" Next oControl End Sub
Кстати говоря, примерно так же можно перебрать элементы ActiveX не на форме, а на листе(вставляются на лист через вкладку Разработчик (Developer) -Вставить (Insert) -Элементы ActiveX (ActiveX Controls) ). Например, снимем флажки со всех CheckBox-ов:
Sub Off_ShapeCheckBoxes() Dim oControl 'цикл по всем объектам на листе For Each oControl In ActiveSheet.DrawingObjects 'определяем тип объекта - если это Элемент форм, то будет указание на конкретный тип If TypeName(oControl) = "CheckBox" Then oControl.Value = 0 End If Next oControl End Sub
В данном случае для определения конкретного типа контрола используется TypeName. В коде выше нам нужны CheckBox-ы и на них и проверяем. А вот список основных элементов форм, которые доступны по умолчанию для всех версий для вставки на лист:
Button - кнопка
DropDown - поле со списком
CheckBox - флажок
Spinner - счетчик
ListBox - список
OptionButton - радиокнопка
GroupBox - группа(рамка)
Label - надпись
ScrollBar - полоса прокрутки
Если совместить, то можно снять флажки со всех checkBox-ов - и ActiveX и Элементов форм:
Sub Off_AllCheckBoxes() Dim oControl 'цикл по всем объектам на листе For Each oControl In ActiveSheet.DrawingObjects 'определяем тип флажка: ActiveX или Элемент форм Select Case TypeName(oControl) Case "OLEObject" 'ActiveX - необходимо дополнительно проверить тип элемента If TypeOf oControl.Object Is MSForms.CheckBox Then oControl.Object.Value = 0 End If Case "CheckBox" 'Элемент форм CheckBox oControl.Value = 0 End Select Next oControl End Sub
В итоге, определяя конкретный тип элемента можно производить над ним нужные действия.
Удаление не выделенных строк в ListBox (Поиск в ListBox)
Добрый день! Друзья, подскажите, пожалуйста, как внутри ListBox организовать поиск по записям.
Удаление выделенных строк из умной таблицы
Ребят есть умная таблица с фамилиями (3 столбца). человек тыкает на нужные (в любой столбец.
Удаление пустых строк в ListBox
Private Sub ListBox1_Enter() For k = 2 To 50 If Cells(i + 1, 1).Value <> " " Then.
Перенос записей (строк) между двумя ListBox и их удаление
На листе "Меню" имеется 2 колонки: Наименование (А) и код (В). Так же имеется форма "требование" с.
При этом цикл лучше пустить в обратную сторону, т.к. при удалении i-го элемента i-тым становится следующий элемент.
Для этого изначально надо понимать как эта строка в ListBox попадает. И как её правильно отнести именно к нужной строке, чтобы не удалять то, чего удалять не надо.
Обычно в таких случаях при занесении данных в ListBox делают скрытый столбец, в котором для каждой строки записывается её номер на листе Excel. И потом опираясь на этот номер строка и удаляется.
Удаление выделенных файлов из Listbox
Всем привет! Есть Listbox, который заполняется файлами из папки ListBox1.Items.AddRange((From.
Перенос из ListBox'a в ComboBox выделенных строк
В ListBox есть строки,пользователь выделяет определенные и нажимает на Button,после этого.
Listbox. Получение текста из нескольких выделенных строк
Доброго времени суток. Такая задача: Необходимо из компонента listbox получить текст из выбранных.
Как скопировать данные из выделенных строк ListBox в буфер?
Как скопировать данные из выделенных строк ListBox в буфер? Обычные ctrl+Ins не работает. Может.
Удаление выделенных строк
имеется код формирования элементов checkbox <?php if(isset($_GET)) < $root = $_GET; .
I'm trying to 'reset' a listbox in Excel VBA when a form closes. Currently when I use the userform1.hide function the form disappears but when I open it up again using the .show function it still has the previous selections in it. As someone who is relatively new to this can anyone help?
The code for the listboxes is as follows:
Thanks in advance everyone,
6 Answers 6
If you want to clear ONLY the selection (as you are using hide, not unload) then use:
If it is a multiselect listbox, you need to use:
this will clear the selection by setting it to single selection only and then clearing the selection, then setting the functionality to multi select again.
If you want to clear the entire list box (the options that you select) use:
For MS-Access: setting .MultiSelect is not allowed; to deselect all items in a multiselect ListBox you need to do a loop like For Each varItem in .ItemsSelected : .Selected(varItem) = False : Next
try this code to Clear listbox in VBA
To reset the apparent item selected in the listbox, try:
There will be no apparent item in the listbox control after that.
Hide and show has no effect. If you want to use "brute force", use unload then load, but it will reset everything (not just the radio buttons) and will be memory consuming (well, if your form doesn't contain thousands of components and your computer is recent etc etc it will be fine though)
another way to do what you want is simply to run through all radio buttons and uncheck them all
Adapting LBPLC's clever technique, here's a single solution that works in all cases:
But note that this doesn't work if called from within the ListBox control's VBA Change event. Probably, that's due to Excel's ListBox change-event code freezing the effective state of the MultiSelect property during processing, in order to prevent unstable conditions for its own processing.
Not the answer you're looking for? Browse other questions tagged vba excel listbox or ask your own question.
Linked
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2022 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2022.5.6.42057
ListBox is one of the UserForm control. You can select and drag ListBox on the UserForm. This control is used to display list of items to a list. This is used on the UserForm. Please find more details about ListBox_Control in the following chapter. You can see how to load items to listbox_Control, how to move items from one listbox to another listbox, how to select items from a listbox_Control, etc.
In this topic:
VBA ListBox_Control on the UserForm
Please find more details about VBA ActiveX ListBox_Control and how we are adding it on the UserForm.
- Go To Developer Tab and then click Visual Basic from the Code or Press Alt+F11.
- Go To Insert Menu, Click UserForm. Please find the screenshot for the same.
Code:
Add dynamic ListBox_Control on the UserForm using VBA
Please find the following steps and example code, it will show you how to add dynamic list box control on the userform.
- Add command button on the userform from the toolbox.
- Right click on the command button, click properties
- Change the command button caption to ‘Create_Listbox’
- Double click on the command button
- Now, it shows following code.
- Call the below procedure named ‘Add_Dynamic_Listbox’ and find the below procedure to run.
Procedure to call in the Command Button:
output:
Add Items to ListBox_Control using VBA
Please find the following code, it will show you how to add list items to list box.
In the above code ListBox1 is the name of the listbox_Control. Where additem is the property of listbox.
Clear Items from the ListBox using VBA
Please find the following code, it will show you how to clear the list box items. The below code clears the list box1 items on the UserForm1.
Check if a List box item is selected or not using VBA
Please find the below code to know how to check if a List box is selected or not using VBA. In the below example (0) is the index number.
VBA ListBox Default Values in Excel
Here is the VBA list box default values in Excel. After adding items to list box by using any of the below code you can define the default value.
Code 1:
The below code is useful to select blank option in list box. Where ‘-1’ is the index number.
Code 2:
The below code is useful to select first item in the list box from the available list. . Where ‘0’ is the index number.
Code 3:
The below code is useful to select second item in the list box from the available list. Where ‘1’ is the index number.
Code 4:
The below code is useful to select the last item in the list box from the available list. Where ‘1’ is the index number.
Get the total count of Listbox Items
Here is the following example, it will show you how to get the total count of items in a list box. In the below example ListBox1 is the list box name and ListCount is the property of list box.
Output:
Move all Items from ListBox1 to ListBox2
Please find the below example code, it shows how to Move all Items from ListBox1 to ListBox2. In the below example ‘ListBox1 and ListBox2’ are the list box names.
Get Selected Items from ListBox1 to ListBox2
Please find the below example code, it shows how to Get Selected Items from ListBox1 to ListBox2. In the below example ‘ListBox1 and ListBox2’ are the list box names.
Make ListBox to Select Multiple Items
Please find the below example code, it shows how to make ListBox to Select Multiple Items. In the below example ‘ListBox1’ is the list box name.
Populate ListBox from an Array
Please find the below example code, it shows how to populate ListBox from an Array. In the below example ‘arrList’ is the array name. And ‘ListBox1’ is the list box name.
More Details About the ListBox_Control
VBA ListBox Excel Macros Examples Codes Adding Clearing Multiple Items
Please find the following link for more details about VBA ListBox Excel Macros Examples and Codes Adding and Clearing Multiple Items.
VBA to Remove Duplicates in ListBox Excel
Please find more details about Remove Duplicates in ListBox in Excel VBA.
Excel VBA FAQs: Frequently Asked Questions
Please find the most frequently asked questions and answers for your reference. These are explained more detailed way with examples.
Read More …
ListBox VBA Excel Example Macros Codes for Adding new Items,Moving all Items, selected Items from ListBox to another ListBox,clearing,Multi selection. VBA ListBox in Excel is one of finest control in the Excel. You can show the list of items in the listbox and user can select any item and do different operations. In this tutorial, we will explain different example on using ListBox in VBA Excel .
ListBox VBA Excel – Example Cases:
ListBox VBA Excel -Sample ListBox Design:
- GoTo Developer Tab from Menu
- GoTo Insert from Controls Part
- Insert two ListBox’s and four Buttons from ActiveX Controls
- Select 1st Button and then Right Click Goto Properties and click on it
- Rename 1st button Name as “cmdMoveAllRight” and enter Caption as “>>”
- Select 2nd Button and Rename it as “cmdMoveSelRight” and enter Caption as “>”
- Select 3rd Button and Rename it as “cmdMoveSelLeft” and enter Caption as “ >” button
- Now all ListBox1 Items are available in ListBox2
- Save the file as macro enabled workbook
Move all Items from ListBox2 to ListBox1
The following example will show you how to move all items in on-shot from one ListBox to another ListBox.
Output:
Here is the sample screen-shot.
Instructions:
- Please follow the above mentioned design steps
- Double Click on the 4th button or Right Click on 4th button and then click on View Code
- Copy the above code and Paste in the code window
- Goto Sheet1 and then Click on “ ” button
- Now ListBox1 selected Items are available in ListBox2
- Save the file as macro enabled workbook
Move selected Items from ListBox2 to ListBox1
You can move only selected items form One ListBox to another ListBox. See the below example…
Output:
Instructions:
- Please follow the above mentioned design steps
- Double Click on the 3rd button or Right Click on 3rd button and then click on View Code
- Copy the above code and Paste in the code window
- Goto Sheet1 and selct items from ListBox2, Which we want to move to ListBox1
- Click on “ ON SALE 80% OFF
Advanced Project Planning Templates
120+ Project Management Templates Pack
Excel | PowerPoint | Word
ULTIMATE RESOURCE MANAGEMENT TEMPLATE
50+ Essential Project Management Templates
Excel | PowerPoint | Word
Project Portfolio Management Templates
Excel | PowerPoint Templates
50+ Excel Project Management Templates
Share This Story, Choose Your Platform!
About the Author: Valli
Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing useful VBA examples ad Tips to helps us automating daily tasks.
Related Posts
VBA ActiveSheet – Excel Active Sheet Object
Excel VBA ColorIndex
Excel VBA Copy Range to Another Sheet with Formatting
Show or Hide|Unload a userform
21 Comments
Thanks for your example, very well written.
I was wodnering if you could simplify the code this way ?
Private Sub cmdMoveSelLeft_Click()
‘Variable Declaration
Dim iCnt As Integer
For iCnt = Me.ListBox2.ListCount – 1 To 0 Step -1
If Me.ListBox2.Selected(iCnt) = True Then
Me.ListBox1.AddItem Me.ListBox2.List(iCnt)
Me.ListBox2.RemoveItem iCnt
End If
Next
Or even more simplified by using one sub with parameters, parameters being the name of the controls. Somthing like this :
Sub transfert_one_or_several_items(source As String, destination As String)
Dim i As Integer
For i = Controls(source).ListCount – 1 To 0 Step -1
If Controls(source).Selected(i) Then
Controls(destination).AddItem Controls(source).List(i)
Controls(source).RemoveItem (i)
End If
Next
End Sub
Hello Gaetan,
Thanks for your suggestions. Our focus is providing fundamental concepts on VBA with proper examples to explain the use in real-time (particularly for data analysis applications).
Yes, we can simplify the codes even further, it will be easy for advanced programs. We will provide more simpler way of writing VBA Programs and best practices in near future.
Is there a way to save the selection you make? It seems, if you make a selection and save the workbook it won’t save upon re-opening (e.g. if i choose “south” then save, then I re-open, “south isn’t in Listbox2).
Hi Joe,
Excel will not save selected options in the listbox. However, you can save selected items using VBA.
1. Put the selected option in a Range on BeforeSave Event:
Example: in your case, we put ‘South’ in Sheet1 at Range A1
2. Populate the Items in the Range while opening the workbook (On Workbook Open Event)
Listbox2.value=Sheet1.Range(“A1”)
Hope this clarifies your doubt.
this is very helpful for a starter like me..
Im just wondering on how to code if i want to fill the list box by values which is in another sheet in a column.
this is for easy maintenance. Not hardcoding all the needed values.
You can do this in two different ways.
1. Without using VBA: Place a listbox form control (Not an activeX Control)in your worksheet. Then right click on it and choose the input range to fill the items in the listbox.
(To insert listbox: Goto Developer Tab in the ribbon => under developer tab click on the Insert in the Controls group under)
2. Using VBA: Loop through the range using for or do while loop and add the item to an activex listbox:
place a listbox (activex control) in your worksheet, let’s assue you have placed a listbox (ListBox1) in Sheet1. And you have the the data in Sheet2 for Range A1 to A10. The following procedure will populate the list box, you can call this in the workbook open event.
Sub populateListbox()
Sheet1.ListBox1.Clear
For i = 1 To 10
Sheet1.ListBox1.AddItem Sheet2.Cells(i, 1)
Next
End Sub
Hope this helps.
Thanks-PNRao!
Hi, This is very help like me as newbie. what if i have a userform with 3 textboxes which i want to fill the listbox using textboxes data.
Hi Jov,
Thanks for your comments.
You can use same method like:
ListBox1.Clear
ListBox1.AddItem TextBox1.Value
ListBox1.AddItem TextBox2.Value
ListBox1.AddItem TextBox3.Value
if you have many textboxes, you can do something like below:
ListBox1.Clear
For iCntr=1 to 10
ListBox1.AddItem Me.Controls("TextBox" &iCntr).Value
Next
Hope this helps!
Thanks
PNRao
I followed excatly all of the steps as outlined – and although my items move between for the “all” buttons they do not move for the “select” buttons. Is there something else I can do for the “select” buttons? Thanks.
Hi,
I may have two problems with the code you’re providing.
First with this line:
Me.ListBox1.RemoveItem iCnt
There, VBA’s always giving me an error.
Second with the saving of the values:
I’m using a range for my listbox values so they can be save. In fact, all the items in my list that are selected are being send to cells and when I reopen, I use ListFillRange to repopulate my ListBox. The problem is that I would like to modify my selection after reopening the file and VBA won’t allow it because the ListBox can’t be clear.
It should work fine, could you please provide me the example file.
Hi,
Thanks for your code … really helpful in our project.
I have one question… we are hardcoding the source values in Listbox1 similarly like your code. when we select the values to Listbox2 and again when you move back that value from listbox2 to listbox1 … that value is going to last row of Listbox1. Is there any way to get display in Listbox1 as we hardcoded in the source?
If I have many controls like textbox,combobox & DTpicker then how to pull listbox items in there
Thanks in advanced
hi,
I am new to VBA macro and also VBA code. I have an excel sheet containing following respective columns :-Region Company, SicCode, Address, City, ZIP, ContactName, Telephone, etc………….
Here i have to perform some data cleaning work like to eliminate exceptions from company names ( -,”,’,, @, numbers, etc.,) , SicCode exceptions ( should only contain numbers, not alphabets and no special characters), zip code exceptions ( should be in 6 characters and one space between 3 characters like “A1B 2NO”, should not be like “A1B2NO”), Contact name should be in alphabets in proper no leading and trailing spaces, telephone should be in numberic and should not contain special characters (ex: “(123) 123 1234” ).
for all these data clean, how can i write the code and execute. pls help me in session.
Hi,
May i request you to kindly give codes and examples to change the range of listbox connected to a worksheet depending on the rows of data. i have a worksheet with 25 rows of data, which is a list of distributors. When the option to display the list is selected thru a command button to run the macro, If i added 10 more names, my listbox should display all 35 names. But, if i delete 10 names, from the original, it should display only 15 names. i.e, end range of listbox should be able to change to last row of data. To say, originally A1:A25, if 10 names added, A1:A35, if 5 names deleted then A1:A30 and so on.
Читайте также: