Macro ne marche pas en 2010 mais marche en 2003
d
bonjour tout le monde,
j'ai besoin de l'aide des experts excel pour résoudre mon problème avec une macro qui fait des graphiques mais ne met pas à jour les mois dans l'axe des absissces( il met seulement jan-00 dans tous les mis de l'année). excel le fait parfaitement en 2003 mais pas en 2010. je peux mettre le code si vous avez le courage de jeter un coup d'oeil.
Bonjour
drybest a écrit :je peux mettre le code si vous avez le courage de jeter un coup d'oeil.
C'est un minimum car on est pas des devins ! Mais mieux, joins ton fichier.
Amicalement
Nad
d
Bonjour,
je précise que je suis débutant et que ce n'est pas moi qui a tout fait. j'ai beaucoup de code. c'est parti.
merci pour votre aide.
le code du workbook
Private Sub Workbook_BeforeClose(cancel As Boolean)
Set fs = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
fs.deletefile TEMP_DIR + "abis.dsn", True
fs.deletefile TEMP_DIR + "Schema.ini", True
fs.deletefile TEMP_DIR + "TXTODBC.TXT", True
End Sub
Private Sub Workbook_Open()
Dim intlastYear As Integer
Dim nblig As Integer
Dim vad As Date
Dim stmonth, styear As Integer
FLAG_MODIF = 0
LOGENABLE = 0
CHEMIN_ABIS = "C:\Data\ABIS"
With Application
.DecimalSeparator = "."
.ThousandsSeparator = " "
.UseSystemSeparators = False
End With
Sheets("G3").Select
Application.ScreenUpdating = False
LOGLINE = 25
FLAG_CREATION = 0
G3_START_LINE = 11
Call search_user_temp_dir
FLAG_MAJ_SPECIAL_VIEW = 0
If Sheets("Properties").Cells(1, 256).Text <> "OK" Then
LOGENABLE = 1
Call logsproc("START")
'test si il n'y a pas de données dans le fichier TXTODBC.TXT
If verif_data = 0 Then
If Environ("REPORT_SERVER_CLOSE") = "YES" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Exit Sub
End If
Call logsproc("VERIF DATA")
ANA_PERIODE = "CURRENT YEAR"
Sheets("Properties").Cells(1, 200).Value = 0
FLAG_CREATION = 1
Sheets("Properties").Cells(1, 256).Value = "OK"
'MsgBox "COMMENT UNEXPECTED"
nblinestableview = 0
nblinestableviewhide = 0
'show all sheets
Call VEVA_Show_sheets
Call logsproc("SHOW SHEET")
'import datas for selections
Call Import_data
Call logsproc("IMPORT DATA")
Call create_data_plant_odbc
Call logsproc("CREATE ODBC PLANT")
'write currency in sheet properties
Sheets("Properties").Cells(8, 3).Value = Sheets("DATAS").Cells(2, 21).Value
'write date and month of generation in sheet properties
stmonth = Month(Date)
styear = Year(Date)
If stmonth - 1 = 0 Then
Sheets("Properties").Cells(10, 3).Value = "'" + Trim(Str(styear - 1)) + "_12"
vad = "12/" + Trim(Str(styear - 1))
Else
If stmonth - 1 < 10 Then
Sheets("Properties").Cells(10, 3).Value = "'" + Trim(Str(styear)) + "_0" + Trim(Str(stmonth - 1))
vad = "0" + Trim(Str(stmonth - 1)) + "/" + Trim(Str(styear))
Else
Sheets("Properties").Cells(10, 3).Value = "'" + Trim(Str(styear)) + "_" + Trim(Str(stmonth - 1))
vad = Trim(Str(stmonth - 1)) + "/" + Trim(Str(styear))
End If
End If
If stmonth < 10 Then
Sheets("Properties").Cells(10, 4).Value = "'" + Trim(Str(styear)) + "_0" + Trim(Str(stmonth))
Else
Sheets("Properties").Cells(10, 4).Value = "'" + Trim(Str(styear)) + "_" + Trim(Str(stmonth))
End If
'Sheets("Properties").Cells(10, 5).Value = vad
Sheets("Properties").Cells(10, 5).Value = Format(DateAdd("m", -1, Date), "mmm YYYY")
'write the caption in sheet G3 for the year to date values
Sheets("G3").LabelYTDCAL.Caption = "YTD Actual calenderized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + ":"
Sheets("G3").LabelYTDANN.Caption = "YTD Actual annualized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + ":"
Sheets("G3").LabelYTDCarryover.Caption = "YTD Calenderized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + " + carry-over annualized (Sept-Dec" + Str(Val(Left(Sheets("Properties").Cells(10, 3).Value, 4)) - 1) + ") :"
'Sheets("G3").LabelYTDCarryover.Caption = "YTD Actual savings calenderized (included carry-over) " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + ":"
Sheets("Special View").LabelYTDCAL.Caption = "YTD Actual calenderized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + ":"
Sheets("Special View").LabelYTDANN.Caption = "YTD Actual annualized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + ":"
Sheets("Special View").LabelYTDCarryover.Caption = "YTD Calenderized " + Left(Sheets("Properties").Cells(10, 3).Value, 4) + " + carry-over annualized (Sept-Dec" + Str(Val(Left(Sheets("Properties").Cells(10, 3).Value, 4)) - 1) + ") :"
'calculate and update periode in sheets
Call VEVA_update_data_periode("CURRENT YEAR")
Call logsproc("UPDATE DATA PERODE")
'write the currency
Sheets("G3").labcurr.Caption = "Currency = " + Sheets("Properties").Cells(8, 3).Value
Sheets("Special View").labcurr.Caption = "Currency = " + Sheets("Properties").Cells(8, 3).Value
PRIORITY_SELECTION = 1
CUSTOMER_SELECTION = 1
DIVISION_SELECTION = 1
SITE_INVOLVED_SELECTION = 1
TECH_CENTER_SELECTION = 1
CATEGORY_SELECTION = 1
COMMODITY_CODE_AREA_SELECTION = 1
COMMODITY_CODE_SELECTION = 1
PRODUCT_FAMILY_SELECTION = 1
PHASE_SELECTION = 1
VEVA_SOURCE_SELECTION = 1
Call show_selection
Call logsproc("SHOWSELECTION")
'active autofilter
Sheets("DATAS").Select
Rows("1:1").Select
Selection.AutoFilter
'refresh the pivot tables
Sheets("GLOBAL PIVOT").Select
Range("A14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"DATAS!C1:C31"
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("REFRESH GLOBAL PIVOT")
'refresh the pivot tables
Sheets("Carry over sheet").Select
Range("A2:C2").Select
Selection.AutoFill Destination:=Range("A2:C26"), Type:=xlFillCopy
Range("A2:C26").Select
Range("A27:C27").Select
Selection.AutoFill Destination:=Range("A27:C51"), Type:=xlFillCopy
Sheets("Carry over").Select
Range("A14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Carry over sheet'!R1C1:R51C3"
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("REFRESH CARRY OVER")
Worksheets("Actual accumulated").Activate
Sheets("Actual accumulated sheet").Select
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D26"), Type:=xlFillCopy
Range("A27:D27").Select
Selection.AutoFill Destination:=Range("A27:D51"), Type:=xlFillCopy
Sheets("Actual accumulated").Select
Range("A14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Actual accumulated sheet'!R1C1:R51C4"
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("REFRESH ACTUAL ACCUMULATED")
Sheets("Forecast update sheet").Select
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D26"), Type:=xlFillCopy
Range("A27:D27").Select
Selection.AutoFill Destination:=Range("A27:D51"), Type:=xlFillCopy
Range("A52:D52").Select
Selection.AutoFill Destination:=Range("A52:D76"), Type:=xlFillCopy
Range("A77:D77").Select
Selection.AutoFill Destination:=Range("A77:D101"), Type:=xlFillCopy
Range("A102:D102").Select
Selection.AutoFill Destination:=Range("A102:D126"), Type:=xlFillCopy
Range("A127:D127").Select
Selection.AutoFill Destination:=Range("A127:D151"), Type:=xlFillCopy
Range("A152:D152").Select
Selection.AutoFill Destination:=Range("A152:D176"), Type:=xlFillCopy
Sheets("Forecast update").Select
Range("A14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Forecast update sheet'!R1C1:R126C4"
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("REFRESH FORECAST UPDATE")
Sheets("DATA-PARETO").Select
Range("A14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Forecast update sheet'!R1C1:R176C4"
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("REFRESH DATA-PARETO")
Sheets("TABLE1").Select
Range("A16").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"DATAS!C1:C31"
'Sheets("TABLE1").PivotTables("PivotTable1").PivotCache.Refresh
ActiveWorkbook.ShowPivotTableFieldList = False
Call logsproc("TABLE1")
' Call clean_pivot_table
'apply the user's selection and update data in all sheets
Call VEVA_update_datas
Call logsproc("UPDATE DATA")
'create data for the table
Call test_update_table
'hide all sheets
Call VEVA_Hide_sheets
Call logsproc("HIDE SHEET")
'write all values of the selection in sheets properties
Application.ScreenUpdating = True
Sheets("G3").Select
'if subscribed report then save and close workbooks
If Environ("REPORT_SERVER_CLOSE") = "YES" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Call logsproc("END")
LOGENABLE = 0
'active user selection
FLAG_CREATION = 0
FLAG_MODIF = 1
FLAG_MAJ_SPECIAL_VIEW = 1
Else
'show all sheets
Call VEVA_Show_sheets
Call create_data_plant_odbc
'active user selection
FLAG_MODIF = 1
'active autofilter
'hide all sheets
Call VEVA_Hide_sheets
FLAG_MAJ_SPECIAL_VIEW = 1
End If
Application.ScreenUpdating = True
Sheets("G3").Select
Range("A1").Select
End Suble code pour le feuillet
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox "change"
FLAG_MAJ_SPECIAL_VIEW = 1
If FLAG_MAJ_SPECIAL_VIEW = 0 Then Exit Sub
num = Selection.Row
nb = Selection.Rows.Count
col = Selection.Column
If col = 12 Then
If num > G3_START_LINE And nb = 1 Then
num = num - 1
nb = nb + 1
End If
For nline = num To num + nb - 1
Select Case Sheets("Special view").Cells(nline, 12).Text
Case "G"
Range("H" + Trim(Str(nline)) + ":I" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 35
Range("L" + Trim(Str(nline)) + ":AB" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 35
Case "g"
Sheets("Special view").Cells(nline, 12).Value = "G"
Case "R"
Range("H" + Trim(Str(nline)) + ":I" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 38 '3
Range("L" + Trim(Str(nline)) + ":AB" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 38 '3
Case "r"
Sheets("Special view").Cells(nline, 12).Value = "R"
Case "Y"
Range("H" + Trim(Str(nline)) + ":I" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 19
Range("L" + Trim(Str(nline)) + ":AB" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 19
Case "y"
Sheets("Special view").Cells(nline, 12).Value = "Y"
Case "B"
Range("H" + Trim(Str(nline)) + ":I" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 34
Range("L" + Trim(Str(nline)) + ":AB" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = 34
Case "b"
Sheets("Special view").Cells(nline, 12).Value = "B"
Case Else
Range("H" + Trim(Str(nline)) + ":I" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = xlNone
Range("L" + Trim(Str(nline)) + ":AB" + Trim(Str(nline))).Select
Selection.Interior.ColorIndex = xlNone
End Select
Next nline
Range("L" + Trim(Str(nline - 1)) + ":L" + Trim(Str(nline - 1))).Select
End If
End Suble code pour le feuillet G3
Private Sub Cde_category_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Category selection"
UserForm_selection.Show
End Sub
Private Sub Cde_clearfilter_Click()
Dim nbline As Long
Dim sselectionn, sqlselection As String
Dim numcol1, numline1, flagallselected As Integer
UPDATED_SHEET = "G3"
Sheets("G3").Cde_priority.ForeColor = -2147483630
Sheets("G3").Cde_customer.ForeColor = -2147483630
Sheets("G3").Cde_division.ForeColor = -2147483630
Sheets("G3").Cde_site_involved.ForeColor = -2147483630
Sheets("G3").Cde_tech_center.ForeColor = -2147483630
Sheets("G3").Cde_category.ForeColor = -2147483630
Sheets("G3").Cde_commodity_code_area.ForeColor = -2147483630
Sheets("G3").Cde_commodity_code.ForeColor = -2147483630
Sheets("G3").Cde_product_family.ForeColor = -2147483630
Sheets("G3").Cde_phase.ForeColor = -2147483630
Sheets("G3").cde_veva_source.ForeColor = -2147483630
'stop updating screen
Application.ScreenUpdating = False
Call VEVA_Show_sheets
Sheets("G3").cde_refresh_data.ForeColor = 255
Sheets("G3").cde_refresh_data.Caption = "Refresh Data Needed"
'assigne Priority selection
numcol1 = 53
flagallselected = 1
sselection = ""
sqlselection = ""
nbline = 2
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
PRIORITYSELECTION = sqlselection
PRIORITY_SELECTION = 1
Sheets("G3").Cells(1, 7).Value = sselection
Sheets("Special view").Cells(1, 5).Value = sselection
'assigne Customer selection
Sheets("ACTIVE-SELECTION").Select
numcol1 = 55
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
CUSTOMERSELECTION = sqlselection
CUSTOMER_SELECTION = 1
Sheets("G3").Cells(1, 13).Value = sselection
Sheets("Special view").Cells(1, 11).Value = sselection
'assigne Division selection
numcol1 = 57
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
DIVISIONSELECTION = sqlselection
DIVISION_SELECTION = 1
Sheets("G3").Cells(2, 6).Value = sselection
Sheets("Special view").Cells(2, 4).Value = sselection
'assigne Site involved selection
numcol1 = 59
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
SITE_INVOLVEDSELECTION = sqlselection
SITE_INVOLVED_SELECTION = flagallselected
Sheets("G3").Cells(2, 13).Value = sselection
Sheets("Special view").Cells(2, 11).Value = sselection
'assigne Tech center selection
numcol1 = 61
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
TECH_CENTERSELECTION = sqlselection
TECH_CENTER_SELECTION = 1
Sheets("G3").Cells(3, 6).Value = sselection
Sheets("Special view").Cells(3, 4).Value = sselection
'assigne Category selection
numcol1 = 63
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
CATEGORYSELECTION = sqlselection
CATEGORY_SELECTION = flagallselected
Sheets("G3").Cells(3, 13).Value = sselection
Sheets("Special view").Cells(3, 11).Value = sselection
'assigne Commodity code area selection
numcol1 = 65
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
COMMODITY_CODE_AREASELECTION = sqlselection
COMMODITY_CODE_AREA_SELECTION = flagallselected
Sheets("G3").Cells(4, 6).Value = sselection
Sheets("Special view").Cells(4, 4).Value = sselection
'assigne Commodity code selection
numcol1 = 67
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
COMMODITY_CODESELECTION = sqlselection
COMMODITY_CODE_SELECTION = flagallselected
Sheets("G3").Cells(4, 13).Value = sselection
Sheets("Special view").Cells(4, 11).Value = sselection
'assigne Product family selection
numcol1 = 69
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
PRODUCT_FAMILYSELECTION = sqlselection
PRODUCT_FAMILY_SELECTION = flagallselected
Sheets("G3").Cells(5, 6).Value = sselection
Sheets("Special view").Cells(5, 4).Value = sselection
'assigne Phase selection
numcol1 = 71
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
PHASESELECTION = sqlselection
PHASE_SELECTION = flagallselected
Sheets("G3").Cells(5, 13).Value = sselection
Sheets("Special view").Cells(5, 11).Value = sselection
'assigne veva source selection
numcol1 = 73
flagallselected = 1
sselection = ""
sqlselection = ""
Sheets("ACTIVE-SELECTION").Cells(1, numcol1 + 1).Value = 1
nbline = 2
While Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text <> ""
Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1 + 1).Value = 1
If sselection = "" Then
sselection = Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = "'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
Else
sselection = sselection + " , " + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text
sqlselection = sqlselection + ",'" + Sheets("ACTIVE-SELECTION").Cells(nbline, numcol1).Text + "'"
End If
nbline = nbline + 1
Wend
VEVA_SOURCESELECTION = sqlselection
VEVA_SOURCE_SELECTION = flagallselected
Sheets("G3").Cells(6, 6).Value = sselection
Sheets("Special view").Cells(5, 4).Value = sselection
UserForm_selection.Hide
Call set_selection("PRIORITY")
Call VEVA_Hide_sheets
'stop updating screen
Application.ScreenUpdating = True
End Sub
Private Sub Cde_commodity_code_area_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Commodity code area selection"
UserForm_selection.Show
End Sub
Private Sub Cde_commodity_code_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Commodity code selection"
UserForm_selection.Show
End Sub
Private Sub Cde_customer_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Customer selection"
UserForm_selection.Show
End Sub
Private Sub Cde_division_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Division selection"
UserForm_selection.Show
End Sub
Private Sub Cde_phase_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Phase selection"
UserForm_selection.Show
End Sub
Private Sub Cde_priority_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Priority selection"
UserForm_selection.Show
End Sub
Private Sub Cde_product_family_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Product family selection"
UserForm_selection.Show
End Sub
Private Sub cde_refresh_data_Click()
Dim nomfich, chemin As String
nomfich = ActiveWorkbook.Name
chemin = ActiveWorkbook.Path
'si le nom fichier à des paranthèses pour [ et que pas déjà sauvé
If InStr(ActiveWorkbook.Name, "(") > 0 And Sheets("Properties").Cells(2, 256).Text <> "OK" Then
Sheets("Properties").Cells(2, 256).Value = "OK"
'recherche si le fichier existe
With Application.FileSearch
.LookIn = chemin
.Filename = nomfich
If .Execute() > 0 Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set F = fs.GetFile(chemin + "\" + nomfich)
On Error GoTo passauve
F.Delete
Else
End If
End With
ActiveWorkbook.SaveAs Filename:= _
chemin + "\" + nomfich, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
passauve:
If Sheets("G3").Cells(1, 7).Text = "" Or Sheets("G3").Cells(1, 13).Text = "" Or _
Sheets("G3").Cells(2, 6).Text = "" Or Sheets("G3").Cells(2, 13).Text = "" Or _
Sheets("G3").Cells(3, 6).Text = "" Or Sheets("G3").Cells(3, 13).Text = "" Or _
Sheets("G3").Cells(4, 6).Text = "" Or Sheets("G3").Cells(4, 13).Text = "" Or _
Sheets("G3").Cells(5, 6).Text = "" Or Sheets("G3").Cells(5, 13).Text = "" Or _
Sheets("G3").Cells(6, 6).Text = "" Then
MsgBox "A critaria of selection have no data !" + Chr$(10) + "Clear Filter and apply a new one.", vbCritical
Exit Sub
End If
If Sheets("ACTIVE-SELECTION").Cells(2, 53).Text = "" Or Sheets("ACTIVE-SELECTION").Cells(2, 55).Text = "" Or _
Sheets("ACTIVE-SELECTION").Cells(2, 57).Text = "" Or Sheets("ACTIVE-SELECTION").Cells(2, 59).Text = "" Or _
Sheets("ACTIVE-SELECTION").Cells(2, 61).Text = "" Or Sheets("ACTIVE-SELECTION").Cells(2, 63).Text = "" Or _
Sheets("ACTIVE-SELECTION").Cells(2, 65).Text = "" Or Sheets("ACTIVE-SELECTION").Cells(2, 67).Text = "" Or _
Sheets("ACTIVE-SELECTION").Cells(2, 69).Text = "" Or Sheets("ACTIVE-SELECTION").Cells(2, 71).Text = "" Or _
Sheets("ACTIVE-SELECTION").Cells(2, 73).Text = "" Then
MsgBox "A critaria of selection have no data !" + Chr$(10) + "Clear Filter and apply a new one.", vbCritical
Exit Sub
End If
FLAG_MAJ_SPECIAL_VIEW = 0
On Error GoTo 0
Application.ScreenUpdating = False
Call VEVA_Show_sheets
If Sheets("G3").Opt_cur_year.Value = True Then
ANA_PERIODE = "CURRENT YEAR"
Else
ANA_PERIODE = "NEXT YEAR"
End If
Sheets("G3").Select
Sheets("G3").cde_refresh_data.ForeColor = 8388608
Sheets("G3").cde_refresh_data.Caption = "Refresh Data"
Rows("10:65536").Select
Selection.EntireRow.Hidden = False
'apply the user's selection
'Call VEVA_update_datas
'update datas
Call apply_selection("PRIORITY")
Call apply_selection("CUSTOMER")
Call apply_selection("DIVISION")
Call apply_selection("SITE_INVOLVED")
Call apply_selection("TECH_CENTER")
Call apply_selection("CATEGORY")
Call apply_selection("COMMODITY_CODE_AREA")
Call apply_selection("COMMODITY_CODE")
Call apply_selection("PRODUCT_FAMILY")
Call apply_selection("PHASE")
Call apply_selection("VEVA_SOURCE")
On Error Resume Next
If ANA_PERIODE = "CURRENT YEAR" Then
Sheets("TABLE1").Select
'affiche toutes les lignes next_view
Sheets("TABLE1").Range("A12").Select
On Error Resume Next
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("NEXT_VIEW").PivotItems("1").Visible = True
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("NEXT_VIEW").PivotItems("0").Visible = True
'affiche que les lignes current_view à 1
Sheets("TABLE1").Range("A12").Select
Err.Clear
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("CURRENT_VIEW").PivotItems("1").Visible = True
If Err.Number <> 0 Then
NO_DATA = 1
Else
NO_DATA = 0
End If
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("CURRENT_VIEW").PivotItems("0").Visible = False
On Error GoTo 0
End If
If ANA_PERIODE = "NEXT YEAR" Then
Sheets("TABLE1").Select
'affiche toutes les lignes next_view
Sheets("TABLE1").Range("A12").Select
On Error Resume Next
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("CURRENT_VIEW").PivotItems("1").Visible = True
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("CURRENT_VIEW").PivotItems("0").Visible = True
'affiche que les lignes current_view à 1
Sheets("TABLE1").Range("A12").Select
Err.Clear
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("NEXT_VIEW").PivotItems("1").Visible = True
If Err.Number <> 0 Then
NO_DATA = 1
Else
NO_DATA = 0
End If
On Error GoTo 0
Sheets("TABLE1").PivotTables("PivotTable1").PivotFields("NEXT_VIEW").PivotItems("0").Visible = False
End If
On Error GoTo 0
Call test_update_table
Call VEVA_Hide_sheets
Call VEVA_Hide_table_lines
FLAG_MAJ_SPECIAL_VIEW = 1
Application.ScreenUpdating = True
End Sub
Private Sub Cde_site_involved_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Site involved selection"
UserForm_selection.Show
End Sub
Private Sub Cde_tech_center_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "Tech center selection"
UserForm_selection.Show
End Sub
Private Sub Cdetarget_Click()
UserForm1.Show
End Sub
Private Sub Opt_cur_year_Click()
Application.ScreenUpdating = False
ANA_PERIODE = "CURRENT YEAR"
Call VEVA_update_data_periode("CURRENT YEAR")
Call cde_refresh_data_Click
End Sub
Private Sub Opt_next_year_Click()
Application.ScreenUpdating = False
ANA_PERIODE = "NEXT YEAR"
Call VEVA_update_data_periode("NEXT YEAR")
Call cde_refresh_data_Click
End Sub
Private Sub cde_veva_source_Click()
UPDATED_SHEET = "G3"
UserForm_selection.Caption = "VEVA Source selection"
UserForm_selection.Show
End Sub
Private Sub vevaTextnblines_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Application.ScreenUpdating = False
Call VEVA_Hide_table_lines
Application.ScreenUpdating = True
End If
End Subet j'ai un autre code
Attribute VB_Name = "Mod_generate_view"
Sub generate_view()
Call logsproc("START GENERATE VIEW")
FLAG_MAJ_SPECIAL_VIEW = 0
Sheets("Special View").Select
Range("A1").Select
Columns("L:O").Select
Range("L8").Activate
Selection.EntireColumn.Hidden = False
'récupère le nombre de ligne
nblinestableview = Sheets("Properties").Cells(1, 201).Value
nblinestableviewhide = Sheets("Properties").Cells(1, 202).Value
If nblinestableviewhide < (nblinestableview + G3_START_LINE - 1) And nblinestableviewhide >= G3_START_LINE Then
Rows(Trim(Str(nblinestableviewhide - 1)) + ":" + Trim(Str(nblinestableview + G3_START_LINE))).Select
Selection.EntireRow.Hidden = False
End If
Call logsproc("AFFICHE TOUTES LES LIGNES")
'efface les lignes
Sheets("Special View").Select
If nblinestableview > 1 Then
Rows(Trim(Str(G3_START_LINE + 1)) + ":" + Trim(Str(nblinestableview + G3_START_LINE - 1))).Select
Selection.Delete Shift:=xlUp
End If
Call logsproc("EFFACE LES LIGNES")
'met le nombre de ligne au nombre de ligne sur View
nblinestableview = nblinestable
'le sauvegarde
Sheets("Properties").Cells(1, 201).Value = nblinestableview
G3_START_LINE = 11
If NO_DATA = 1 Then
Rows(Trim(Str(G3_START_LINE)) + ":" + Trim(Str(G3_START_LINE))).Select
Selection.ClearContents
NO_DATA = 0
Else
Sheets("Special View").Select
Sheets("Special View").Cells(G3_START_LINE - 1, 13).Formula = "=RIGHT('D3'!D22,2) &""/""&MID('D3'!D22,3,2)"
Sheets("Special View").Cells(G3_START_LINE - 1, 14).Formula = "=RIGHT('D3'!E22,2)&""/""&MID('D3'!E22,3,2)"
Range("N" + Trim(Str(G3_START_LINE - 1))).Select
Selection.AutoFill Destination:=Range("N" + Trim(Str(G3_START_LINE - 1)) + ":AB" + Trim(Str(G3_START_LINE - 1))), Type:=xlFillDefault
Sheets("Special View").Cells(G3_START_LINE, 2).Formula = "='G3'!B" + Trim(Str(G3_START_LINE)) + " & "" / "" & 'G3'!C" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 3).Formula = "='G3'!E" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 4).Formula = "='G3'!F" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 5).Formula = "='G3'!I" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 6).Formula = "='G3'!BB" + Trim(Str(G3_START_LINE)) + "/1000"
Sheets("Special View").Cells(G3_START_LINE, 7).Formula = "='G3'!J" + Trim(Str(G3_START_LINE))
'modif mail tony kamitsy 19/01/2009 Sheets("Special View").Cells(G3_START_LINE, 8).Formula = "=IF('G3'!BC" + Trim(Str(G3_START_LINE)) + "=""null"",'G3'!AW" + Trim(Str(G3_START_LINE)) + ",'G3'!BC" + Trim(Str(G3_START_LINE)) + ")"
Sheets("Special View").Cells(G3_START_LINE, 8).Formula = "='G3'!AW" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 9).Formula = "=IF('G3'!BD" + Trim(Str(G3_START_LINE)) + "=""null"","""",'G3'!BD" + Trim(Str(G3_START_LINE)) + ")"
Sheets("Special View").Cells(G3_START_LINE, 10).Formula = "=IF('G3'!L" + Trim(Str(G3_START_LINE)) + "="""","""",'G3'!L" + Trim(Str(G3_START_LINE)) + ")"
Sheets("Special View").Cells(G3_START_LINE, 11).Formula = "='G3'!M" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 12).Value = ""
strformula = "=IF(TYPE(GETPIVOTDATA(""Sum of CALENDERIZED_SAVING"",PIVOT_TABLE1!$A$3,""VEVA_NUMBER"",'G3'!$B" + Trim(Str(G3_START_LINE)) + ",""PLANT"",'G3'!$C" + Trim(Str(G3_START_LINE)) + " "
strformula = strformula + ",""DESCRIPTION"",'G3'!$F" + Trim(Str(G3_START_LINE)) + ",""PRIORITY"",'G3'!$E" + Trim(Str(G3_START_LINE)) + ",""VEVA_STATUS"",'G3'!$J" + Trim(Str(G3_START_LINE)) + ",""RATING"",'G3'!$G" + Trim(Str(G3_START_LINE)) + ",""MONTHTG3"",'D3'!B$30,""FULL_ANNUALIZED_SAVINGS"",'G3'!$BA"
strformula = strformula + Trim(Str(G3_START_LINE)) + ",""DIVISION"",'G3'!$D" + Trim(Str(G3_START_LINE)) + ",""TYPE_OF_SAVING"",'G3'!$M" + Trim(Str(G3_START_LINE)) + ","
strformula = strformula + " ""ACTION_COLUMN"",'G3'!$AY" + Trim(Str(G3_START_LINE)) + ")) =16,"""",IF(GETPIVOTDATA(""Sum of CALENDERIZED_SAVING"",PIVOT_TABLE1!$A$3,""VEVA_NUMBER"",'G3'!$B" + Trim(Str(G3_START_LINE)) + ",""PLANT"",'G3'!$C" + Trim(Str(G3_START_LINE)) + ",""DESCRIPTION"",'G3'!$F" + Trim(Str(G3_START_LINE)) + ",""PRIORITY"",'G3'!$E" + Trim(Str(G3_START_LINE)) + ",""VEVA_STATUS"",'G3'!$J" + Trim(Str(G3_START_LINE)) + ",""RATING"",'G3'!$G" + Trim(Str(G3_START_LINE)) + ",""MONTHTG3"",'D3'!B$30,""FULL_ANNUALIZED_SAVINGS"",'G3'!$BA"
strformula = strformula + Trim(Str(G3_START_LINE)) + ",""DIVISION"",'G3'!$D" + Trim(Str(G3_START_LINE)) + ",""TYPE_OF_SAVING"",'G3'!$M" + Trim(Str(G3_START_LINE)) + ","
strformula = strformula + " ""ACTION_COLUMN"",'G3'!$AY" + Trim(Str(G3_START_LINE)) + ")=0,"""",GETPIVOTDATA(""Sum of CALENDERIZED_SAVING"",PIVOT_TABLE1!$A$3,""VEVA_NUMBER"",'G3'!$B" + Trim(Str(G3_START_LINE)) + ",""PLANT"",'G3'!$C" + Trim(Str(G3_START_LINE)) + ",""DESCRIPTION"",'G3'!$F" + Trim(Str(G3_START_LINE)) + ",""PRIORITY"",'G3'!$E" + Trim(Str(G3_START_LINE)) + ",""VEVA_STATUS"",'G3'!$J" + Trim(Str(G3_START_LINE)) + ",""RATING"",'G3'!$G" + Trim(Str(G3_START_LINE)) + ",""MONTHTG3"",'D3'!B$30,""FULL_ANNUALIZED_SAVINGS"",'G3'!$BA" + Trim(Str(G3_START_LINE)) + ",""DIVISION"",'G3'!$D" + Trim(Str(G3_START_LINE)) + ",""TYPE_OF_SAVING"",'G3'!$M" + Trim(Str(G3_START_LINE))
strformula = strformula + ", ""ACTION_COLUMN"",'G3'!$AY" + Trim(Str(G3_START_LINE)) + ")/1000))"
Sheets("Special View").Cells(G3_START_LINE, 13).Formula = strformula
Sheets("Special View").Cells(G3_START_LINE, 14).Formula = "='G3'!O" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 15).Formula = "='G3'!P" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 16).Formula = "='G3'!Q" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 17).Formula = "='G3'!R" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 18).Formula = "='G3'!S" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 19).Formula = "='G3'!T" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 20).Formula = "='G3'!U" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 21).Formula = "='G3'!V" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 22).Formula = "='G3'!W" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 23).Formula = "='G3'!X" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 24).Formula = "='G3'!Y" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 25).Formula = "='G3'!Z" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 26).Formula = "='G3'!AA" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 27).Formula = "='G3'!AB" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 28).Formula = "='G3'!AC" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 33).Value = "=RIGHT(""0000000000"" &'G3'!B" + Trim(Str(G3_START_LINE)) + ",10) & "" / "" & 'G3'!C" + Trim(Str(G3_START_LINE))
'Sheets("Special View").Cells(G3_START_LINE, 34).Value = "=IF(AK" + Trim(Str(G3_START_LINE)) + "=""0"",C" + Trim(Str(G3_START_LINE)) + ",AG" + Trim(Str(G3_START_LINE)) + ")"
Sheets("Special View").Cells(G3_START_LINE, 34).Value = "=IF(AK" + Trim(Str(G3_START_LINE)) + "=""0"",C" + Trim(Str(G3_START_LINE)) + ",'G3'!H" + Trim(Str(G3_START_LINE)) + ")"
Sheets("Special View").Cells(G3_START_LINE, 35).Value = "=H" + Trim(Str(G3_START_LINE))
Sheets("Special View").Cells(G3_START_LINE, 37).Value = "=IF(G" + Trim(Str(G3_START_LINE)) + "=""TG3"",IF(H" + Trim(Str(G3_START_LINE)) + "<DATE(LEFT(Properties!$C$10,4),RIGHT(Properties!$C$10,2)-1,1),""2"",""1""),IF(G" + Trim(Str(G3_START_LINE)) + "=""TG4"",IF(H" + Trim(Str(G3_START_LINE)) + "<DATE(LEFT(Properties!$C$10,4),RIGHT(Properties!$C$10,2)-1,1),""2"",""1""),""0""))"
'Sheets("Special View").Cells(G3_START_LINE, 41).Value = "=IF(M" + Trim(Str(G3_START_LINE)) + "="""","""",IF($L" + Trim(Str(G3_START_LINE)) + "=""R"",""R"",IF($L" + Trim(Str(G3_START_LINE)) + "=""Y"",""Y"",if($L" + Trim(Str(G3_START_LINE)) + "=""G"",""G"",""""))))"
Sheets("Special View").Cells(G3_START_LINE, 41).Value = "=IF($L" + Trim(Str(G3_START_LINE)) + "=""R"",""R"",IF($L" + Trim(Str(G3_START_LINE)) + "=""Y"",""Y"",if($L" + Trim(Str(G3_START_LINE)) + "=""G"",""G"","""")))"
Range("AO" + Trim(Str(G3_START_LINE))).Select
Selection.AutoFill Destination:=Range("AO" + Trim(Str(G3_START_LINE)) + ":BB" + Trim(Str(G3_START_LINE))), Type:=xlFillDefault
Sheets("Special View").Cells(G3_START_LINE, 55).Formula = "=IF(14 * MAX(LEN(D11)/40,LEN(G11)/6,LEN(I11)/30,LEN(J11)/29)> 409,409,IF(INT(MAX(LEN(D11)/40,LEN(G11)/6,LEN(I11)/30,LEN(J11)/29)) - MAX(LEN(D11)/40,LEN(G11)/6,LEN(I11)/30,LEN(J11)/29) < 0,14 * (MAX(LEN(D11)/40,LEN(G11)/6,LEN(I11)/30,LEN(J11)/29)+1),14 * MAX(LEN(D11)/40,LEN(G11)/6,LEN(I11)/30,LEN(J11)/29)))"
Call logsproc("ECRIT FORMULES")
If nblinestableview > 1 Then
Rows(Trim(Str(G3_START_LINE + 1)) + ":" + Trim(Str(nblinestable + G3_START_LINE - 1))).Select
Selection.Insert Shift:=xlDown
Range("A" + Trim(Str(G3_START_LINE)) + ":BC" + Trim(Str(G3_START_LINE))).Select
Selection.AutoFill Destination:=Range("A" + Trim(Str(G3_START_LINE)) + ":BC" + Trim(Str(nblinestableview + G3_START_LINE - 1))), Type:=xlFillDefault
'Rows(Trim(Str(G3_START_LINE)) + ":" + Trim(Str(nblinestable + G3_START_LINE - 1))).Select
Range("B" + Trim(Str(G3_START_LINE)) + ":AK" + Trim(Str(nblinestable + G3_START_LINE - 1))).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False 'Selection.Copy
Range("B" + Trim(Str(G3_START_LINE)) + ":AK" + Trim(Str(nblinestable + G3_START_LINE - 1))).Select
Selection.Sort Key1:=Range("AK" + Trim(Str(G3_START_LINE))), Order1:=xlAscending, Key2:=Range( _
"AH" + Trim(Str(G3_START_LINE))), Order2:=xlDescending, Key3:=Range("AI" + Trim(Str(G3_START_LINE))), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal _
, DataOption3:=xlSortNormal
End If
Call logsproc("AJOUT LIGNES ET TRIE")
'For nline2 = G3_START_LINE To G3_START_LINE + nblinestableview - 1
Rows(Trim(Str(G3_START_LINE)) + ":" + Trim(Str(G3_START_LINE + nblinestableview - 1))).Select
Rows("11:16").EntireRow.AutoFit
Range("A1").Select
Call logsproc("MAJ COULEUR ET HAUTEUR")
Range("A1").Select
Range("M" + Trim(Str(G3_START_LINE - 1)) + ":AB" + Trim(Str(G3_START_LINE - 1))).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False 'Selection.Copy
Call logsproc("SUPPRIME FORMULES")
'recherche ligne à cacher
nb = G3_START_LINE
While Sheets("Special View").Cells(nb, 37).Value <> "" And Sheets("Special View").Cells(nb, 37).Value <> 2
nb = nb + 1
Wend
nblinestableviewhide = nb
Sheets("Properties").Cells(1, 202).Value = nblinestableviewhide
If nblinestableviewhide < nblinestableview + G3_START_LINE - 1 Then
Rows(Trim(Str(nblinestableviewhide)) + ":" + Trim(Str(nblinestableview + G3_START_LINE - 1))).Select
Selection.EntireRow.Hidden = True
End If
Call logsproc("CACHE LIGNES")
End If
If ANA_PERIODE = "CURRENT YEAR" Then
Range("A1").Select
Range("M8:N65526").Select
Range("M8").Activate
Selection.EntireColumn.Hidden = True
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='D3'!R25C8:R25C49"
ActiveChart.SeriesCollection(1).Values = "='D3'!R26C8:R26C49"
ActiveChart.SeriesCollection(2).XValues = "='D3'!R25C8:R25C49"
ActiveChart.SeriesCollection(2).Values = "='D3'!R28C8:R28C49"
ActiveChart.SeriesCollection(3).XValues = "='D3'!R25C8:R25C49"
ActiveChart.SeriesCollection(3).Values = "='D3'!R27C8:R27C49"
ActiveChart.SeriesCollection(4).XValues = "='D3'!R25C8:R25C49"
ActiveChart.SeriesCollection(4).Values = "='D3'!R29C8:R29C49"
ActiveChart.SeriesCollection(5).Values = "='D3'!R30C8:R30C49"
ActiveChart.SeriesCollection(6).Values = "='D3'!R31C8:R31C49"
Else
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=('D3'!R25C2:R25C49)"
ActiveChart.SeriesCollection(1).Values = "=('D3'!R26C2:R26C49)"
ActiveChart.SeriesCollection(2).XValues = "=('D3'!R25C2:R25C49)"
ActiveChart.SeriesCollection(2).Values = "=('D3'!R28C2:R28C49)"
ActiveChart.SeriesCollection(3).XValues = "=('D3'!R25C2:R25C49)"
ActiveChart.SeriesCollection(3).Values = "=('D3'!R27C2:R27C49)"
ActiveChart.SeriesCollection(4).XValues = "=('D3'!R25C2:R25C49)"
ActiveChart.SeriesCollection(4).Values = "=('D3'!R29C2:R29C49)"
ActiveChart.SeriesCollection(5).Values = "='D3'!R30C2:R30C49"
ActiveChart.SeriesCollection(6).Values = "='D3'!R31C2:R31C49"
End If
Call logsproc("FIN GENERATE VIEW")
Range("A1").Select
FLAG_MAJ_SPECIAL_VIEW = 1
End Sub-- 07 Juin 2011, 16:24 --
j'ai d'autres modules que je n'ai pas posté encore. je ne sais pas si c'est necessaire de les poster