Sarek
05-11-2007, 06:13 PM
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! ^^
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! ^^