Hi,
I'm trying to automatically open a Form when a target cell contains an exact
text.

Example:
IF cell A1 says: "Oranges"
THEN
Open Form1.

I've only opened Forms using command buttons.
I was thinking maybe using Sub Worksheet_Change to do this?

If cell A1 says anything else, (false) Do not open Form1.

Thank-you in advance
Amy

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value 200 Then
UserForm1.Show
End If
End If
End Sub

Let's say the value in cell A1 is 200; UserForm1 pops open.
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"spreadsheetlady" wrote:

Hi,
I'm trying to automatically open a Form when a target cell contains an exact
text.

Example:
IF cell A1 says: "Oranges"
THEN
Open Form1.

I've only opened Forms using command buttons.
I was thinking maybe using Sub Worksheet_Change to do this?

If cell A1 says anything else, (false) Do not open Form1.

Thank-you in advance
Amy

Hi,

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If UCase(Target) = "ORANGES" Then
Application.EnableEvents = False

UserForm1.Show
Application.EnableEvents = True
End If
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"spreadsheetlady" wrote:

Hi,
I'm trying to automatically open a Form when a target cell contains an exact
text.

Example:
IF cell A1 says: "Oranges"
THEN
Open Form1.

I've only opened Forms using command buttons.
I was thinking maybe using Sub Worksheet_Change to do this?

If cell A1 says anything else, (false) Do not open Form1.

Thank-you in advance
Amy

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = "Oranges" Then
UserForm1.Show
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 28 May 2010 07:29:02 -0700, spreadsheetlady
wrote:

Hi,
I'm trying to automatically open a Form when a target cell contains an exact
text.

Example:
IF cell A1 says: "Oranges"
THEN
Open Form1.

I've only opened Forms using command buttons.
I was thinking maybe using Sub Worksheet_Change to do this?

If cell A1 says anything else, (false) Do not open Form1.

Thank-you in advance
Amy