Userform with input numerical number with listbox

jocker_boy

Board Regular
Joined
Feb 5, 2015
Messages
83
Hi,

I found this code:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.EnableEvents = False
    On Error GoTo exitHandler
 
    If Target.Column = 10 Then
      gCountryListArr = Sheets("RESOURCES").Range("Table2[COD]").Value
      gCellCurrVal = Target.Value
      UserForm1.Show 'Pop up the form
      Target.Value = gCellCurrVal
    End If

exitHandler:
    Application.EnableEvents = True
End Sub

and:

Code:
Private Sub CommandButton1_Click()
  UserForm1.Hide 'Pop up the form
End Sub
Private Sub CommandButton2_Click()
    For ii = 0 To ListBox1.ListCount - 1
        Me.ListBox1.Selected(ii) = False
    Next ii
End Sub
Private Sub CommandButton3_Click()
    For ii = 0 To ListBox1.ListCount - 1
        Me.ListBox1.Selected(ii) = True
    Next ii
End Sub

Private Sub CommandButton4_Click()
    gCellCurrVal = ""
    For ii = 0 To ListBox1.ListCount - 1
      If Me.ListBox1.Selected(ii) = True Then
        If gCellCurrVal = "" Then
          gCellCurrVal = Me.ListBox1.List(ii)
        Else
          gCellCurrVal = gCellCurrVal & ";" & Me.ListBox1.List(ii)
        End If
      End If
    Next ii
    UserForm1.Hide
End Sub

Private Sub UserForm_Activate()
On Error Resume Next
'On each activation, clear the whole,
'then add every country list element as blank
Me.ListBox1.Clear
For Each element In gCountryListArr
  Me.ListBox1.AddItem element
Next element
UserForm_initialize
End Sub


Private Sub UserForm_initialize()
  For Each element In Split(gCellCurrVal, ";")
    For ii = 0 To ListBox1.ListCount - 1
      If element = Me.ListBox1.List(ii) Then
        Me.ListBox1.Selected(ii) = True
      End If
    Next ii
  Next element
End Sub

Sorry for not posting a image, but in my companies i can't find any site for hosting images that aren't block.

But the userform have 4 buttons: "Cancel"; "Clear"; "ALL"; "Ok".

This works perfect, because i need to select multiples strings to input in one cell.
However, i would like to input a diferent numerical number behind each string.
For example, if i have my strings:

ABC
DE
FGHI
JKL
MNOP
RST


I would like to select a cell, open a userform and have the possibility to write:

0,25 and pick ABC
1,00 and pick FGHI
0,75 and pick JKL

To input the cell this result:

"0,25ABC;1,00FGHI;0,75JKL"

Is this possible?

Many thanks in advance.
Gonçalo
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
But this values ​​where do they come from: 0,25 - 1,00 - 0,75?
 
Upvote 0
But this values ​​where do they come from: 0,25 - 1,00 - 0,75?

For each cell, the user needs to write some numerical values for some strings, so that values are random and different for each cell.
I think the best is to have a cell where the user can write some numerical value, that select one string from a list and click "add", and do this for every string he needs, then click "done" and those values with corespondent string will fill the cell.
 
Upvote 0
I know this is a absurder example, but imagine this:

monday1,00apple;0,50orange;0,25meat
tuesday0,25orange;0,35banana
wednesday0,50meat;1,00fish
.......

<tbody>
</tbody>

Strings:
apple
orange
meat
banana
fish


The best and ideal solution would be, click in B2, popup a user form similar to this, with all the string in 1º column

apple
orange
banana
fish
meat

<tbody>
</tbody>

where i can write the numerical numbers for each string in 2º column, and then click done.

apple
1,00
orange
0,50
banana
fish
meat0,25

<tbody>
</tbody>


or when i click in B2, i would like to popup a userform and:

  • write 1,00 and choose "apple" and click add,
  • write 0,50 and choose "orange" and click add,
  • write 0,25 and choose "meat" and click add,
  • click done.

Thanks once more.
Gonçalo
 
Last edited:
Upvote 0
But in a listbox you can not write data.

or when i click in B2, i would like to popup a userform and:


  • write 1,00 and choose "apple" and click add,
  • write 0,50 and choose "orange" and click add,
  • write 0,25 and choose "meat" and click add,
  • click done.

The above is possible, you only need a textbox to enter the quantity and the listbox.

Try this:

Code:
Private Sub CommandButton4_Click()
    gCellCurrVal = ""
    For ii = 0 To ListBox1.ListCount - 1
      If Me.ListBox1.Selected(ii) = True Then
        If gCellCurrVal = "" Then
          gCellCurrVal = Me.ListBox1.List(ii, 1) & Me.ListBox1.List(ii, 0)
        Else
          gCellCurrVal = gCellCurrVal & ";" & Me.ListBox1.List(ii, 1) & Me.ListBox1.List(ii, 0)
        End If
      End If
    Next ii
    UserForm1.Hide
End Sub
'
Private Sub CommandButton5_Click()
    'button Add
    If TextBox1 = "" Then
        MsgBox "Write value in textbox"
        TextBox1.SetFocus
        Exit Sub
    End If
    If ListBox1.ListIndex = -1 Then
        MsgBox "Select item from listbox"
        ListBox1.SetFocus
        Exit Sub
    End If
    '
    ListBox1.List(ListBox1.ListIndex, 1) = TextBox1.Value
End Sub
 
Upvote 0
I delete the old code for CommanButton4_Click() and added that code.
I also add a new button.
But i can't get the textbox to appear.
I very new at vba, sorry.

Thanks,
Gonçalo
 
Last edited:
Upvote 0
You must do the following:
1- Create a textbox1 in your userform
2- Execute your userform
3- Select a record in the listbox1
4- Capture a value in the textbox1
5- Click on commandbutton5
6 - Repeat steps 2 to 5
7. Click on commandbutton4

If you have an error you must tell me what the error says and in which line the macro stops.
 
Upvote 0
You must do the following:
1- Create a textbox1 in your userform
2- Execute your userform
3- Select a record in the listbox1
4- Capture a value in the textbox1
5- Click on commandbutton5
6 - Repeat steps 2 to 5
7. Click on commandbutton4

If you have an error you must tell me what the error says and in which line the macro stops.

It works :)
Many thanks.

Do you think it is possible, to add a "textbox", so that every time when click in button5, we can see the result saved.

Many thanks,
Gonçalo
 
Upvote 0
It works :)
Many thanks.

Do you think it is possible, to add a "textbox", so that every time when click in button5, we can see the result saved.

Many thanks,
Gonçalo

To see the results, change the ColumnCount = 2 property of the listbox. Select Listbox / Properties / ColumnCount and type 2
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top