macro triggered by changes to cell only works if i run it twice

  • Thread starter Thread starter oli merge
  • Start date Start date
O

oli merge

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!
 
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
 
perfect, thanks!

Bob Phillips said:
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




.
Click to expand...
 
Since Target is a Range object you can use the .Address property as below.

If Target.Address = "$F$12" Then Call ChangeStops
 
Back
Top