Solved: Copy module from present file to PERSONAL.XLS

[t2t]

Hello guys,

congrats for this great forum, best I have seen ever!

My doubt is:

I have this model sheet (Bridge Graph 3.0.xls), which, by the way, started at 1.0 (of course), and it has many macros on it.
The problem is that users constantly copy the worksheet to other files, and then the macros stop working (specially when they move the sheet).

I found out that, the solution would be to place the macros on the PERSONAL.XLS file, and make the proper commands from there.

OK, but the regular users are not in the mood to import the macros :banghead: , I need to make a button to make the personal ready to work on diferent machines.

From my research I found out this article:
(can't post link, but it is in this site:
Instructions for use: Create the Personal.xls workbook and import files )

Which is great, but it deals with importing the macros from a BAS file, not from the original one.

I tried to adapt it, but I cant make the thing work!

So, here is the situation:

File: "Bridge Graph 3.0.xls"




Option Explicit
Option Compare Text

Sub AddPersonal()
Application.ScreenUpdating = False
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, FileName
Dim blnPersonal As Boolean
Dim FSO As Object, Folder As Object, File As Object
Dim PersonalXLS As Workbook

'Create an instance of the FileSystemObject and obtain the
'excel startup folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(Application.StartupPath)

'See if Personal.xls already exists
For Each File In Folder.Files
If UCase(File.Name) = "PERSONAL.XLS" Then
If WorkbookIsOpen(File.Name) Then
Set PersonalXLS = Application.Workbooks(File.Name)
Else
Set PersonalXLS = Application.Workbooks.Open(File.Path)
End If

blnPersonal = True
Exit For
End If
Next

'Prompt user to continue if Personal.xls was found
If blnPersonal = True Then
If MsgBox("Personal.xls already exists." & vbCrLf & vbCrLf & _
"Would you like to add modules to it?", vbYesNo, _
"Personal.xls Exists") = vbNo Then GoTo ExitHere
End If

'If Personal.xls was not found, create Personal.xls workbook and hide it
If blnPersonal = False Then
Set PersonalXLS = Application.Workbooks.Add
PersonalXLS.SaveAs (Application.StartupPath & "\PERSONAL.xls")
Windows("PERSONAL.xls").Visible = False

Windows("Bridge Graph 3.0.xls").Activate

'Import the Files to the Personal.xls workbook

With ActiveSheet.VBProject.VBComponents.Select
.PersonalXLS.VBProject.VBComponents.Copy

End With

ExitHere:
'Clean up
PersonalXLS.Save
Set PersonalXLS = Nothing
Set FSO = Nothing
Set Folder = Nothing
Set File = Nothing
Application.ScreenUpdating = True
End Sub

I know I must be doing something stupid, but if you guys could help I would really appreciate it.

Best regards,

Thiago Scaldaferri

[Zack Barresse]

Hi there, welcome to the board!

I'm not sure this is the best way to go about this. I realize I'm not answering your question, and posing another question in return, so apologies. Would it not be better to create an add-in for your code that users used? If multiple users are using your workbook (and subsequently your code), and you aren't going to lock down your workbook, you should think about how you want your code transported, or maybe even the scope of the workbook you're working on. If you kept your code in an add-in, and distributed it to all your users, then it wouldn't really matter what they copied. You would need to do some adjusting in your code to accomodate for this, but you wouldn't have the current issue you have.

Rule #1 about end users: they're dumb. And I don't mean they're really dumb, I mean assume they're all dumb. You won't be able to accomodate all users, but you can curb them as you wish by how you setup the system in which they'll use. As stated above, an add-in is one way to do that. And since we don't really know much about your workbook, or have a sample file, there's not a lot we'll be able to do besides give you suggestions.

HTH

[t2t]

Zack,

thanks for the answer. Is I really considered doin a Add-in, I even created my XLA and a spreadshee that automatically would install it, but the problem is I could not find how to reference to my macros inside the add-in. I mean, when I open the VBA navigator I see the Add-in and all the VB code in it, but from Excel I cannot make them run!
I imagined that, add-ins were not suppose to work just as simple macros (and a really seached for that).
If you guys could point me how to run the SUBs in a add-in, yes, my problem definitively would be solved, but by now, I can't do it by myself.

Best regards,

Thiago

[mdmackillop]

Application.Run "Test.xla!MyMacro"

[Zack Barresse]

Just adding to how Malcolm showed you how to reference it, just do it as any other workbook, because that's what it is - a hidden workbook loaded at Excel startup. :)

[t2t]

Zack and Mackillop,

That was exactly what I imagined (and tried before), but, if I want to run the sub from a button (for ex.) in the excel sheet, I imagined I shall enter the command as:
NameOfAddin.xla!NameSub

But excel automatically changes it to just the name of the sub! Ok, I imagine that he does that because the add in is already loaded! Great, but than it just doesn't run! Doesn't find the sub!

One other thing is, when I open the "Macros" player, the name of the subs does not appear, is that correct?

(This is why I left Add-in, and was trying with personal!)

Thanks for all the help!

[Zack Barresse]

If you want your users to pick a routine from a list, why not just make an add-in out of it and make your own customized menu? Wouldn't that be simpler, more elegant and professional looking?

[t2t]

Zack,

actually, I am not doing the Macros > Run thing, the problem is that the file is complicated, and from now it already has like 50 different commands, each one in one part of the calculation and with different actions.

Is it impossible to run the Add-in subs from buttons in the sheet? How do I refer to them? Do I have to make any changes in the code so that they are "run able"?

Thanks

[mdmackillop]

Is the sub Private or is it in a Worksheet module? If so, try removing Private and make sure it's in a standard module.

[Zack Barresse]

Is it impossible to run the Add-in subs from buttons in the sheet? How do I refer to them? Do I have to make any changes in the code so that they are "run able"?
It still sounds like an add-in would be a better method.

[t2t]

mdmackillop and Zack Barresse, thanks so much fo all the help!
The thing is that I used to have the code inside the sheet, and changed the name of the module (many versions ago).
I started a new file, moved the sheet, created anoter file from zero (and put the code in this), made the Add-in, and now it works!
You were great!

Thanks so much!
(changing the name of the topic, ok?)