replace code in VBE with values on worksheet

[tpoynton]

Greetings! I am going to be updating an add in over the next few weeks, so I expect to be posting here a bit!

I have an add in that allows users to define and/or use a predefined language. I have all the translations in a worksheet, and a userform where users select their language. Since I dont speak Spanish or Albanian, this makes updating things quite cumbersome...


gLang = ThisWorkbook.Sheets("lngTrsl8").Cells(1, 15).Value
MsgBox ThisWorkbook.Sheets("lngTrsl8").Cells(409, gLang).Value


This is how I populate all text on userforms, worksheets, messageboxes, etc. gLang is a global variable, and when each userform, function is called it sets the column to find either English or another language.

I am now going to revert to an English-only version, so I can stay on top of updating. I'd like to know if there is a way to programmatically change the code in the VBE to the text in each designated cell in the workbook.

I've attached a sample workbook showing how the worksheet is structured.

Hope this makes sense! there are about 500 references like this, and I'd really like to not have to retype everything...THANKS!

[Bob Phillips]

I would suggest that you have another column that is initially empty, and link all of your labels to this column, either directly as in worksheet references, or by loaing in userform initialize event.

Then, in your language selection, provide a drop-down of languages, and use the index of the selected item to get the correct langauge column, and copy wholesale into the extra column.

[tpoynton]

Thanks Bob - I use the value stored in the worksheet (set by user with optionbuttons now) so that when Excel restarts the user doesnt have to reset the language...

Regardless, I am actually trying to ditch this and go English-only :). is it possible to take the text from the worksheet and replace references to the text in the VBE with the actual text itself? I can mock up a workbook if that will help. Or, if this doesnt sound possible, I'll just start copying and pasting and hope I get done by day's end.

I can go through the code and search and replace 'gLang' with '2'.
So, as the end result, I'd like

ThisWorkbook.Sheets("lngTrsl8").Cells(6, 2).Value

to become
"New Variable..."
which is the text stored in row 6, column 2

This will replace all captions, labels, etc.

sorry for the confusion...tim

[tpoynton]

err...nevermind. I just realized I simply need to delete the programmatic references, as they replaced text I already put on the userforms and such. This just leaves me with messageboxes and a few other odds and ends that are relatively easily dealt with.