VBA - Userform doesn"t work anymore once unloaded (runtime error 9)

M

maxime_jajko

New Member
Joined
Jan 13, 2018
Messages
2
Hello everybody,

Here's my problem : (a bit strange by the way)

1) I have a UserForm1 (my main interface) on which I click on a button to call Userform2 => it works normally at this point
2) Then, I have a button on the userform2 to unload it and go back to the userform1 => it works well too
3) But then, when i want to load the userform2 again, it doen't work and i have the usual runtime error 9.

Why does it work perfectly the first time but crashes afterwards ? Why can't I load userform 2 for a second time ?

Here's my code :

UserForm1 code

Code: 
[COLOR=#0000FF]Private[/COLOR][COLOR=#0000FF]Declare[/COLOR][COLOR=#0000FF]Function[/COLOR][COLOR=#333333] GetSystemMenu _[/COLOR]    [COLOR=#0000FF]Lib[/COLOR] [COLOR=#FF0000]"user32"[/COLOR] ( _
    [COLOR=#0000FF]ByVal[/COLOR] hWnd [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR], _
    [COLOR=#0000FF]ByVal[/COLOR] bRevert [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR]) _
[COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Declare[/COLOR] [COLOR=#0000FF]Function[/COLOR] RemoveMenu _
    [COLOR=#0000FF]Lib[/COLOR] [COLOR=#FF0000]"user32"[/COLOR] ( _
    [COLOR=#0000FF]ByVal[/COLOR] hMenu [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR], _
    [COLOR=#0000FF]ByVal[/COLOR] nPosition [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR], _
    [COLOR=#0000FF]ByVal[/COLOR] wFlags [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR]) _
[COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Declare[/COLOR] [COLOR=#0000FF]Function[/COLOR] FindWindowA _
    [COLOR=#0000FF]Lib[/COLOR] [COLOR=#FF0000]"user32"[/COLOR] ( _
    [COLOR=#0000FF]ByVal[/COLOR] lpClassName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR], _
    [COLOR=#0000FF]ByVal[/COLOR] lpWindowName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR]) _
[COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Const[/COLOR] MF_BYPOSITION [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR] = [COLOR=#CC66CC]&H400[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_Initialize()
[COLOR=#0000FF]Dim[/COLOR] lFrmHdl [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR], iCount [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR]
[COLOR=#808080]'// Ivan F Moala[/COLOR]
lFrmHdl = FindWindowA(vbNullString, Me.Caption)
 
[COLOR=#0000FF]If[/COLOR] lFrmHdl <> [COLOR=#CC66CC]0[/COLOR] [COLOR=#0000FF]Then[/COLOR]
    [COLOR=#808080]'// MF_BYCOMMAND[/COLOR]
    [COLOR=#808080]'//Indicates that uPosition gives the identifier of the menu item.[/COLOR]
 
    [COLOR=#808080]'//If neither the MF_BYCOMMAND nor MF_BYPOSITION flag is specified,[/COLOR]
    [COLOR=#808080]'//the MF_BYCOMMAND flag is the default flag.[/COLOR]
 
    [COLOR=#808080]'// MF_BYPOSITION[/COLOR]
    [COLOR=#808080]'//Indicates that uPosition gives the zero-based relative position of the menu item.[/COLOR]
    [COLOR=#808080]'// ie 0,1,2,3 etc[/COLOR]
    [COLOR=#808080]'Exit Sub[/COLOR]
    [COLOR=#808080]'//Typical Windows has 6 menus[/COLOR]
    [COLOR=#808080]'//Restore, Move, Size, Minimise, Maximize, Close[/COLOR]
    [COLOR=#808080]'//Even though a Userform displays 2 = Move & Close[/COLOR]
    [COLOR=#808080]'//By default Move is the Next item[/COLOR]
    [COLOR=#808080]'//so just loop twice[/COLOR]
    [COLOR=#0000FF]For[/COLOR] iCount = [COLOR=#CC66CC]0[/COLOR] [COLOR=#0000FF]To[/COLOR] [COLOR=#CC66CC]1[/COLOR]
        RemoveMenu GetSystemMenu(lFrmHdl, [COLOR=#0000FF]False[/COLOR]), [COLOR=#CC66CC]0[/COLOR], MF_BYPOSITION
    [COLOR=#0000FF]Next[/COLOR] iCount
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
 
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_QueryClose(Cancel [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR], CloseMode [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR])
[COLOR=#0000FF]If[/COLOR] CloseMode = vbFormControlMenu [COLOR=#0000FF]Then[/COLOR]
MsgBox [COLOR=#FF0000]"Vous ne pouvez pas utiliser ce bouton de fermeture."[/COLOR] & Chr([COLOR=#CC66CC]10[/COLOR]) _
& [COLOR=#FF0000]"Pour quitter le logiciel, veuillez utiliser le bouton prévu à cet effet"[/COLOR]
Cancel = [COLOR=#0000FF]True[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] ComboBox1_Change()
[COLOR=#0000FF]Dim[/COLOR] WB [COLOR=#0000FF]As[/COLOR] Workbook
 
[COLOR=#0000FF]For[/COLOR] [COLOR=#0000FF]Each[/COLOR] WB [COLOR=#0000FF]In[/COLOR] Workbooks
[COLOR=#0000FF]If[/COLOR] (WB.Name <> [COLOR=#FF0000]"LAST_SOFT.xls"[/COLOR]) = [COLOR=#0000FF]True[/COLOR] [COLOR=#0000FF]Then[/COLOR]
WB.Close SaveChanges:=[COLOR=#0000FF]False[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR]
 
 
    [COLOR=#0000FF]If[/COLOR] ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 4"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR4.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 2"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR2.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR1.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 3"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR3.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
 
 
    UserForm6.Show
 
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton1_Click()
Me.Hide
UserForm2.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton2_Click()
Me.Hide
UserForm4.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton3_Click()
Me.Hide
UserForm5.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton4_Click()
ActiveWorkbook.Close SaveChanges:=[COLOR=#0000FF]False[/COLOR]
ThisWorkbook.Close SaveChanges:=[COLOR=#0000FF]False[/COLOR]
Application.Quit
Application.DisplayAlerts = [COLOR=#0000FF]True[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton5_Click()
UserForm3.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton6_Click()
UserForm7.Show [COLOR=#0000FF]End[/COLOR][COLOR=#0000FF]Sub[/COLOR]

UserForm2 Code :

Code: 
[COLOR=#0000FF]Private[/COLOR][COLOR=#0000FF]Sub[/COLOR][COLOR=#333333] CommandButton1_Click[/COLOR][COLOR=#333333]([/COLOR][COLOR=#333333])[/COLOR]Unload UserForm2
UserForm1.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton172_Click()
Unload UserForm2
 
[COLOR=#0000FF]Dim[/COLOR] WB [COLOR=#0000FF]As[/COLOR] Workbook
 
[COLOR=#0000FF]For[/COLOR] [COLOR=#0000FF]Each[/COLOR] WB [COLOR=#0000FF]In[/COLOR] Workbooks
[COLOR=#0000FF]If[/COLOR] (WB.Name <> [COLOR=#FF0000]"LAST_SOFT.xls"[/COLOR]) = [COLOR=#0000FF]True[/COLOR] [COLOR=#0000FF]Then[/COLOR]
WB.Close SaveChanges:=[COLOR=#0000FF]False[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR]
 
    [COLOR=#0000FF]If[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 4"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR4.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 2"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR2.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR1.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 3"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR3.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
 
 
    UserForm6.Show
    UserForm2.Show
 
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton2_Click()
Unload UserForm2
UserForm3.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub
[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_QueryClose(Cancel [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR], CloseMode [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR])
[COLOR=#0000FF]If[/COLOR] CloseMode = vbFormControlMenu [COLOR=#0000FF]Then[/COLOR]
MsgBox [COLOR=#FF0000]"Vous ne pouvez pas utiliser ce bouton de fermeture."[/COLOR] & Chr([COLOR=#CC66CC]10[/COLOR]) _
& [COLOR=#FF0000]"Pour revenir au menu principal, veuillez utiliser le bouton prévu à cet effet"[/COLOR]
Cancel = [COLOR=#0000FF]True[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_Initialize()
[COLOR=#0000FF]Dim[/COLOR] R [COLOR=#0000FF]As[/COLOR] Range
[COLOR=#0000FF]Dim[/COLOR] SP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] SHSP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] RSP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] Ctrl [COLOR=#0000FF]As[/COLOR] Control
[COLOR=#0000FF]Dim[/COLOR] i [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR]
 
i = [COLOR=#CC66CC]1[/COLOR]
[COLOR=#0000FF]For[/COLOR] [COLOR=#0000FF]Each[/COLOR] Ctrl [COLOR=#0000FF]In[/COLOR] Controls
[COLOR=#0000FF]If[/COLOR] i <= [COLOR=#CC66CC]34[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Me.MultiPage1.Pages(i - [COLOR=#CC66CC]1[/COLOR]).Caption = ActiveWorkbook.Sheets(i + [COLOR=#CC66CC]1[/COLOR]).Cells([COLOR=#CC66CC]2[/COLOR], [COLOR=#CC66CC]2[/COLOR]).Value
[COLOR=#0000FF]If[/COLOR] TypeName(Ctrl) = [COLOR=#FF0000]"Spreadsheet"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
[COLOR=#0000FF]Set[/COLOR] R = ActiveWorkbook.Sheets(i + [COLOR=#CC66CC]1[/COLOR]).Range([COLOR=#FF0000]"a1:z100"[/COLOR])
R.Copy
[COLOR=#0000FF]Set[/COLOR] SP = Ctrl.[COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Set[/COLOR] SHSP = SP.Worksheets([COLOR=#CC66CC]1[/COLOR])
[COLOR=#0000FF]Set[/COLOR] RSP = SHSP.[a1]
RSP.[COLOR=#0000FF]Select[/COLOR]
SHSP.Paste
RSP.[COLOR=#0000FF]Select[/COLOR]
Application.CutCopyMode = [COLOR=#0000FF]False[/COLOR]
SP.DisplayTitleBar = [COLOR=#0000FF]False[/COLOR]
SP.DisplayToolbar = [COLOR=#0000FF]False[/COLOR]
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]3[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]8[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]13[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]18[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]23[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]2[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]7[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]12[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]17[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]22[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]4[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]9[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]14[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]19[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]24[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]1[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]6[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]11[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]16[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]21[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]26[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]5[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]10[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]15[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]20[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]25[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
 
 
 
[COLOR=#0000FF]Dim[/COLOR] j [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR]
[COLOR=#0000FF]For[/COLOR] j = [COLOR=#CC66CC]1[/COLOR] [COLOR=#0000FF]To[/COLOR] [COLOR=#CC66CC]110[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]6[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]4[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]6[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]4[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]11[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]9[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]11[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]9[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]16[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]14[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]16[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]14[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]21[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]19[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]21[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1,00"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]19[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]26[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]24[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]26[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]24[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR]
i = i + [COLOR=#CC66CC]1[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR] [COLOR=#0000FF]End[/COLOR][COLOR=#0000FF]Sub[/COLOR]

Thanks in advance for your ideas and your help.
Also I would like to precise that ShowModal properties is False for both userforms.

PS : Sorry for my english, I am from France
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Userform2 code command1 doesn't unload or hide userform2 before showing userform1. Command72 unloads userform2 then does a bunch of stuff then shows userfom6 and userform2? U should do stuff then unload/hide a userform then show/load a different userform. HTH. Dave
 
Upvote 0
NdNoviceHlp said:
Userform2 code command1 doesn't unload or hide userform2 before showing userform1. Command72 unloads userform2 then does a bunch of stuff then shows userfom6 and userform2? U should do stuff then unload/hide a userform then show/load a different userform. HTH. Dave
Click to expand...

Thanks for your answer.

It's just a mistake in the Command1 (not in my real code)

Code: 
[COLOR=#0000FF]Private[/COLOR][COLOR=#0000FF]Sub[/COLOR][COLOR=#333333] CommandButton1_Click[/COLOR][COLOR=#333333]([/COLOR][COLOR=#333333])[/COLOR]Unload UserForm2
UserForm1.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton172_Click()
Unload UserForm2
 
[COLOR=#0000FF]Dim[/COLOR] WB [COLOR=#0000FF]As[/COLOR] Workbook
 
[COLOR=#0000FF]For[/COLOR] [COLOR=#0000FF]Each[/COLOR] WB [COLOR=#0000FF]In[/COLOR] Workbooks
[COLOR=#0000FF]If[/COLOR] (WB.Name <> [COLOR=#FF0000]"LAST_SOFT.xls"[/COLOR]) = [COLOR=#0000FF]True[/COLOR] [COLOR=#0000FF]Then[/COLOR]
WB.Close SaveChanges:=[COLOR=#0000FF]False[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR]
 
    [COLOR=#0000FF]If[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 4"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR4.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 2"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR2.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR1.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]ElseIf[/COLOR] UserForm1.ComboBox1.Value = [COLOR=#FF0000]"TRANCHE 3"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]Set[/COLOR] WB = Workbooks.Open([COLOR=#FF0000]"\\Atlas.edf.fr\co\45dam-dpn\services.006\sco.001\Exp34\0 Equipe F\Maxime\LAST\LAST_TR3.xls"[/COLOR])
        WB.Activate
 
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
 
 
    UserForm6.Show
    UserForm2.Repaint
 
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] CommandButton2_Click()
Unload UserForm2
UserForm3.Show
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub
[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_QueryClose(Cancel [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR], CloseMode [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR])
[COLOR=#0000FF]If[/COLOR] CloseMode = vbFormControlMenu [COLOR=#0000FF]Then[/COLOR]
MsgBox [COLOR=#FF0000]"Vous ne pouvez pas utiliser ce bouton de fermeture."[/COLOR] & Chr([COLOR=#CC66CC]10[/COLOR]) _
& [COLOR=#FF0000]"Pour revenir au menu principal, veuillez utiliser le bouton prévu à cet effet"[/COLOR]
Cancel = [COLOR=#0000FF]True[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub

[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] UserForm_Initialize()
[COLOR=#0000FF]Dim[/COLOR] R [COLOR=#0000FF]As[/COLOR] Range
[COLOR=#0000FF]Dim[/COLOR] SP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] SHSP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] RSP [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Dim[/COLOR] Ctrl [COLOR=#0000FF]As[/COLOR] Control
[COLOR=#0000FF]Dim[/COLOR] i [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR]
 
i = [COLOR=#CC66CC]1[/COLOR]
[COLOR=#0000FF]For[/COLOR] [COLOR=#0000FF]Each[/COLOR] Ctrl [COLOR=#0000FF]In[/COLOR] Controls
[COLOR=#0000FF]If[/COLOR] i <= [COLOR=#CC66CC]34[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Me.MultiPage1.Pages(i - [COLOR=#CC66CC]1[/COLOR]).Caption = ActiveWorkbook.Sheets(i + [COLOR=#CC66CC]1[/COLOR]).Cells([COLOR=#CC66CC]2[/COLOR], [COLOR=#CC66CC]2[/COLOR]).Value
[COLOR=#0000FF]If[/COLOR] TypeName(Ctrl) = [COLOR=#FF0000]"Spreadsheet"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
[COLOR=#0000FF]Set[/COLOR] R = ActiveWorkbook.Sheets(i + [COLOR=#CC66CC]1[/COLOR]).Range([COLOR=#FF0000]"a1:z100"[/COLOR])
R.Copy
[COLOR=#0000FF]Set[/COLOR] SP = Ctrl.[COLOR=#0000FF]Object[/COLOR]
[COLOR=#0000FF]Set[/COLOR] SHSP = SP.Worksheets([COLOR=#CC66CC]1[/COLOR])
[COLOR=#0000FF]Set[/COLOR] RSP = SHSP.[a1]
RSP.[COLOR=#0000FF]Select[/COLOR]
SHSP.Paste
RSP.[COLOR=#0000FF]Select[/COLOR]
Application.CutCopyMode = [COLOR=#0000FF]False[/COLOR]
SP.DisplayTitleBar = [COLOR=#0000FF]False[/COLOR]
SP.DisplayToolbar = [COLOR=#0000FF]False[/COLOR]
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]3[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]8[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]13[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]18[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]23[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]2[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]7[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]12[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]17[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]22[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]4[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]9[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]14[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]19[/COLOR]).AutoFit
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]24[/COLOR]).AutoFit
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]1[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]6[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]11[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]16[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]21[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]26[/COLOR]).ColumnWidth = [COLOR=#CC66CC]4[/COLOR]
 
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]5[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]10[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]15[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]20[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Columns([COLOR=#CC66CC]25[/COLOR]).ColumnWidth = [COLOR=#CC66CC]5[/COLOR]
 
 
 
[COLOR=#0000FF]Dim[/COLOR] j [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR]
[COLOR=#0000FF]For[/COLOR] j = [COLOR=#CC66CC]1[/COLOR] [COLOR=#0000FF]To[/COLOR] [COLOR=#CC66CC]110[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]6[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]4[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]6[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]4[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]11[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]9[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]11[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]9[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]16[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]14[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]16[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]14[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]21[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]19[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]21[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1,00"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]19[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]If[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]26[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"0"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]24[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]10[/COLOR]
[COLOR=#0000FF]ElseIf[/COLOR] Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]26[/COLOR]).[COLOR=#0000FF]Text[/COLOR] = [COLOR=#FF0000]"1"[/COLOR] [COLOR=#0000FF]Then[/COLOR]
Ctrl.Object.Worksheets([COLOR=#FF0000]"Feuil1"[/COLOR]).Cells(j, [COLOR=#CC66CC]24[/COLOR]).Font.ColorIndex = [COLOR=#CC66CC]3[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR]
i = i + [COLOR=#CC66CC]1[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]Next[/COLOR] [COLOR=#0000FF]End[/COLOR][COLOR=#0000FF]Sub

[/COLOR]



Another idea ?
 
Upvote 0
Oh I see command1 just didn't post right. U could trial userform1.hide instead of me,hide for the userform1 code. I still don't get the flow of command 172? "Me.MultiPage1" could be userform2.multipage1. It seems like something in userform2 initialize code isn't right. Trial option explicit at the top of your code sheets. Dave
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top