Hi keepITcool
I thank you for your quick responce. Your suggestion seams to be right
on target, but I'm still going back on my userform module after I call
it. I am not a VB programmer, I am still working in the VBA from
Excel.
Do you think it would be possible for me to send you my file so you
could take a look at it. (and dont laught at my beginners code in the
process !
) The adress (e-mail address removed) is good if you
want to contact me.
Mean while here is i hope will be anought to give you and idea.
Class module: VBECmdHandler
Public WithEvents EvtHandler As VBIDE.CommandBarEvents
Private Sub EvtHandler_Click(ByVal CommandBarControl As Object,
Handled As Boolean, CancelDefault As Boolean)
Set VBInstance = Application.VBE
With VBInstance.MainWindow
strNomWorkbook = Trim(Mid(.Caption, InStr(.Caption, "- ") + 2,
InStr(.Caption, "[") - InStr(.Caption, "- ") - 2)) '.Filename
.BuildFileName .Name
End With
Set VBInstance = Nothing
Application.Run CommandBarControl.OnAction
'
' Indicate to the Events object that we've successfully handled the
event.
'
Handled = True
CancelDefault = True
End Sub
Regular Module: Choix
Public Sub Code_Presentation()
'Application.ScreenUpdating = False
réglages.Show
End Sub
Userform Module: réglages
Public Declare Function FindWindow Lib "user32.dll" Alias
"FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As
String) As Long Public Declare Function SetParent Lib "user32.dll"
(ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long
Property Get Hwnd() As Long
Hwnd = FindWindow(vbNullString, Caption)
End Property
Private Sub CommandButton1_Click()
With Me
Open "C:\XLD\Temp_Files\FileCodeFormat.txt" For Output As #1
Print #1, "" & .ComboBox_couleur_code.Value & Chr(32) &
Abs(.CheckBox_c_g.Value) & Chr(32) & Abs(.CheckBox_c_i) & Chr(32) &
Abs(.CheckBox_c_u)
Print #1, "" & .ComboBox_couleur_mots_clés & Chr(32) &
Abs(.CheckBox_m_g) & Chr(32) & Abs(.CheckBox_m_i) & Chr(32) &
Abs(.CheckBox_m_u)
Print #1, "" & .ComboBox_couleur_commentaires & Chr(32) &
Abs(.CheckBox_com_g) & Chr(32) & Abs(.CheckBox_com_i) & Chr(32) &
Abs(.CheckBox_com_u)
Print #1, .taille
Print #1, "" & Abs(.case_quote)
Print #1, "EOF"
Close #1
.Hide
End With
Unload Me
End Sub
Private Sub Spin_taille_Change()
Me.taille = Me.Spin_taille.Value
End Sub
Private Sub UserForm_Initialize()
Dim arrCouleur() As Variant, Item As Variant, strCritères() As String
Dim bytLine As Byte
Dim strLine As String
With Application.VBE.MainWindow
If .Visible And Not .WindowState = 1 Then 'minimized
.SetFocus
Call SetParent(Me.Hwnd, .Hwnd)
End If
End With
Repaint
arrCouleur = Array("white", "black", "Gray", "silver", "blue", "navy",
"cyan", "teal", "green", "olive", "lime", "fuschia", "purple", "red",
"maroon", "yellow", "aliceblue", "antiquewhite", "aqua", "aquamarine",
"azure", "beige", "bisque", "black", "blanchedalmond", "blue",
"blueviolet" _
, "brown", "burlywood", "cadetblue", "chartreuse", "chocolate",
"coral", "cornflowerblue", "cornsilk", "crimson", "cyan", "darkblue",
"darkcyan", "darkgoldenrod", "darkgray", "darkgreen", "darkkhaki",
"darkmagenta", "darkolivegreen", "darkorange", "darkorchid",
"darkred", "darksalmon" _ , "darkseagreen", "darkslateblue",
"darkslategray", "darkturquoise", "darkviolet", "deeppink",
"deepskyblue", "dimgray", "dodgerblue", "firebrick", "floralwhite",
"forestgreen", "gainsboro", "ghostwhite", "gold", "goldenrod",
"gray", "green", "greenyellow", "honeydew", "hotpink" _
, "indianred", "indigo", "ivory", "khaki", "lavender",
"lavenderblush", "lawngreen", "lemonchiffon", "lightblue",
"lightcoral", "lightcyan", "lightgoldenrodyellow", "lightgray",
"lightgreen", "lightpink", "lightsalmon", "lightseagreen",
"lightskyblue", "lightslateblue" _ , "lightslategray",
"lightsteelblue", "lightyellow", "lime", "limegreen", "linen",
"magenta", "maroon", "mediumaquamarine", "mediumblue",
"mediumorchid", "mediumpurple", "mediumseagreen", "mediumslateblue",
"mediumspringgreen", "mediumturquoise", "mediumvioletred",
"midnightblue" _ , "mintcream", "mistyrose", "moccasin",
"navajowhite", "navy", "oldlace", "olive", "olivedrab", "orange",
"orangered", "orchid", "palegoldenrod", "palegreen", "paleturquoise",
"palevioletred", "papayawhip", "peachpuff", "peru", "pink", "plum",
"powderblue", "purple", "red" _
, "rosybrown", "royalblue", "saddlebrown", "salmon", "sandybrown",
"seagreen", "seashell", "sienna", "silver", "skyblue", "slateblue",
"slategray", "snow", "springgreen", "steelblue", "tan", "teal",
"thistle", "tomato", "turquoise", "violet", "violetred", "wheat",
"white" _
, "whitesmoke", "yellow", "yellowgreen")
With Me
For Each Item In arrCouleur
.ComboBox_couleur_mots_clés.AddItem Item
.ComboBox_couleur_code.AddItem Item
.ComboBox_couleur_commentaires.AddItem Item
Next Item
On Error GoTo CloseFile
Open "C:\XLD\Temp_Files\FileCodeFormat.txt" For Input As #1
On Error GoTo 0
Do
Line Input #1, strLine
bytLine = bytLine + 1
On Error GoTo Excel97Split
strCritères() = Split(strLine, Chr(32))
On Error GoTo 0
Select Case bytLine
Case 1
.ComboBox_couleur_code = strCritères()(0)
.CheckBox_c_g = strCritères()(1)
.CheckBox_c_i = strCritères()(2)
.CheckBox_c_u = strCritères()(3)
Case 2
.ComboBox_couleur_mots_clés =
strCritères()(0)
.CheckBox_m_g = strCritères()(1)
.CheckBox_m_i = strCritères()(2)
.CheckBox_m_u = strCritères()(3)
Case 3
.ComboBox_couleur_commentaires =
strCritères()(0)
.CheckBox_com_g = strCritères()(1)
.CheckBox_com_i = strCritères()(2)
.CheckBox_com_u = strCritères()(3)
Case 4
.taille = strCritères()(0)
Case 5
.case_quote = strCritères()(0)
Case 6
Exit Do
End Select
Loop
Close #1
End With
Exit Sub
Excel97Split:
strCritères() = SplitXl97(strLine)
Resume Next
CloseFile:
Close #1
On Error GoTo 0
Exit Sub
End Sub
Thanks for your time
Philippe
