Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 validation macros
Hi,
I have two different cells with different validation results. I would a macro to run for each of them depending on my scenario. It works for cell A14, but does not work for A16. Why? What is wrong with this picture? Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Target() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Worksheet1_Change(ByVal Period As Range) Application.ScreenUpdating = False If Period.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro1_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Period() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 validation macros
Hi
As you can only have one worksheet_change macro, you have to do it like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True ElseIf Target.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Regards, Per "MrRJ" skrev i meddelelsen ... Hi, I have two different cells with different validation results. I would a macro to run for each of them depending on my scenario. It works for cell A14, but does not work for A16. Why? What is wrong with this picture? Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Target() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Worksheet1_Change(ByVal Period As Range) Application.ScreenUpdating = False If Period.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro1_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Period() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 validation macros
Hi
As you can only have one worksheet_change macro, you have to do it like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True ElseIf Target.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Regards, Per "MrRJ" skrev i meddelelsen ... Hi, I have two different cells with different validation results. I would a macro to run for each of them depending on my scenario. It works for cell A14, but does not work for A16. Why? What is wrong with this picture? Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Target() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Worksheet1_Change(ByVal Period As Range) Application.ScreenUpdating = False If Period.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro1_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Period() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 validation macros
That is awesome. It works. Thanks Per
"Per Jessen" wrote: Hi As you can only have one worksheet_change macro, you have to do it like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True ElseIf Target.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Regards, Per "MrRJ" skrev i meddelelsen ... Hi, I have two different cells with different validation results. I would a macro to run for each of them depending on my scenario. It works for cell A14, but does not work for A16. Why? What is wrong with this picture? Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Target() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Worksheet1_Change(ByVal Period As Range) Application.ScreenUpdating = False If Period.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro1_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Period() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 validation macros
That is awesome. It works. Thanks Per
"Per Jessen" wrote: Hi As you can only have one worksheet_change macro, you have to do it like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True ElseIf Target.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Regards, Per "MrRJ" skrev i meddelelsen ... Hi, I have two different cells with different validation results. I would a macro to run for each of them depending on my scenario. It works for cell A14, but does not work for A16. Why? What is wrong with this picture? Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$A$14" Then Application.EnableEvents = False Call selectedmacro_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Target() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B1:IV1") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Worksheet1_Change(ByVal Period As Range) Application.ScreenUpdating = False If Period.Address = "$A$16" Then Application.EnableEvents = False Call selectedmacro1_click Application.EnableEvents = True End If Application.ScreenUpdating = True End Sub Private Sub selectedmacro1_click() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub Private Sub Period() Range("A:IV").EntireColumn.Hidden = False For Each c In Range("B5:IV5") If c < 1 Then Columns(c.Column).Hidden = True Next c Range("A10").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros & Validation | New Users to Excel | |||
ID Validation using macros in excel | Excel Programming | |||
Validation/Macros | Excel Programming | |||
Validation/Macros | Excel Programming | |||
Validation/Macros | Excel Programming |