Nightlife2004
asked on
Excel Function To Randomly Select A Value In Excel Sheet
Hello,
I am trying to create a function in excel to randomly select a value which will then go into a combo box when I click on a button and will then take that value so I can add it to another spread sheet.
On my second spreadsheet I have a list of values which I am trying use.
I know that in excel you can use the random function like this ?
=INDEX(Sheet2!$C:$C,RANDBE TWEEN(1,CO UNTA($C:$C )),1) . but how would I include this function into a combo box as this appears blank if I type it into the properties box of the combo box
Thanks.
I am trying to create a function in excel to randomly select a value which will then go into a combo box when I click on a button and will then take that value so I can add it to another spread sheet.
On my second spreadsheet I have a list of values which I am trying use.
I know that in excel you can use the random function like this ?
=INDEX(Sheet2!$C:$C,RANDBE
Thanks.
ASKER
Hello,
I have tested the formula , it works great however I get some errors when running the macro if I changed the values to text based like string text.
Mismatch error code 13.
As I believe when I debug the code it only shows numbers for the controlformat.value where as the v variant is like a text string.
And one other note I am using activex control combo boxes in my excel spread sheet , how would I go about implementing that?
I have tested the formula , it works great however I get some errors when running the macro if I changed the values to text based like string text.
Mismatch error code 13.
As I believe when I debug the code it only shows numbers for the controlformat.value where as the v variant is like a text string.
And one other note I am using activex control combo boxes in my excel spread sheet , how would I go about implementing that?
I posted a sample file so you could see that the techniques I was suggested actually worked as desired. Since your actual workbook is different from mine and you are unable to adjust to the differences, please post a sample workbook so I can customize my code to match your data layout.
ASKER
Hello,
Ok I will send you the sample workbook which you sent me , I have slightly modified it.
Like all the number values I have changed to text strings like apple,banana etc.
I have include also an activex control combobox , stating that when I click on the drop down button I would like a random value selected from the list.
An error screenshot of the error which I get now when I run the macro.
That will be all thanks.
ComboBoxQ28330547.xlsm
Ok I will send you the sample workbook which you sent me , I have slightly modified it.
Like all the number values I have changed to text strings like apple,banana etc.
I have include also an activex control combobox , stating that when I click on the drop down button I would like a random value selected from the list.
An error screenshot of the error which I get now when I run the macro.
That will be all thanks.
ComboBoxQ28330547.xlsm
I changed the code so it refers to your ActiveX ComboBox control. I also allowed the user to select a specific value from the dropdown, as well as select a random value when you click on the ComboBox.
The code for ActiveX ComboBox events must go in the code pane for the worksheet that contains them. In your case, that would be the Sheet1 code pane. The GotFocus event occurs when the user selects the ComboBox. When that happens, the following event sub runs:
Sub RandomValueMacro()
Dim v As Variant
Dim shp As Shape
v = Range("RandomValue").Value
Worksheets("Sheet1").ComboBox1.Value = v
Range("F5").Value = v
End Sub
The code for ActiveX ComboBox events must go in the code pane for the worksheet that contains them. In your case, that would be the Sheet1 code pane. The GotFocus event occurs when the user selects the ComboBox. When that happens, the following event sub runs:
Private Sub ComboBox1_GotFocus()
RandomValueMacro
End Sub
ComboBoxQ28330547.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Very Much For Your Help . Everything seems to be working now.
Open in new window
RandomComboBoxSelectionQ28330547.xlsm