How to create a transparent listbox control?
When looking at the properties of a listbox, you will noticed that the BackStyle property does not exist. But this property is available e.g for label controls, textbox ...etc
property allows us to add transparency to our control (fmBackStyleTransparent)
In our example we will keep the background image and have it displayed in the bottom of my list. We will use a textbox instead of the listbox.
I - Prerequisites:
First draw a UserForm to which we shall apply a background image (using the Picture property).
In this UserForm, draw a textbox.
II - Tip:
Use the MultiLine, ScrollBars and BackStyle properties of the textbox to give it the appearance of a listbox. When "loading" data, simply define each line using an invisible character (Chr(1)). To use this data, simply loop through all the characters in this selection.
III - The codes
Upon initializing the UserForm:
Private Sub UserForm_Initialize()
Dim i As Integer, texto As String
For i = 1 To 100
'Enter the text to be displayed in the textbox in the form of a list,
'Each new line start the invisible character Chr(1)
If i = 1 Then texto = Chr(1) & "Valeur de liste 1" Else texto = texto & Chr(10) & Chr(1) & "Valeur de liste " & i
.BackStyle = fmBackStyleTransparent
.MultiLine = True
.ScrollBars = fmScrollBarsVertical
.Move 5, 5, Me.Width - 16, Me.Height - 40
'At the end the list with the invisible character, to indicate that it is the last line
.Text = texto & Chr(1)
'If you want that the selected line becomes the first line, simply clear the following lines of code:
'.CurLine = 0
During the MouseDown event of the textbox:
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim debSel As Long, finSel As Long, texto As String, txtSel As String, i As Integer
'The Chr(10) characters used to fill the textbox
'count as 1 character.
'Therefore, they should not be taken into account in this procedure
'we must remove them from our variable
texto = Replace(TextBox1.Text, Chr(10), "")
'Move the location of the mouse click
debSel = TextBox1.SelStart
finSel = TextBox1.SelStart
'The lines always begin with the "Chr(1)" character, so we will look for:
' 1- backwards => will give us the position of the first character of the line
Do While Mid(texto, debSel, 1) <> Chr(1)
debSel = debSel - 1
' 2- forward = > will give us the position of the first character of the next line
If Mid(texto, finSel, 1) = Chr(1) Then finSel = finSel + 1
Do While Mid(texto, finSel, 1) <> Chr(1)
finSel = finSel + 1
'Loop to store the selected content in the variable
For i = debSel + 1 To finSel - 1
txtSel = txtSel & Mid(texto, i, 1)
'Cursor at the beginning of the line
TextBox1.SelStart = debSel
TextBox1.SelLength = finSel - debSel - 1
'Send the selected value in a cell
Sheets("Feuil1").Range("A1") = Trim(txtSel)
IV - Going further
You can also add a real listbox to the UserForm and make it invisible (ListBox1.Visible = False). This will offer more flexibility as you will take advantage of all the properties of listbox.
V - Download:
Download sample file: http://cjoint.com/14av/DDDqYVphUkn.htm