G
George
Fellow folks:
I have a ComboBox (View/ToolBars/Forms) on excel sheet1. This ComboBox
has 5 Options (Option1~Option5) with an input range $A$1:$A:$5 and
cell link $B$1. What I need is if I select Option4, I must pick one
and only one number of the ten numbers (1~10) from a ComboBox in a
UserForm1. Here is what I did. Define A11:A20 as Range and define B11
as OptionIndex, Alt+F11, insert a UserForm1, put OptionIndex in
Control Source and Range in RowSource under ComboBox1 properties. Make
a CommandButton1 on this same UserForm1 and change the text to "OK".
Then I assign this code under module1 to ComboBox under excel Sheet1:
Sub DropDown1_Change()
If ActiveSheet.Range("B1") = 4 Then
UserForm1.Show
End If
End Sub
Also, I assign the following code to CommandButton1 under UserForm1:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub
Here are on question and two problems:
Question 1: What I did is correct? Please comments as this is my first
time to use UserForm.
Problem1: If I select Option4 from the ComboBox under Excel Sheet1,
this UserForm1 will pop up and allow me to choose one number from the
ComboBox1 under UserForm1. However, after I click "OK" button under
UserForm1 and if I want change my selection, I must choose any of the
other options (other than Option4 ) from the Combobox under sheet1 and
then choose Option4 to pop up the UserForm AGAIN. Is there any way to
prevent this as I want to pop up this UserForm1 whenever I click
Option4?
Problem 2: After I select one number from the ComboBox1 under
UserForm1 and click "OK" button, the UserForm1 will disappear.
However, once I pop up this UserForm1 AGAIN and select another number,
the OptionIndex will be changed even though I close ("X" in the upper
right corner of the UserForm1) the UserForm1 with NO intention to
change my previous selection. How to prevent this to happen?
Any of your help will be highly appreciated!
George
I have a ComboBox (View/ToolBars/Forms) on excel sheet1. This ComboBox
has 5 Options (Option1~Option5) with an input range $A$1:$A:$5 and
cell link $B$1. What I need is if I select Option4, I must pick one
and only one number of the ten numbers (1~10) from a ComboBox in a
UserForm1. Here is what I did. Define A11:A20 as Range and define B11
as OptionIndex, Alt+F11, insert a UserForm1, put OptionIndex in
Control Source and Range in RowSource under ComboBox1 properties. Make
a CommandButton1 on this same UserForm1 and change the text to "OK".
Then I assign this code under module1 to ComboBox under excel Sheet1:
Sub DropDown1_Change()
If ActiveSheet.Range("B1") = 4 Then
UserForm1.Show
End If
End Sub
Also, I assign the following code to CommandButton1 under UserForm1:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub
Here are on question and two problems:
Question 1: What I did is correct? Please comments as this is my first
time to use UserForm.
Problem1: If I select Option4 from the ComboBox under Excel Sheet1,
this UserForm1 will pop up and allow me to choose one number from the
ComboBox1 under UserForm1. However, after I click "OK" button under
UserForm1 and if I want change my selection, I must choose any of the
other options (other than Option4 ) from the Combobox under sheet1 and
then choose Option4 to pop up the UserForm AGAIN. Is there any way to
prevent this as I want to pop up this UserForm1 whenever I click
Option4?
Problem 2: After I select one number from the ComboBox1 under
UserForm1 and click "OK" button, the UserForm1 will disappear.
However, once I pop up this UserForm1 AGAIN and select another number,
the OptionIndex will be changed even though I close ("X" in the upper
right corner of the UserForm1) the UserForm1 with NO intention to
change my previous selection. How to prevent this to happen?
Any of your help will be highly appreciated!
George