Select item in listbox and set range

vidarv

New Member
Joined
Feb 23, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have a listbox in userform based on a named range.
VBA Code:
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

VBA Code:
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 ?
 

Attachments

  • Picture1.png
    Picture1.png
    140.9 KB · Views: 13

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,059
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Feb 23, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
@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
VBA Code:
 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
 

Attachments

  • Picture1.png
    Picture1.png
    7.2 KB · Views: 4

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,059
Office Version
  1. 2010
Platform
  1. Windows
I forgot to put the initialize event

Try this:
VBA Code:
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
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,059
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 23, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I forgot to put the initialize event

Try this:
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
15,059
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,221
Messages
5,852,736
Members
431,520
Latest member
BK_Excel

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
Top