Return to the original row with a listbox from a userform

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hi all, I wondered how to Return to the original row with a listbox

I know with a short statement that's OK for the listbox for a determined lenght and height, I see the whole statement when I activate the userform.

Code:
If OptionButton1.Value = True Then Me.ListBox2.AddItem ("blabla")
But If I don't want to change the features of the listbox and keep the same height and lenght

If I have a long statement, it does not fully appear in the listbox when the userform is activated.

Like

Code:
If OptionButton2.Value = True Then Me.ListBox2.AddItem ("blablaasdfghjklqwertyuiopfghjkwertyuiasdfghjk")
So, does anyone know how to Return to the original row with a listbox ?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,230
Office Version
2007
Platform
Windows
Try this
When you move the mouse to the listbox, its width automatically increases. When you remove the mouse, it automatically returns to its original size.

Code:
Dim LWidth  'At the beginning of all the code


Private Sub ListBox2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ListBox2.Width = 200
End Sub


Private Sub UserForm_Activate()
    LWidth = ListBox2.Width
End Sub


Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ListBox2.Width = LWidth
End Sub
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Many Thanks @DanteAmor for your reply.

Indeed that works :), the listbox expands to the right direction, however it still does not allow to display all the text in the listbox, even if it's better than before.

Actually rather than expand the width of the listbox, because unfortunately I can't expand endlessly the width of the userform, I really look for a code that would allow me [h=2]to Return to the original row for a very long text in a listbox [/h]
for the VBA code, you can use " _" and then to the next row "&"
but for a very long text included in a listbox, when the userform is activate, how to Return to the next row ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,230
Office Version
2007
Platform
Windows
Many Thanks @DanteAmor for your reply.

Indeed that works :), the listbox expands to the right direction, however it still does not allow to display all the text in the listbox, even if it's better than before.

Actually rather than expand the width of the listbox, because unfortunately I can't expand endlessly the width of the userform, I really look for a code that would allow me to Return to the original row for a very long text in a listbox


for the VBA code, you can use " _" and then to the next row "&"
but for a very long text included in a listbox, when the userform is activate, how to Return to the next row ?

In a ListBox control you can only have one row per field.
I think I'm not really understanding what the problem is.
Do you have a ListBox or a TextBox?
In the listbox, when the length of the text is longer, the scroll bar is activated automatically, so that you scroll horizontally.
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hello @DanteAmor,
Many thanks for your reply and sorry for mine which is quite late
Please find enclosed the Excel Workbook with the macro you gave me.

https://www.dropbox.com/s/gdibhibnztthfme/Macro Listbox expand.xlsm?dl=0


Indeed for the Optionbutton1, that’s Ok given that the text related is short
But for the Optionbutton2, even if the listbox expand,, it does not displays all the text.

So if you say me, one row in the listbox for 1 statement. So I guess that’s not possible to get what I was looking for: Return to the next row with a listbox for a same statement

Kind regards

Doflamingo
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,230
Office Version
2007
Platform
Windows
delete the code and use only the following

Code:
Private Sub OptionButton1_Click()
If OptionButton1 = True Then ListBox1.AddItem "blabla"
End Sub


Private Sub OptionButton2_Click()
If OptionButton2 = True Then ListBox1.AddItem "You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:"
End Sub


Private Sub UserForm_Activate()
 ListBox1.ColumnWidths = "1000"
End Sub
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hello @DanteAmor

I've erased the old code and replaced it with the new one you gave me.

That a srcoll bar that allows me to see the whole text of the listbox when Optionbutton2 is activated

Many thanks for your help and time once again :)

Kind regards

Doflamingo
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,230
Office Version
2007
Platform
Windows
Hello @DanteAmor

I've erased the old code and replaced it with the new one you gave me.

That a srcoll bar that allows me to see the whole text of the listbox when Optionbutton2 is activated

Many thanks for your help and time once again :)

Kind regards

Doflamingo

Youre welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,887
Messages
5,489,539
Members
407,697
Latest member
Lotte_4

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top