I have a userform with 100 textboxes on it and another form with 100
labels. The textboxes are filled with numbers 1 to 26.

I want to change the label captions on the second form to the alpha
equivalent of the number in the textbox, ie if textbox 27 has 5 in it,
the caption for label 27 should be E.

Also if I change say textbox 45 from 7 to 22, I want this cahnge to be
reflected in the caption for label 45.

I understand this could be done through control arrays but I can't get
them to work for textboxes.

Could someone help please?
Thanks
Chris

Chris,

The chief problem is that form controls in a class don't have Exit or
AfterUpdate events, so validation is difficult, because you have to use the
Change event. Beyond that, I think I was able to get what you want:

Assuming:
UserForm1 and UserForm2: with TextBox1 ... TextBox100 and Label1...
Label100, respectively
A class module called "FormTboxClass"
A standard module

In the FormTboxClass this code:

Public WithEvents FormTbox As msforms.TextBox
Private Sub FormTbox_Change()

With FormTbox
If IsNumeric(.Value) Then
If .Value = 1 And .Value <= 26 Then
UserForm2.Controls("Label" & Mid(FormTbox.Name, 8)).Caption =
Chr(FormTbox.Value + 64)
End If
End If
End With
End Sub

In UserForm1 this code:

Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub UserForm_Initialize()

Dim tmpctl As Control
Dim ctl As FormTboxClass

Set myControls = New Collection
For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.TextBox Then
Set ctl = New FormTboxClass
Set ctl.FormTbox = tmpctl
myControls.Add ctl
End If
Next

End Sub

In the standard module this code:

Sub test()

UserForm1.Show modeless
UserForm2.Show modeless

End Sub

Run the "test" macro. Move Form2 to reveal Form1. You should get the
actions you described. Notice that if you go outside the range 1 to 26 or
enter non-numeric characters, nothing happens. I tried to do more with
invalid entries, but couldn't find a good solution. I think the forms have
to be modeless for this to work, but not positive.

hth,

Doug

"inquirer" wrote in message
u...
I have a userform with 100 textboxes on it and another form with 100
labels. The textboxes are filled with numbers 1 to 26.

I want to change the label captions on the second form to the alpha
equivalent of the number in the textbox, ie if textbox 27 has 5 in it, the
caption for label 27 should be E.

Also if I change say textbox 45 from 7 to 22, I want this cahnge to be
reflected in the caption for label 45.

I understand this could be done through control arrays but I can't get
them to work for textboxes.

Could someone help please?
Thanks
Chris

Doug Glancy wrote:
Chris,

The chief problem is that form controls in a class don't have Exit or
AfterUpdate events, so validation is difficult, because you have to use the
Change event. Beyond that, I think I was able to get what you want:

Assuming:
UserForm1 and UserForm2: with TextBox1 ... TextBox100 and Label1...
Label100, respectively
A class module called "FormTboxClass"
A standard module

In the FormTboxClass this code:

Public WithEvents FormTbox As msforms.TextBox
Private Sub FormTbox_Change()

With FormTbox
If IsNumeric(.Value) Then
If .Value = 1 And .Value <= 26 Then
UserForm2.Controls("Label" & Mid(FormTbox.Name, 8)).Caption =
Chr(FormTbox.Value + 64)
End If
End If
End With
End Sub

In UserForm1 this code:

Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub UserForm_Initialize()

Dim tmpctl As Control
Dim ctl As FormTboxClass

Set myControls = New Collection
For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.TextBox Then
Set ctl = New FormTboxClass
Set ctl.FormTbox = tmpctl
myControls.Add ctl
End If
Next

End Sub

In the standard module this code:

Sub test()

UserForm1.Show modeless
UserForm2.Show modeless

End Sub

Run the "test" macro. Move Form2 to reveal Form1. You should get the
actions you described. Notice that if you go outside the range 1 to 26 or
enter non-numeric characters, nothing happens. I tried to do more with
invalid entries, but couldn't find a good solution. I think the forms have
to be modeless for this to work, but not positive.

hth,

Doug

"inquirer" wrote in message
u...

I have a userform with 100 textboxes on it and another form with 100
labels. The textboxes are filled with numbers 1 to 26.

I want to change the label captions on the second form to the alpha
equivalent of the number in the textbox, ie if textbox 27 has 5 in it, the
caption for label 27 should be E.

Also if I change say textbox 45 from 7 to 22, I want this cahnge to be
reflected in the caption for label 45.

I understand this could be done through control arrays but I can't get
them to work for textboxes.

Could someone help please?
Thanks
Chris



Thanks Doug.
I tried that and it seems ok but for one thing - I can't enter more than
1 digit in the first userform - as soon as I try to enter say 11, it
accepts the first 1 then tries to insert a 1 in the code for the
formtboxclass.

The other thing I wanted to do was evaluate an integer j in the test
module code and then substitute Chr(j + 64)
in label(j).caption
Will
UserForm2.Controls("Label" & j).Caption = Chr(j + 64)
do that for me?

Thanks
Chris

Doug
"inquirer" wrote in message
...
Doug Glancy wrote:
Chris,

The chief problem is that form controls in a class don't have Exit or
AfterUpdate events, so validation is difficult, because you have to use
the Change event. Beyond that, I think I was able to get what you want:

Assuming:
UserForm1 and UserForm2: with TextBox1 ... TextBox100 and Label1...
Label100, respectively
A class module called "FormTboxClass"
A standard module

In the FormTboxClass this code:

Public WithEvents FormTbox As msforms.TextBox
Private Sub FormTbox_Change()

With FormTbox
If IsNumeric(.Value) Then
If .Value = 1 And .Value <= 26 Then
UserForm2.Controls("Label" & Mid(FormTbox.Name, 8)).Caption =
Chr(FormTbox.Value + 64)
End If
End If
End With
End Sub

In UserForm1 this code:

Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub UserForm_Initialize()

Dim tmpctl As Control
Dim ctl As FormTboxClass

Set myControls = New Collection
For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.TextBox Then
Set ctl = New FormTboxClass
Set ctl.FormTbox = tmpctl
myControls.Add ctl
End If
Next

End Sub

In the standard module this code:

Sub test()

UserForm1.Show modeless
UserForm2.Show modeless

End Sub

Run the "test" macro. Move Form2 to reveal Form1. You should get the
actions you described. Notice that if you go outside the range 1 to 26
or enter non-numeric characters, nothing happens. I tried to do more
with invalid entries, but couldn't find a good solution. I think the
forms have to be modeless for this to work, but not positive.

hth,

Doug

"inquirer" wrote in message
u...

I have a userform with 100 textboxes on it and another form with 100
labels. The textboxes are filled with numbers 1 to 26.

I want to change the label captions on the second form to the alpha
equivalent of the number in the textbox, ie if textbox 27 has 5 in it,
the caption for label 27 should be E.

Also if I change say textbox 45 from 7 to 22, I want this cahnge to be
reflected in the caption for label 45.

I understand this could be done through control arrays but I can't get
them to work for textboxes.

Could someone help please?
Thanks
Chris



Thanks Doug.
I tried that and it seems ok but for one thing - I can't enter more than 1
digit in the first userform - as soon as I try to enter say 11, it accepts
the first 1 then tries to insert a 1 in the code for the formtboxclass.

I was able to enter 2 digits as long as the overall number was in the range
1 to 26. But because I used the Change event, the letters in changed with
every digit typed. Since no Exit or AfterUpdate event is available in the
Class textbox, I don't know how to avoid this. Are you never able to enter
the second digit?

The other thing I wanted to do was evaluate an integer j in the test
module code and then substitute Chr(j + 64)
in label(j).caption
Will
UserForm2.Controls("Label" & j).Caption = Chr(j + 64)
do that for me?
I think so, but I'd need to see the code in order to try to answer.

Thanks
Chris

Doug

Doug Glancy wrote:
Doug
"inquirer" wrote in message
...

Doug Glancy wrote:

Chris,

The chief problem is that form controls in a class don't have Exit or
AfterUpdate events, so validation is difficult, because you have to use
the Change event. Beyond that, I think I was able to get what you want:

Assuming:
UserForm1 and UserForm2: with TextBox1 ... TextBox100 and Label1...
Label100, respectively
A class module called "FormTboxClass"
A standard module

In the FormTboxClass this code:

Public WithEvents FormTbox As msforms.TextBox
Private Sub FormTbox_Change()

With FormTbox
If IsNumeric(.Value) Then
If .Value = 1 And .Value <= 26 Then
UserForm2.Controls("Label" & Mid(FormTbox.Name, 8)).Caption =
Chr(FormTbox.Value + 64)
End If
End If
End With
End Sub

In UserForm1 this code:

Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub UserForm_Initialize()

Dim tmpctl As Control
Dim ctl As FormTboxClass

Set myControls = New Collection
For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.TextBox Then
Set ctl = New FormTboxClass
Set ctl.FormTbox = tmpctl
myControls.Add ctl
End If
Next

End Sub

In the standard module this code:

Sub test()

UserForm1.Show modeless
UserForm2.Show modeless

End Sub

Run the "test" macro. Move Form2 to reveal Form1. You should get the
actions you described. Notice that if you go outside the range 1 to 26
or enter non-numeric characters, nothing happens. I tried to do more
with invalid entries, but couldn't find a good solution. I think the
forms have to be modeless for this to work, but not positive.

hth,

Doug

"inquirer" wrote in message
m.au...


I have a userform with 100 textboxes on it and another form with 100
labels. The textboxes are filled with numbers 1 to 26.

I want to change the label captions on the second form to the alpha
equivalent of the number in the textbox, ie if textbox 27 has 5 in it,
the caption for label 27 should be E.

Also if I change say textbox 45 from 7 to 22, I want this cahnge to be
reflected in the caption for label 45.

I understand this could be done through control arrays but I can't get
them to work for textboxes.

Could someone help please?
Thanks
Chris



Thanks Doug.
I tried that and it seems ok but for one thing - I can't enter more than 1
digit in the first userform - as soon as I try to enter say 11, it accepts
the first 1 then tries to insert a 1 in the code for the formtboxclass.


I was able to enter 2 digits as long as the overall number was in the range
1 to 26. But because I used the Change event, the letters in changed with
every digit typed. Since no Exit or AfterUpdate event is available in the
Class textbox, I don't know how to avoid this. Are you never able to enter
the second digit?


I can not enter more than 1 digit without triggering the FormTbox check