Unload me closing all userforms

chroniclesofdave

New Member
Joined
Aug 8, 2016
Messages
48
So i have a second userform that launches from the first, and in the second i use a bit of code as seen here
Code:
Private Sub CommandButton66_Click()        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
    
        ProcName = "Button1"
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With
       StartAdd
End Sub
Sub StartAdd()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = ActiveWorkbook.VBProject.VBComponents("Module1")
        
        
        AddProcedureToModule
        
    End Sub


Sub AddProcedureToModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character




        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub Button1()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "Dim obj As New DataObject"
            LineNum = LineNum + 1
            .InsertLines LineNum, "Dim txt As String"
            LineNum = LineNum + 1
            .InsertLines LineNum, "'Put some text inside a string variable"
            LineNum = LineNum + 1
            .InsertLines LineNum, "txt = " & DQUOTE & TextBox1.Value & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "'Make object's text equal above string variable"
            LineNum = LineNum + 1
            .InsertLines LineNum, "obj.SetText txt"
            LineNum = LineNum + 1
            .InsertLines LineNum, "'Place DataObject's text into the Clipboard"
            LineNum = LineNum + 1
            .InsertLines LineNum, "obj.PutInClipboard"
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
            LineNum = LineNum + 1
        End With
    End Sub

and this codes intent is to overwrite a bit of code in Module1. Everything works fine, but when i run the code and then click the command button with
Code:
unload me
or
Code:
unload userform8
or even
Code:
userform8.hide
it always hides userform1 as well. maybe it isn't actually hidden, but it is no longer visible, and i cannot locate it on my screen. if i do not run the above code and click the button, everything works fine, but once the code has run, it starts to act up. i have trolled through forum posts both here, MS forums, and everywhere i can, but i have had no success. any ideas?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
A couple of things might help. There's some reference library required and your macro options developer settings must be set to allow for axis to the VBA project model. I'm guessing that if U are able to make the changes then those aren't your problem. I would trial saving the workbook (programattically) after making the changes to the module code.... XL does occasionally get lost. But maybe it's as simple as....
Code:
Userform1.Show
after the Unload userform8. Anyways, good luck and good name. HTH. Dave
 
Last edited:
Upvote 0
You're going to run into issues like this when you're writing code with code at runtime. Why do you need to do that? It's almost always unnecessary and there is usually a better way
 
Upvote 0
Thanks, i will give this a shot!

A couple of things might help. There's some reference library required and your macro options developer settings must be set to allow for axis to the VBA project model. I'm guessing that if U are able to make the changes then those aren't your problem. I would trial saving the workbook (programattically) after making the changes to the module code.... XL does occasionally get lost. But maybe it's as simple as....
Code:
Userform1.Show
after the Unload userform8. Anyways, good luck and good name. HTH. Dave
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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