Userform with input numerical number with listbox

[jocker_boy]

Hi,

I found this 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:

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

 

[DanteAmor]

But this values ​​where do they come from: 0,25 - 1,00 - 0,75?

 

[jocker_boy]

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.

 

[jocker_boy]

I know this is a absurder example, but imagine this:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]monday[/TD]
[TD]1,00apple;0,50orange;0,25meat[/TD]
[/TR]
[TR]
[TD]tuesday[/TD]
[TD]0,25orange;0,35banana[/TD]
[/TR]
[TR]
[TD]wednesday[/TD]
[TD]0,50meat;1,00fish[/TD]
[/TR]
[TR]
[TD]....[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]apple
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fish[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]meat[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]apple
[/TD]
[TD]1,00[/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD]0,50[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fish[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]meat[/TD]
[TD]0,25
[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[DanteAmor]

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:

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

 

[jocker_boy]

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

 

[DanteAmor]

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.

 

[jocker_boy]

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

 

[DanteAmor]

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

 

[jocker_boy]

I have changed that value, but nothing happens.
But i need to add a textbox, right?