Jacob Skaria
2/2/2010 1:45:00 PM
Since Target is a Range object you can use the .Address property as below.
If Target.Address = "$F$12" Then Call ChangeStops
--
Jacob
"oli merge" wrote:
> perfect, thanks!
>
> "Bob Phillips" wrote:
>
> > Try using the Change event
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > If Target.Column = 6 And Target.Row = 12 Then
> >
> > Call ChangeStops
> >
> > End If
> >
> > HTH
> >
> > Bob
> >
> > "oli merge" <olimerge@discussions.microsoft.com> wrote in message
> > news:A0B6F448-2158-434F-8503-1EE674050217@microsoft.com...
> > > Hi,
> > >
> > > While developing a form with some automated elements I have come into a
> > > problem about triggering a macro when a user selects an option from a drop
> > > down cell (the drop down list is created through validation).
> > >
> > > I have previously been running the macro whenever ANY cell changes in the
> > > workbook, which was working fine. However, this started interfering with
> > > another macro I have added doing something else, so i have been trying to
> > > get
> > > my orginal macro to only run when the specific cell (F12) changes by a
> > > user
> > > selecting a dropdown option.
> > >
> > > I have tried several ways, with the last attempt I used the following code
> > > to call my macro:
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > >
> > > If Target.Column = 6 And Target.Row = 12 Then
> > >
> > > Call ChangeStops
> > >
> > > End If
> > >
> > > The problem is that the macro "ChangeStops" only seemto work now if I
> > > change
> > > the cell F12 twice, whereas before when I triggered it from any cell
> > > change
> > > it would work immediately.
> > >
> > > The code for the Macro "ChangeStops" is:
> > >
> > > Private Sub ChangeStops()
> > > Application.DisplayAlerts = False
> > > Application.ScreenUpdating = False
> > >
> > > Select Case Range("F12").Value
> > >
> > > Case "Mailing"
> > >
> > > EmailMSlist.Visible = False
> > > TelMSlist.Visible = False
> > > Mslist.Visible = True
> > > ActiveSheet.Rows("54:57").Hidden = False
> > > ActiveSheet.Rows("55:56").Hidden = True
> > >
> > > Case "Email"
> > >
> > > EmailMSlist.Visible = True
> > > TelMSlist.Visible = False
> > > Mslist.Visible = False
> > > ActiveSheet.Rows("54:57").Hidden = False
> > > ActiveSheet.Rows("54:54").Hidden = True
> > > ActiveSheet.Rows("56:56").Hidden = True
> > > Case "Telemarketing"
> > >
> > > EmailMSlist.Visible = False
> > > TelMSlist.Visible = True
> > > Mslist.Visible = False
> > > ActiveSheet.Rows("54:57").Hidden = False
> > > ActiveSheet.Rows("54:55").Hidden = True
> > > End Select
> > > Application.DisplayAlerts = True
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > >
> > >
> > > Thanks!
> >
> >
> > .
> >