UserForm

[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

 

[lovepeaceofmind]

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

Is there anybody interested in this? Thanks,

 

[Susan]

ok, george, i'll bite
but you may be sorry.............!

you seem to be employing two methods that perhaps could be combined
into one method, the userform. but to answer your specific questions:

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?

an option button is designed to be clicked once. once it's value is
set to true, you can't change the value to false (AFAIK) until you
click another option button in the group, thereby setting that one to
true & the previous one to false. perhaps you want to change the
option buttons to command buttons - they can be clicked repeatedly
regardless of which other ones have been clicked. then clicking your
command button #4 would automatically re-show the userform. clicking
the ok button on the userform would hide it again.


i'm not understanding you completely here........ as to how many
comboboxes you have (you say "under" the userform but i don't know if
you mean "within" the userform or literally "underneath" it on the
spreadsheet).
so if there are two comboboxes, to which one are you referring with
the OptionIndex?
if it's the combobox on the userform that has changed, do you want it
to continue to show the previous selection? or be blank?
if it's the combobox on the worksheet that has changed, same
question........
maybe i can help if i understand a little better what you're doing.

susan

 

[George]

Hi, Susan:

Thank you so much for your response.

Your answer to the first problem is to-the-point. I agree with what
you said and I don't have any questions about it.

Now let me clarify my SECOND problem. For simplicity, let's make the
following within excel sheet1:

ColumnA ColumnB
Row1 Option1 CellLink
Row2 Option2
Row3
Row4 10 OptionIndex
Row5 20
Row6 30

Now, I make a combobox within excel sheet1 which has Cell Range $A$1:$A
$2 and Cell Link $B$1. If I select Option1 from the combobox, then a
number 1 will show up in cell B1. Nothing else will happen. However,
if I select Option2 from this same combobox, I want a UserForm1 to
show up. This UserForm1 will contain a combobox which has a list of
10,20,and 30 for people to select. For example, if I select 20 and I
click a "Ok" button, the UserForm1 will disappear and Cell B4 will
have a number of 20. However, if I re-popup the UserForm1 and select
10 and click a "cancel" button (or click "X" to close the UserForm1),
I still want a number of 20 showing in cell B4. Right now, the problem
is cell B4 will have a number of 10 instead of 20 even though I "X"
the UserForm1. I need your advice to correct this. Hopefully, the
problem is clear to you to understand.

I look forward to hearing from you!

Thanks,

George

 

[Susan]

ahh! ok.
the first thing you have to do is force the user to use the CANCEL
button instead of the "x". because (AFAIK) you can't code the "x",
but you can code the cancel button.


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button to close the form", vbInformation

End If
End Sub


now i have a little problem..... you said your "ok" button code was
simply:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

how are you getting the option into the B4 range? is it a
optionbutton4_click event? if it's a click event then it's going to
mess up what i do below with the cancel button. otherwise i would
think you would have to have some coding in there that put the
userform option button value into the spreadsheet & i don't see any of
that........... (question to self - do userform comboboxes have cell
links???? - quick look, not that i see.....)

but anyway, i digress. all you have to do is add a cancel button
next to your ok button. name it cmdCancel & put this in your userform
coding. just keep in mind if your info is going to the spreadsheet
via an unseen (to me) click event, this will not work, because the
value will have already changed. if you've got a link or click event
running around in there, you'll have to change that & make the value
go to myCell in the "ok" coding before hiding the userform.


sub cmdCancel_click()

dim myCell as range

set myCell = worksheet.range("B4")
myCell.value = myCell.value
me.hide

end sub


i don't know if this has helped any, beside the can't-use-the-x-
button, which isn't mine, i snagged it off the newsgroup quite some
time ago.

susan

 

[George]

Hi, Susan:

Thank you so much for your help.

I will digest what you wrote and reply to you shortly.

I hope you have a wonderful day!

Thanks again,

George

 

[George]

ahh! ok.
the first thing you have to do is force the user to use the CANCEL
button instead of the "x". because (AFAIK) you can't code the "x",
but you can code the cancel button.

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button to close the form", vbInformation

End If
End Sub

now i have a little problem..... you said your "ok" button code was
simply:
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

how are you getting the option into the B4 range? is it a
optionbutton4_click event? if it's a click event then it's going to
mess up what i do below with the cancel button. otherwise i would
think you would have to have some coding in there that put the
userform option button value into the spreadsheet & i don't see any of
that........... (question to self - do userform comboboxes have cell
links???? - quick look, not that i see.....)

but anyway, i digress. all you have to do is add a cancel button
next to your ok button. name it cmdCancel & put this in your userform
coding. just keep in mind if your info is going to the spreadsheet
via an unseen (to me) click event, this will not work, because the
value will have already changed. if you've got a link or click event
running around in there, you'll have to change that & make the value
go to myCell in the "ok" coding before hiding the userform.

sub cmdCancel_click()

dim myCell as range

set myCell = worksheet.range("B4")
myCell.value = myCell.value
me.hide

end sub

i don't know if this has helped any, beside the can't-use-the-x-
button, which isn't mine, i snagged it off the newsgroup quite some
time ago.

susan









- Show quoted text -

Hi, Susan:

Thanks. I think we are almost there after I read your message.

Firstly, the "X" is working. Whenever I click the "X", a message will
tell me to use the button to close the form.

Now, a little bit more left over. That's, I am not sure how to use
cmdCancel_Click macro.

***** Here is what I need*****
If I choose Option1, I DO NOT need any userform to show up.
If I choose Option2, I need a userform to show up. The user will need
to choose one number (10, 20, or 30) from the UserForm drop down list.
After the user select a number, there are two cases: Case No.1) If she/
he click "Ok" button located in the same UserForm, the userform will
close and cell B4 will have the number that the user JUST SELECTED;
Case No.2) If she/he click "cmdCancel" button located in the same
userform next to "Ok" button, the userform will close and cell B4 will
stay with the PREVIOUS number.

***** Here is what I did *****
Define A4:A6 as "List". Define B4 as "OptionIndex". Put "List" in the
"RowSource" properties of ComboBox1 in UserForm1. Put "OptionIndex" in
the "ControlSource" properties of ComboBox 1 in UserForm1. Make a
commandButton1 serving as "Ok" button and commandButton2 serving as
"cmdCancel" button within the UserForm.

Maybe I did something wrong and the "cmdCancel" button DOES NOT work.

For your conveniece, I attached an excel file to your personnal e-mail
box. Please advise!

Thanks again and have a nice day!

George

 

[Susan]

responded via private e-mail
final coding:

'for combobox on sheet
Sub DropDown1_Change()

If ActiveSheet.Range("B1") = 2 Then
UserForm1.Show
End If

End Sub


'userform coding
Option Explicit

Public ws As Worksheet
Public wb As Workbook
Public myCell As Range
Public oControl As Control
'

Private Sub CommandButton1_Click()

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

Set myCell = ws.Range("b4")
Set oControl = Me.Controls("Combobox1")

myCell.Value = oControl.Value

Me.Hide

End Sub


Private Sub cmdCancel_Click()

Set myCell = ws.Range("b4")

myCell.Value = myCell.Value

Me.Hide

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button to close the form", vbInformation

End If
End Sub