|
macro triggered by changes to cell only works if i run it twice
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! |
macro triggered by changes to cell only works if i run it twice
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" wrote in message ... 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! |
macro triggered by changes to cell only works if i run it twic
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" wrote in message ... 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! . |
macro triggered by changes to cell only works if i run it twic
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" wrote in message ... 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! . |
| All times are GMT +1. The time now is 06:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com