Questo sito utilizza cookies solo per scopi di autenticazione sul sito e nient'altro. Nessuna informazione personale viene tracciata. Leggi l'informativa sui cookies.
Username: Password: oppure
Altri Linguaggi - evento calculate
Forum - Altri Linguaggi - evento calculate

Avatar
trittico69 (Normal User)
Pro


Messaggi: 144
Iscritto: 02/04/2010

Segnala al moderatore
Postato alle 17:46
Domenica, 08/05/2011
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

Codice sorgente - presumibilmente VB.NET
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Not Intersect(Range("AX1,AY1,AZ1"), Target) Is Nothing Then
  3.     If Range("AX1") <> "" And Range("AY1") <> "" _
  4.     And Range("AZ1") <> "" Then Call minuti
  5. End If
  6. End Sub


E nel modulo

Codice sorgente - presumibilmente VB.NET
  1. Sub minuti()
  2. Dim AX As Range
  3. Dim AY As Range
  4. Dim AZ As Range
  5. Dim F As Range
  6. Dim G As Range
  7. Dim H As Range
  8.  
  9. Set AX = ThisWorkbook.Sheets("GEN ").Range("AX1")
  10. Set AY = ThisWorkbook.Sheets("GEN ").Range("AY1")
  11. Set AZ = ThisWorkbook.Sheets("GEN ").Range("AZ1")
  12. Set F = ThisWorkbook.Sheets("GEN ").Range("F10")
  13. Set G = ThisWorkbook.Sheets("GEN ").Range("G10")
  14. Set H = ThisWorkbook.Sheets("GEN ").Range("H10")
  15.  
  16. Select Case True
  17.  
  18. 'PRIMO CASO
  19. Case VBA.Minute(AX) > 30 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) = 0
  20.     Application.EnableEvents = False
  21.     F = VBA.Hour(AX) + 1
  22.     G = VBA.Hour(AY)
  23.     H = VBA.Hour(AZ)
  24.  
  25. Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 30 And VBA.Minute(AZ) = 0
  26.     Application.EnableEvents = False
  27.     F = VBA.Hour(AX)
  28.     G = VBA.Hour(AY) + 1
  29.     H = VBA.Hour(AZ)
  30.    
  31. Case VBA.Minute(AX) = 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 30
  32.     Application.EnableEvents = False
  33.     F = VBA.Hour(AX)
  34.     G = VBA.Hour(AY)
  35.     H = VBA.Hour(AZ) + 1
  36.    
  37. 'SECONDO CASO
  38. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) = 0 _
  39. And VBA.Minute(AX) + VBA.Minute(AY) > 30 And VBA.Minute(AX) _
  40. + VBA.Minute(AY) <= 60
  41.     Application.EnableEvents = False
  42.     F = VBA.Hour(AX) + 1
  43.     G = VBA.Hour(AY)
  44.     H = VBA.Hour(AZ)
  45.    
  46. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) = 0 _
  47. And VBA.Minute(AX) + VBA.Minute(AY) > 60
  48.     Application.EnableEvents = False
  49.     F = VBA.Hour(AX)
  50.     G = VBA.Hour(AY) + 1
  51.     H = VBA.Hour(AZ)
  52.    
  53. 'TERZO CASO
  54. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 0 _
  55. And VBA.Minute(AX) + VBA.Minute(AZ) > 30 And VBA.Minute(AX) _
  56. + VBA.Minute(AZ) <= 60
  57.     Application.EnableEvents = False
  58.     F = VBA.Hour(AX)
  59.     G = VBA.Hour(AY) + 1
  60.     H = VBA.Hour(AZ)
  61.    
  62.  
  63. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) = 0 And VBA.Minute(AZ) > 0 _
  64. And VBA.Minute(AX) + VBA.Minute(AZ) > 60
  65.     Application.EnableEvents = False
  66.     F = VBA.Hour(AX)
  67.     G = VBA.Hour(AY)
  68.     H = VBA.Hour(AZ) + 1
  69.    
  70.  
  71. 'QUARTO CASO
  72. Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  73.     And VBA.Minute(AY) + VBA.Minute(AZ) > 30 And VBA.Minute(AY) _
  74.     + VBA.Minute(AZ) <= 60
  75.     Application.EnableEvents = False
  76.     F = VBA.Hour(AX)
  77.     G = VBA.Hour(AY) + 1
  78.     H = VBA.Hour(AZ)
  79.  
  80. Case VBA.Minute(AX) = 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  81.     And VBA.Minute(AY) + VBA.Minute(AZ) > 60
  82.     Application.EnableEvents = False
  83.     F = VBA.Hour(AX)
  84.     G = VBA.Hour(AY)
  85.     H = VBA.Hour(AZ) + 1
  86.    
  87.  
  88. 'OTTAVO CASO
  89. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  90. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 150
  91.     Application.EnableEvents = False
  92.     F = VBA.Hour(AX)
  93.     G = VBA.Hour(AY) + 1
  94.     H = VBA.Hour(AZ) + 2
  95.  
  96.  
  97. 'SETTIMO CASO
  98. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  99. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 120 _
  100. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 150
  101.     Application.EnableEvents = False
  102.     F = VBA.Hour(AX)
  103.     G = VBA.Hour(AY)
  104.     H = VBA.Hour(AZ) + 2
  105.  
  106. 'SESTO CASO
  107. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  108. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 60 _
  109. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 90
  110.     Application.EnableEvents = False
  111.     F = VBA.Hour(AX)
  112.     G = VBA.Hour(AY)
  113.     H = VBA.Hour(AZ) + 1
  114.  
  115. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  116. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 90 _
  117. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 120
  118.     Application.EnableEvents = False
  119.     F = VBA.Hour(AX)
  120.     G = VBA.Hour(AY) + 1
  121.     H = VBA.Hour(AZ) + 1
  122.  
  123. 'QUINTO CASO
  124. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  125. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 30 _
  126. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) <= 60
  127.     Application.EnableEvents = False
  128.     F = VBA.Hour(AX)
  129.     G = VBA.Hour(AY) + 1
  130.     H = VBA.Hour(AZ)
  131.  
  132. Case VBA.Minute(AX) > 0 And VBA.Minute(AY) > 0 And VBA.Minute(AZ) > 0 _
  133. And VBA.Minute(AX) + VBA.Minute(AY) + VBA.Minute(AZ) > 60
  134.     Application.EnableEvents = False
  135.     F = VBA.Hour(AX)
  136.     G = VBA.Hour(AY)
  137.     H = VBA.Hour(AZ) + 1
  138.  
  139. 'IN NESSUNA DELLE CONDIZIONI SOPRA
  140. Case Else
  141.     Application.EnableEvents = False
  142.     F = ""
  143.     G = ""
  144.     H = ""
  145.  
  146. End Select
  147.  
  148. Set AX = Nothing
  149. Set AY = Nothing
  150. Set AZ = Nothing
  151. Set F = Nothing
  152. Set G = Nothing
  153. Set H = Nothing
  154. Application.EnableEvents = True
  155. End Sub



Chi mi aiuta a mettere un evento Calculate?

Ultima modifica effettuata da trittico69 il 08/05/2011 alle 17:48
PM Quote