Mẹo Vba listbox design Mới nhất

Mục lục bài viết

Mẹo Hướng dẫn Vba listbox design Mới Nhất

Cập Nhật: 2022-03-14 03:34:14,Bạn Cần kiến thức và kỹ năng về Vba listbox design. You trọn vẹn có thể lại Thảo luận ở phía dưới để Tác giả được tương hỗ.

734

I have been experimenting with possible solutions to your problem. I think I have taken the listbox approach as far as it will go so I will share what I have discovered.

Tóm lược đại ý quan trọng trong bài

Add Items to ListBox_Control using VBA
Clear Items from the ListBox using VBA
Check if a List box item is selected or not using VBA
VBA ListBox Default Values in Excel
Get the total count of Listbox Items
Move all Items from ListBox1 to ListBox2
Get Selected Items from ListBox1 to ListBox2
Make ListBox to Select Multiple Items
Populate ListBox from an Array
More Details About the ListBox_Control

I can find nothing on the web to suggest that anyone believes you can have listbox column headers without using property RowSource. To use RowSource, you set it to an Excel range.

I got Outlook to create an Excel workbook and to write some data to it. Unfortunately, I could not find any way of getting an Outlook user form to access an Excel range. The syntax for setting RowSource is:

ListBox1.RowSource = “Emails!A2:D20”

This is not the standard syntax for a range and I have failed to discover any method of extending it to include a workbook name.

Jonah_Hess describes an interesting approach in stackoverflow/a/43381634/973283. He has two list boxes. One is a one-line listbox that contains the headings and the other contains the data. The two listboxes are set to the same number of columns with the same widths. This gives an attractive appearance but if you scroll the data listbox, the headings listbox does not scroll with it. This is not really any different from placing labels above a single listbox.

I tried putting the headings and the data list boxes in a frame and scrolling the frame but could not get it to work. I have used frames with VB user forms but the functionality is very different so there are no lessons learnt that I could bring to a VBA user form. Perhaps someone more familiar with VBA frames could get this approach to work.

I gave up trying to get a solution in Outlook. An Excel macro can access Outlook data so I tried that approach.

I created a macro-enabled workbook. Within it, I have two forms both of which fill the screen to conceal the worksheet. The first form just says: “Please wait while I load data from Outlook”. I am not clear about the data on your form so I imported selected details from a thư mục full of junk emails which I wrote to a worksheet. I sized the columns for the list box to match those for the worksheet. The result was:

The text is a little small but I think it is readable. The listbox at the bottom allows me to select emails for different periods. Long ago I had problems with RowSource which meant I could change the values in the range but I could not change the size of the range. I have either managed to avoid that problem today or it was a bug that has been fixed.

You can see that the headings are displayed. The columns are a little wide but I consider them to be a reasonable first approximation. Options to change the widths would be easy to implement.

The changes you ask for:

Add column headers. Done
Change the alignment of a column to right-aligned. Possible but difficult. You would need to pad the text with an appropriate number of leading spaces.
Optional: allow the user to sort the list by any column. The data is in a worksheet so easy.
Optional: allow the user to change the width of any column. I have set the column widths at runtime to show it is possible.
Optional: show a grid in the ListBox. Not possible.

If the above is interesting, I could show you all my code and instruct you on creating the forms so you could duplicate my experiment. Alternately, I could just explain: how I imported the Outlook data to Excel, how I included the column headings and how I set the column widths.

I cannot find anything to suggest that anything better can be achieved with listboxes.

An alternative approach is to use a grid of labels. This can give an attractive appearance and one or more columns could be right-aligned. Using the Controls property of the user form, you can treat the grid as a two-dimensional array. I have used this technique long ago and found it attractive and not particularly difficult.

A list box is a list from where a user can select an item. To create a list box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click List Box.

3. Drag a list box on your worksheet.

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ListBox1 as the name of the list box.

Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

7. To add items to the list box, add the following code lines to the Workbook Open Event:

With Sheet1.ListBox1 .AddItem “Paris” .AddItem “Thành Phố New York” .AddItem “London”

End With

Note: use Sheet2 if your list box is located on the second worksheet, Sheet3 if your list box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open sự kiện, you might want to add the following code line before these code lines. This code line clears the list box. This way your items won’t be added multiple times if you execute your code more than once.

ListBox1.Clear

8. To link this list box to a cell, right click on the list box (make sure design mode is selected) and click on Properties. Fill in D3 for LinkedCell.

Note: also see the ListFillRange property to fill a list box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

Although in some situations it can be useful to directly place a list box on your worksheet, a list box is particularly useful when placed on a 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.

Drag Listbox_Control on the Userform from the Toolbox. Please find the screen shot for the same.

Double Click on the UserForm, and select the Userform sự kiện as shown in the below screen shot.

Now can see the following code in the module.

Private Sub UserForm_Initialize()

End Sub

Now, add the following code to the in between procedure.

Code:

Private Sub UserForm_Initialize()
ListBox1.AddItem “MBA”
ListBox1.AddItem “MCA”
ListBox1.AddItem “MSC”
ListBox1.AddItem “MECS”
ListBox1.AddItem “CA”
End Sub

Now, Press ‘F5’ to run the code. You can see the following Output. It is shown in the following Screen Shot.

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.

Private Sub CommandButton1_Click()

End Sub

Call the below procedure named ‘Add_Dynamic_Listbox’ and find the below procedure to run.

Private Sub CommandButton1_Click()
Call Add_Dynamic_Listbox
End Sub
Sub Add_Dynamic_Listbox()
‘Add Dynamic List Box and assign it to object ‘LstBx’
Set LstBx = UserForm3.Controls.Add(“Forms.ListBox.1”)

‘List Box Position
LstBx.Left = 20
LstBx.Top = 10
End Sub

Now, click F5 to run the macro, click ‘Create_Listbox’ button to see the result.

You can see the created dynamic checkbox in the following screen shot.

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.

Private Sub Insert _Items _To_LstBox ()
ListBox1.AddItem “Item 1”
ListBox1.AddItem “Item 2”
ListBox1.AddItem “Item 3”
ListBox1.AddItem “Item 4”
ListBox1.AddItem “Item 5”
End Sub

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.

Sub Clr_LstBx()
UserForm3.ListBox1.Clear
End Sub

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.

Sub Chk_Item_SelectOrNot()
If UserForm3.ListBox1.Selected(0) = True Then
MsgBox “First item has selected in the ListBox.”
Else
MsgBox “First item has not selected in the ListBox.”
End If
End Sub

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.

Sub LstBx_Dflt_Val_Ex1()
UserForm3.ListBox1.ListIndex = -1
End Sub

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.

Sub LstBx_Dflt_Val_Ex2()
UserForm3.ListBox1.ListIndex = 0
End Sub

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.

Sub LstBx_Dflt_Val_Ex3()
UserForm3.ListBox1.ListIndex = 1
End Sub

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.

Sub LstBx_Dflt_Val_Ex4()
UserForm3.ListBox1.ListIndex = UserForm3.ListBox1.Count – 1
End Sub

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.

Sub Get_Ttl_Cnt()
MsgBox “Total Items in a ListBox is ” & UserForm3.ListBox1.ListCount
End Sub

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.

Sub Move_ListBox_Items()
‘Variable declaration
Dim iCnt As Integer

‘Moving ListBox1 Items to ListBox2
For iCnt = 0 To ListBox1.ListCount – 1
ListBox2.AddItem ListBox1.List(iCnt)
Next iCnt

‘Then Clear the ListBox1 Items
ListBox1.Clear
End Sub

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.

Sub Get_ListBox_Selected_Items()
‘Variable declaration
Dim iCnt As Integer

‘Get Selcted Items from ListBox1 to ListBox2
For iCnt = 0 To ListBox1.ListCount – 1
‘Check ListBox Item has selcted or not
If ListBox1.Selected(iCnt) = True Then
ListBox2.AddItem ListBox1.List(iCnt)
End If
Next
End Sub

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.

Sub Multiple_ListBox_Selection()
ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

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.

Sub Load_ListBox_From_Array()
‘Load Items to an Array
arrList = Array(“Item 1”, “Item 2”, “Item 3”)

‘Load an Array Items to ListBox
ListBox1.List = arrList
End Sub

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.

Review Share Link Tải Vba listbox design ?

– Một số từ khóa tìm kiếm nhiều : ” Review Vba listbox design tiên tiến và phát triển nhất , Share Link Download Vba listbox design “.

Hỏi đáp vướng mắc về Vba listbox design

Bạn trọn vẹn có thể để lại phản hồi nếu gặp yếu tố chưa hiểu nha.
#Vba #listbox #design Vba listbox design