Problem with updating the wrong worksheet

I am on only my third day with Excel VBA for apps, so this is all very new to me. But it's fun !!!

I am constructing a bi-lingual workbook. I plan to use and 'English' and a 'French' button to change languages. That bit is no problem.

I hold my texts in a worksheet with these columns ...

English text ---------------------------- Example = Year
Column ---------------------------------- Example = A
Row ---------------------------- Example = 99
Full Ref ------------------------------- Example = $A$99
Sheet name ------------------------------- Example = Prompts
Sheet no (in the workbooks array) -------------------------- Example = 1
Reference --------------------------------- Example = 1, not used at present but seemed sensible to generate one !
French text --------------------------------------- Example = Année


The columns are in that order.

I gather the data fine and it all sits in the 'Texts' worksheet absolutely fine. I use this code to do it - the example I give is for the Prompts worksheet.

zz is my ongoing output (to the texts workbook) row identifier.


=========================================================

Please Login or Register  to view this content.

=========================================================

As I say, my data looks fine in that worksheet for all of my worksheets.

When the user wants to change languages he/she presses a button to do so - no problem, I have set the first one up and it calls the correct sub-routine.

But now the problem starts

The sub that I use to reload the texts into the workbook is picking up the wrong worksheet, but getting the right cell and row. So it appears that I am referencing the worksheet the wrong way in my LoadData sub.

Here is the code

============================================================

Please Login or Register  to view this content.

=============================================================
So if, for example I was loading the row I described at the start, I would expect the row I have highlighted above to parse out as ...

Worksheets(1).Cells99, A).Value = "Year" (if language is set to EN)

But sadly it goes to another workbook altogether and I cannot understand why.

Can anybody help please.

Sorry if this is not detailed enough or too detailed. By the way this is Excel 2003

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

There's a small toolbar above the textbox that you write your question/reply in. The icon is there.

Alternatively add [/code] at the start of your code.

Then add [/code] at the end of your code