random numbers from list box

  • Thread starter Thread starter baha17
  • Start date Start date
B

baha17

Hi Everyone,
I want to make a list,list items are numbers from 1 to 20. each tim i
click commandbutton i want two random unique items from list to
display textbox1 and textbox2. so for the next click those two items
should remove from the list.Is that possible to do without function
method? I just need one simple code in my userform.
Thank you in advance
Baha
 
Hi,

Store you numbers on a worksheet with this

Private Sub UserForm_Initialize()
With Sheets("Sheet1").Range("A1")
.Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Date:=xlDay, Step:=1, Stop:=20, Trend:=False
End With
End Sub

The attach this code to your button

Private Sub CommandButton1_Click()
If WorksheetFunction.Count(Sheets("Sheet1").Range("A1:A20")) < 2 Then
MsgBox "Not enough values left"
Exit Sub
End If

Do
Mycell = Int((20 - 1 + 1) * Rnd + 1)
FoundVal = Sheets("Sheet1").Range("A" & Mycell).Value
Sheets("Sheet1").Range("A" & Mycell).Value = ""
Loop Until FoundVal <> ""
TextBox1.Text = FoundVal

Do
Mycell = Int((20 - 1 + 1) * Rnd + 1)
FoundVal = Sheets("Sheet1").Range("A" & Mycell).Value
Sheets("Sheet1").Range("A" & Mycell).Value = ""
Loop Until FoundVal <> ""
TextBox2.Text = FoundVal
End Sub

Mike
 
Back
Top