Hi

I have created a xla file. The main purpuse of that file, is to be able to
copy the code lines of the selected VBProject on to the clipboard. In that
file, i have created 2 userforms. The goal of the first one when called
upon, is to present in a TreeView control, all the Procedure that are
present in the selected vbaproject.

Now, the trouble that I have is that when i call the userform(show), two
things occur that I dont Want:

1- I Go back in Excel, insted of staying in VBE
2- When I unload the userform, I dont go back to the Vbaproject that a
selected before I call the Userform, i usually go back to the vbaproject
that contains my userform module.

So:
How I can I stay In vbe, while calling a userform ?
And :
How can I go back to the VbaProject that I was on before calling the
Userform when I unload it from the memorie. (this is how the sequence end
with)

Thank's

Philippe

Phil,
following should work.


NOTE:
There's 1 problem that I dont know how to easily solve
this code limits the movements of the form to the boundaries of the VBE
form.


Option Explicit
Private Declare Function FindWindow Lib "user32.dll" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private 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 UserForm_Initialize()
With Application.VBE.MainWindow
If .Visible And Not .WindowState = 1 Then 'minimized
.SetFocus
Call SetParent(Me.Hwnd, .Hwnd)
End If
End With
Repaint
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


phil wrote :

Hi

I have created a xla file. The main purpuse of that file, is to be
able to copy the code lines of the selected VBProject on to the
clipboard. In that file, i have created 2 userforms. The goal of the
first one when called upon, is to present in a TreeView control, all
the Procedure that are present in the selected vbaproject.

Now, the trouble that I have is that when i call the userform(show),
two things occur that I dont Want:

1- I Go back in Excel, insted of staying in VBE
2- When I unload the userform, I dont go back to the Vbaproject that
a selected before I call the Userform, i usually go back to the
vbaproject that contains my userform module.

So:
How I can I stay In vbe, while calling a userform ?
And :
How can I go back to the VbaProject that I was on before calling the
Userform when I unload it from the memorie. (this is how the sequence
end with)

Thank's

Philippe

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 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


*** Sent via Developersdex http://www.developersdex.com ***

pls send it.
put @ and . around chello below...




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Hellboy wrote :

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 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


*** Sent via Developersdex http://www.developersdex.com ***