Renaming Userform within VBA

[Roderick_E]

Ok so I'm working to launch a subroutine from one workbook (Test2), then have it open another workbook (Test1) and replace a module and userform on Test2 with a module and userform from Test1.

I got the module part to work but I'm having trouble with the Userform. Probably because of where and when I'm trying to unload the form; since I have to use form I want to replace to actually launch the subroutine.

Here's the code; a function and a subroutine with issue lines commented out.

Launched from TEST2

Private Sub CommandButton1_Click()
Dim swbk As Workbook
Dim twbk As Workbook
Dim modname As String
Dim strfiletoopen As String
strfiletoopen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")
Set swbk = Workbooks.Open(strfiletoopen)
modname = "Module1"
Set twbk = ThisWorkbook
updatexlst swbk, modname, twbk
Unload Me '[COLOR="#FF0000"]need to remove and put later perhaps in function[/COLOR]
End Sub

Function updatexlst(sourcewb As Workbook, modname As String, targetwb As Workbook)
Dim swbk As Workbook
Dim tempForm As UserForm
    strFolder = sourcewb.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    strTempForm = strFolder & "~tmpexport.frm"
    On Error Resume Next
    Application.DisplayAlerts = False
    targetwb.VBProject.VBComponents(modname).Name = "oldmod"
'*** unload TEST2 userform here somehow?? ***
   [COLOR="#FF0000"] 'targetwb.VBProject.VBComponents("Userform1").Name = "oldform"[/COLOR]
    sourcewb.VBProject.VBComponents(modname).Export strTempFile
   [COLOR="#FF0000"] 'sourcewb.VBProject.VBComponents("Userform1").Export strTempForm[/COLOR]
    targetwb.VBProject.VBComponents.Import strTempFile
   [COLOR="#FF0000"] 'targetwb.VBProject.VBComponents.Import strTempForm[/COLOR]
    targetwb.VBProject.VBComponents.Remove targetwb.VBProject.VBComponents("oldmod")
    sourcewb.Close
    Application.DisplayAlerts = True
    Kill strTempFile
    Kill strTempForm
    On Error GoTo 0
End Function

I think if I can unload/close Userform1 within the function it may work.

 

[RickXL]

Hi,

I am not quite sure what you are trying to achieve but you might be better off with an Add-In. Would that be an option?
Basically, you keep all the master code and forms in the Add-In then just load it into every workbook as required. Rewriting code modules is somewhat of a last resort, IMHO.

Another option might be to just keep everything in your Test2 Workbook and write it in such a way that it can operate on Test1 data.


Regards,

 

[Norie]

What problems are you having?

Do you need to use the form you want to replace to launch the code that's going to replace that form?

That sounds like something that's just going to cause problems.

 

[Roderick_E]

Thanks for the replies.
RickXL I really need someone to walk me through the add-in making process. Making a "Personal" seems much easier. I've designed this tool to be something I can share with everyone in the office and allow more advanced users to add their own macros/subroutines to it and utilize the interface ("Eazy Button" system)

Norie, yes I wanted to use the same "launch" form to make it more seamless for the user. I did finally accomplish this with some fancy renaming and closing the form at the correct time and requiring the user to "relaunch" the form which removes the old version of the form.

If either are interested, look at http://rodericke.com/xlsupertool2 All the code is open.

 

[RickXL]

I have had a quick look at your code and it seems to save OK as an Add In. It is quite easy. You write your macros in the usual way then you make sure that you save the workbook as an "Excel Add In". That is, a file with a ".xlam" extension.

If you place that somewhere central where all your users can access it, they would need to use the Add In menu to navigate to it and check the box.

The advantages are that you can easily have several Add Ins in use at the same time and it does not interfere with anyone who already has a Personal workbook. It is also easier to deactivate. You just uncheck it in the Add In screen. Deactivating a Personal workbook seems much harder to me.

If you ever need to change the Add In back to a .xlsm file then you need to find the IsAddin property of its ThisWorkbook module and set it to false. However, as with a Personal file, you can change the macros without having to do that. You would to if you needed to edit a worksheet, though.

You could also customize the menu bar and add a button to run your userform, as well.

Here is an example of an Add In: https://www.dropbox.com/s/r4mlxxq8f9hcwte/Taylor Diagram V1_2.xlsm?dl=0

If you download it then select it in the Developer-AddIns screen (there is a non developer way to do this as well) then you should see an extra icon in the menu bar on the Home tab. If you click that I think it will show a userform with a Demo option. If you select that it will display a chart. If you uncheck the Add In the button should go away.


Regards,

 

[RickXL]

I woke up this morning asking myself if the link I gave you was to an Add In or a conventional macro-enabled workbook. Unfortunately, it is the latter so you will need an extra step in the instructions, sorry.

Download the .xlsm file.
Open it in Excel and save it as a .xlam
Exit Excel.
Open a new instance of Excel then load the Add In that you have just created.
Then click the extra button on the Home tab etc.

I have tried storing the workbook both ways. I think many people use it as a .xlsm file so I have decided to leave it like that. It is easy to convert it if that is what you want to do.


Regards,

 

[Roderick_E]

I have had a quick look at your code and it seems to save OK as an Add In. It is quite easy. You write your macros in the usual way then you make sure that you save the workbook as an "Excel Add In". That is, a file with a ".xlam" extension.

If you place that somewhere central where all your users can access it, they would need to use the Add In menu to navigate to it and check the box.

The advantages are that you can easily have several Add Ins in use at the same time and it does not interfere with anyone who already has a Personal workbook. It is also easier to deactivate. You just uncheck it in the Add In screen. Deactivating a Personal workbook seems much harder to me.

If you ever need to change the Add In back to a .xlsm file then you need to find the IsAddin property of its ThisWorkbook module and set it to false. However, as with a Personal file, you can change the macros without having to do that. You would to if you needed to edit a worksheet, though.

You could also customize the menu bar and add a button to run your userform, as well.

Here is an example of an Add In: https://www.dropbox.com/s/r4mlxxq8f9hcwte/Taylor Diagram V1_2.xlsm?dl=0

If you download it then select it in the Developer-AddIns screen (there is a non developer way to do this as well) then you should see an extra icon in the menu bar on the Home tab. If you click that I think it will show a userform with a Demo option. If you select that it will display a chart. If you uncheck the Add In the button should go away.


Regards,

Hmmm... that does sound simple. There must be more to it for things like "KoolTools" which is how I eventually want to make my XLSuperTool. Thanks. I'll give it a try.

 

[Roderick_E]

I woke up this morning asking myself if the link I gave you was to an Add In or a conventional macro-enabled workbook. Unfortunately, it is the latter so you will need an extra step in the instructions, sorry.

Download the .xlsm file.
Open it in Excel and save it as a .xlam
Exit Excel.
Open a new instance of Excel then load the Add In that you have just created.
Then click the extra button on the Home tab etc.

I have tried storing the workbook both ways. I think many people use it as a .xlsm file so I have decided to leave it like that. It is easy to convert it if that is what you want to do.


Regards,

Ok now how do I launch my add-in once it is added?

 

[Roderick_E]

Ok now how do I launch my add-in once it is added?

Nevermind, figured it out but now trying to add custom icon for it

 

[RickXL]

Nevermind, figured it out but now trying to add custom icon for it

Sorry, I have been out of circlation for a couple of days.

Did you use CustomUI to create your custom icon? If you do that then the icon will be stored with the Add In so when you de-delect the Add In the icon will disappear. That way you can have lots of special purpose Add Ins each with their own controls on the menu bar.

If you know all that then please ignore me!


Regards,