Checking Textboxes

[Sarek]

Hi people!

This is my first post, so pardon me if I make some mistakes... and my english is a bit raw... So, please excuse me if it's difficult to follow...

Well, this is my dilemma...

I made a xls with some macros and ActiveX controls... It has lots of sheets

the first sheet has stadistics about the other sheets.

The other sheets have each one 7 textBoxes.

Each TextBox has to keep a String and a number in two arrays.

I didn't know how to save the values in the variables,. so I used LostFocus


Private Sub TextBox1_LostFocus()


germen1 = TextBox1.Value

For N = 1 To 100 Step 1

If germen1 = germenes(N) Then

germenes1(N) = germenes1(N) + 1
Exit For

Else
If germenes(N) = "" Then

germenes(N) = germen1
germenes1(N) = germenes1(N) + 1
Exit For

End If
End If

Next

End Sub

Each textbox saves the values in germen1, germen2, etc.

In the main sheet, I used a CommandButton to look for these variables:


Private Sub CommandButton1_Click()
For N = 1 To 100

If germenes1(N) > germencomun1 Then

germencomun = germenes(N)
germencomun1 = germenes1(N)

End If

Next

Range("D31").FormulaR1C1 = germencomun
Range("E31").FormulaR1C1 = germencomun1

End Sub


Graet! But now...what if I close Excel and then I open it again? LostFocus won't work anymore... so I think about this:

This new commandbutton goes through every sheet and activate and "lostfocus" on every textbox:


Private Sub CommandButton1_Click()
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count

Sheets(ActiveSheet.Index + i).Select
ActiveSheet.TextBox1.Activate
ActiveSheet.TextBox2.Activate
ActiveSheet.TextBox3.Activate
ActiveSheet.TextBox4.Activate
ActiveSheet.TextBox5.Activate
ActiveSheet.TextBox6.Activate
ActiveSheet.TextBox7.Activate
If ActiveSheet.Index = ThisWorkbook.Worksheets.Count Then
Exit For
End If

Next

Sheets("Principal").Select

For N = 1 To 100

If germenes1(N) > germencomun1 Then



germencomun = germenes(N)
germencomun1 = germenes1(N)

End If

Next

Range("D31").FormulaR1C1 = germencomun
Range("E31").FormulaR1C1 = germencomun1

End Sub


But it isn't working! Where is my problem?

Or do you know another more efficient way to recheck every textbox in every sheet?

Thanks!

Greetings from Argentina! ^^

[Bob Phillips]

How about just calling the procedure



Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count

With Sheets(ActiveSheet.Index + i)
Call .TextBox1.LostFocus
Call .TextBox2.LostFocus
Call .TextBox3.LostFocus
Call .TextBox4.LostFocus
Call .TextBox5.LostFocus
Call .TextBox6.LostFocus
Call .TextBox7.LostFocus
End With

Next

[Sarek]

I tried calling the procedure, but it gave me "Error '438': Object doesn't support this property or method"


Any idea why?