I have a listbox in userform based on a named range.
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
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 ?
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 ?