Select item in listbox and set range

[vidarv]

I have a listbox in userform based on a named range.

Private Sub UserForm_Initialize()

Set rng = Range("Tolk")

For Each cell In rng.Cells
If cell.Value <> vbNullString Then
ListBox1.AddItem cell.Value
End If
Next cell
 
End Sub

When a item is selected in the listbox it will check if the name in range the exists and set a range.
In the commandbutton i have hardcoded an example

Private Sub CommandButton1_Click()
b = ThisWorkbook.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
t = ThisWorkbook.ActiveSheet.Cells(Rows.Count, b + 5).End(xlUp).Row
For m = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(m) = True Then
MsgBox (m)

'Dim ws As Worksheet

    Range("B6").Select
    ActiveCell.FormulaR1C1 = txtFraogMed.Text
    Range("C6").Select
    ActiveCell.FormulaR1C1 = txtTilogMed.Text
    Range("D6").Select
    ActiveCell.FormulaR1C1 = txtFraogMed.Text
    Range("E6").Select
    ActiveCell.FormulaR1C1 = txtTilogMed.Text
    Range("F6").Select
    ActiveCell.FormulaR1C1 = txtFraogMed.Text
    Range("G6").Select
    ActiveCell.FormulaR1C1 = txtTilogMed.Text
    Range("H6").Select
    ActiveCell.FormulaR1C1 = txtFraogMed.Text
    Range("I6").Select
    ActiveCell.FormulaR1C1 = txtTilogMed.Text
    Range("J6").Select
    Range("B6:I6").Select
    Selection.AutoFill Destination:=Range("B6:NC6"), Type:=xlFillDefault
    Range("B6:NC6").Select
    
End If
Next m

End Sub

In the named range "Tolk" i have 102 cells to loop through to check if the value is equal to the value in listbox.
What I am trying to do is select items in listbox, click on the commandbutton and set the range to ("B6:NC6") or ("B7:NC7") up to ("B107:NC107") depending on values selected in listbox (all, 1, 2 or 50 etc.)

Can any one help please ?

 

[DanteAmor]

and set the range to ("B6:NC6") or ("B7:NC7") up to ("B107:NC107") depending on values selected in listbox (all, 1, 2 or 50 etc.)

What is the purpose of setting the rows in range of selected items in the listbox?

With the following code, first in column 1 of the listbox it stores the row number of the item, that way it is not necessary to make a loop to know in which row the item is located. Second in the rng object all the rows of the items selected in the listbox are set.

Private Sub CommandButton1_Click()
  Dim rng As Range
  Dim sh As Worksheet
  Dim i As Long
  
  Set sh = Range("Tolk").Parent
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        If rng Is Nothing Then
          Set rng = sh.Range("B" & .List(i, 1), sh.Range("NC" & .List(i, 1)))
        Else
          Set rng = Union(rng, sh.Range("B" & .List(i, 1), sh.Range("NC" & .List(i, 1))))
        End If
      End If
    Next
  End With
  If Not rng Is Nothing Then rng.Select
End Sub

 

[vidarv]

@DanteAmor thank you for your answer.
When running your code i get the following error.
rng =Nothing
I am sorry if my explanations not was clear enough.
The reason I wanted to select different items in the listbox is to set different values depending on the two textboxes in the userform

 Range("B6").Select
   ActiveCell.FormulaR1C1 = txtFraogMed.Text
   Range("C6").Select
    ActiveCell.FormulaR1C1 = txtTilogMed.Text
   Range("D6").Select

B6 is set to hour 09:00 (textbox is named txtFraogMed) C6 is hour 15:30 (textbox is named txtTilogMed).
D6 is then 09:00 and E6 is 15:30 and further up to NC.
If the values in the textboxes is changed and a different item is selected in the listbox this values are then showed in the range.

Thank you

 

[DanteAmor]

I forgot to put the initialize event

Try this:

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim i As Long, j As Long, k As Long
 
  Set sh = Range("Tolk").Parent
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        j = .List(i, 1)
        For k = Columns("B").Column To Columns("NC").Column Step 2
          sh.Cells(j, k).Resize(1, 2).Value = Array(txtFraogMed, txtTilogMed)
        Next
      End If
    Next
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim rng As Range, cell As Range
  Set rng = Range("Tolk")
  For Each cell In rng.Cells
    If cell.Value <> vbNullString Then
      With ListBox1
        .AddItem cell.Value
        .List(.ListCount - 1, 1) = cell.Row
      End With
    End If
  Next cell
End Sub

 

[DanteAmor]

If the values in the textboxes is changed and a different item is selected in the listbox this values are then showed in the range.

If you select other items and change values, you must press the button again.

 

[vidarv]

I forgot to put the initialize event

Try this:

Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  Dim i As Long, j As Long, k As Long
 
  Set sh = Range("Tolk").Parent
  With ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) = True Then
        j = .List(i, 1)
        For k = Columns("B").Column To Columns("NC").Column Step 2
          sh.Cells(j, k).Resize(1, 2).Value = Array(txtFraogMed, txtTilogMed)
        Next
      End If
    Next
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim rng As Range, cell As Range
  Set rng = Range("Tolk")
  For Each cell In rng.Cells
    If cell.Value <> vbNullString Then
      With ListBox1
        .AddItem cell.Value
        .List(.ListCount - 1, 1) = cell.Row
      End With
    End If
  Next cell
End Sub

Thank you @DanteAmor this is exactly what I was looking for. Nice and elegant coding

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.