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.


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

' Now with the Prompts tab
 ' =================
  zz = 2
  For j = 1 To 150
    With Worksheets("Texts")
      If Not IsEmpty(Worksheets("Prompts").Cells(j, b).Value) And _
        Len(Worksheets("Prompts").Cells(j, a)) > 0 And _
        Not IsNull(Worksheets("Prompts").Cells(j, b).Value) Then
        zz = zz + 1
        TextRef = TextRef + 1
        .Cells(zz, 1) = Worksheets("Prompts").Cells(j, a)
        .Cells(zz, 3) = Worksheets("Prompts").Cells(j, a).Row
        .Cells(zz, 2) = a
        .Cells(zz, 4) = "$" & a & "$" & .Cells(zz, 3)
        .Cells(zz, 5) = "Prompts"
        .Cells(zz, 6) = "1"
        .Cells(zz, 7) = TextRef
      End If
    End With
    NextTextRow = zz
  Next j

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

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

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

Sub LoadText(Langue)

If IsNull(Langue) Or IsEmpty(Langue) Then
  Langue = "EN"
End If

Dim WkSheet As Integer
Dim ColOut As String
Dim RowOut As String
Dim TextOut As String



Dim zz As Integer
zz = 2        ' Avoid the header rows
With Worksheets("Texts")
  Do
    zz = zz + 1
    WkSheet = .Cells(zz, 6).Value
    If ColOut = "****" Then
      Exit Do
    End If
    RowOut = .Cells(zz, 3).Value
    ColOut = .Cells(zz, 2).Value
    If Langue = "FR" Then
      TextOut = .Cells(zz, 8).Value
    Else
      TextOut = .Cells(zz, 1).Value
    End If
    Worksheets(WkSheet).Cells(RowOut, ColOut).Value = TextOut
  Loop
End With
  
   
End Sub

=============================================================
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