08/05/11 15:46
trittico69
Vorrei far si che mi parta automaticamente (anzi che manualmente) una macro al cambiamento automatico di orario in tre celle dove le ore vengono messe li tramite formule
excel 2003
Nel foglio ho inserito questo codice
E nel modulo
Chi mi aiuta a mettere un evento Calculate?
excel 2003
Nel foglio ho inserito questo codice
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("AX1,AY1,AZ1"), Target) Is Nothing Then If Range("AX1") <> "" And Range("AY1") <> "" _ And Range("AZ1") <> "" Then Call minuti End If End Sub
E nel modulo
Sub minuti() Dim AX As Range Dim AY As Range Dim AZ As Range Dim F As Range Dim G As Range Dim H As Range Set AX = ThisWorkbook.Sheets("GEN ").Range("AX1") Set AY = ThisWorkbook.Sheets("GEN ").Range("AY1") Set AZ = ThisWorkbook.Sheets("GEN ").Range("AZ1") Set F = ThisWorkbook.Sheets("GEN ").Range("F10") Set G = ThisWorkbook.Sheets("GEN ").Range("G10") Set H = ThisWorkbook.Sheets("GEN ").Range("H10") Select Case True 'PRIMO CASO Case VBA.Minute(AX) > 30 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) = 0 Application.EnableEvents = False F = VBA.Hour(AX) + 1 G = VBA.Hour(AY) H = VBA.Hour(AZ) Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 30 And VBA.Minute(AZ) = 0 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) Case VBA.Minute(AX) = 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 30 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 1 'SECONDO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) = 0 _ And VBA.Minute(AX) + VBA.Minute(AY) > 30 And VBA.Minute(AX) _ + VBA.Minute(AY) <= 60 Application.EnableEvents = False F = VBA.Hour(AX) + 1 G = VBA.Hour(AY) H = VBA.Hour(AZ) Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) = 0 _ And VBA.Minute(AX) + VBA.Minute(AY) > 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) 'TERZO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AZ) > 30 And VBA.Minute(AX) _ + VBA.Minute(AZ) <= 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) Case VBA.Minute(AX) > 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AZ) > 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 1 'QUARTO CASO Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AY) + VBA.Minute(AZ) > 30 And VBA.Minute(AY) _ + VBA.Minute(AZ) <= 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AY) + VBA.Minute(AZ) > 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 1 'OTTAVO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 150 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) + 2 'SETTIMO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 120 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 150 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 2 'SESTO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 60 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 90 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 1 Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 90 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 120 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) + 1 'QUINTO CASO Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 30 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) + 1 H = VBA.Hour(AZ) Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _ And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 60 Application.EnableEvents = False F = VBA.Hour(AX) G = VBA.Hour(AY) H = VBA.Hour(AZ) + 1 'IN NESSUNA DELLE CONDIZIONI SOPRA Case Else Application.EnableEvents = False F = "" G = "" H = "" End Select Set AX = Nothing Set AY = Nothing Set AZ = Nothing Set F = Nothing Set G = Nothing Set H = Nothing Application.EnableEvents = True End Sub
Chi mi aiuta a mettere un evento Calculate?
Ultima modifica effettuata da trittico69 08/05/11 15:48
aaa