Worksheet_Change?
in [Programming]
|
From: spreadsheetlady on 28 May 2010 10:29 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
From: ryguy7272 on 28 May 2010 10:46 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
From: Mike H on 28 May 2010 10:48 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
From: Gord Dibben on 28 May 2010 10:57 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 <spreadsheetlady(a)discussions.microsoft.com> 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
|
Pages: 1 Prev: Automation of Excel Report using Access Data Next: Print sheets based on cell value |