Activating a Form in Excel

[Amy DeLuca]

If the user types "Yes" in cell A1, I want a form to appear (frmFMB). If "No" then just the text to appear in the cell. Is this possible?

 

[dk]

Right click your worksheet tab and choose View Code. This should do what you need:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" And UCase(Target) = "YES" Then frmFMB.Show
End Sub

EDIT - it should say $ then A and then $ and then 1, but for some reason this board doesn't like that

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-22 12:18

 

[Amy DeLuca]

I don't want the user to have to use uppercase. I have a validation box attached to the cell where the user can only coose "Yes" or "No". How can I rewrite it so that the user can just select the "Yes"?

Thanks for your help!!!

 

[Ekim]

Using DK's code, amend to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target = "YES" Then frmFMB.Show
End Sub

The macro goes in a sheet module.

Save the file, close it and then re-open it - the macro should "take" (using Excel 97-SR2). The macro works with a validation "Yes/No" drop-down box in cell A1.

Regards,

Mike

 

[Amy DeLuca]

Ekim...this worked!!! Thank you!
I put a button in the form that says "OK", what is the code that I attach to it to make it close?

Thanks!!!

 

[Ekim]

Amy,
Put this in the form module (while in the VB editor, double click on the form)

Private Sub CommandButton1_Click()
Unload Me ' or Unload frmFMB
End Sub

The code assumes that your Command Button is named CommandButton1 (if not, change to appropriate description).

Regards,


Mike