Worksheet_Change?

  • Thread starter Thread starter spreadsheetlady
  • Start date Start date
S

spreadsheetlady

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.
 
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.
 
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
 
Back
Top