Changing workbook code via vba

[Journeyman3000]

Hello all,

I have code for my sheets (e.g Sheet1(Main)) that runs all the activation and change events etc. I also have various modules.

I have code that will modify the modules: for example, this follow code deletes a module:

Please Login or Register  to view this content.

However, I don't know how to manipulate subroutines within the sheet module.

In principle, I would like to locate a string within a Sub, and change it. Or, if I have to change the entire sheet module without affecting the sheet itself.

How can this be done - can anyone point me in the right direction. I look at CPearson stuff but it didnt seem to provide this particular answer.

Cheers

[LJMetzger]

Hi Journeyman3000,

See the attached file and/or code in post #3 of the following thread. If it doesn't solve your problem, the references may point you in the right direction. http://www.excelforum.com/excel-prog...de-in-vba.html

Lewis

[Norie]

Why do you want to do this?

[Journeyman3000]

Thank you for your responses. I was kinda hoping for a shorter solution in which I could provide the sheetname, locate the string in the VBE editor and change it. I'll search through the solution and see if I can find it there. cheers

Norrie: I want to do it becuase I've distributed the workbook to mulitple people, and I need to update the code in each. I can do this by looping through each book, looking for the correct worksheet, updating the VBE ect. In this case, it's just a single string. Cheers

[Norie]

Why not use an add-in?

[Journeyman3000]

I don't know what add-in to use, and I beleive it should be acheviable with code.

[xladept]

Hi Journeyman,

Try using the forum search with "VBComponent" - I've found several entries that seem pertinent to your task

[HaHoBe]

Hi, Journeyman3000,

I´d have a read at parameters and pass the information you want to change via parameter, a list in a worksheet, a name in the name manager, a text file rather than changing code.

Ciao,
Holger

[mikerickson]

The best way to update the modules in others workbooks is to create exported files of the modules and have them run a routine that deletes the existing module and imports the files (exports) that you provide.

[Norie]

You would create the add-in, distribute it, edit it, redistribute it etc.

Using code to create/change code can be done but can cause problems.

For example security settings would need to be changed (lowered).

PS If it's just a single string why not store it somewhere instead of hard-coding it?

[Journeyman3000]

Thank you all for for advice.

Using addins is a good idea, but for this particular excercise I needed to change the code in the VBE for Sheet1. It turns out there are several locations that needed updates so I can pretty much use the same code, although lesson learned for the future. I found the following code, and modified it slightly and it works as required.

Please Login or Register  to view this content.

Thanks to all who provided advice.

Cheers