| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for a one time triggered copy of cell value | Excel Discussion (Misc queries) | |||
| Macro triggered by date | Excel Programming | |||
| Macro triggered by a cell value change | Excel Programming | |||
| Can a macro be triggered when a cell is selected? | Excel Programming | |||
| macro triggered by a change to a cell | Excel Programming | |||
Hybrid Mode
