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



On Jun 6, 10:15 am, George wrote:
On Jun 6, 7:29 am, Susan wrote:

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

On Jun 5, 9:30 pm, George wrote:

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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -