suznal


I have a UserForm that contains several textboxes used to display data from a worksheet. I want the data to load and be visible when the userform opens, currently I have to tab through the TextBoxes to get the values to show.

I would like to know what event (I can't seem to find one) will load the data when the UserForm opens and require no user input to display.

Sample code for the first three boxes below...


Code Block

Private Sub TextBox1_Enter()
If Sheet10.CheckBox1.Value = True Then
TextBox1.Value = "1"
ElseIf Sheet10.CheckBox2.Value = True Then
TextBox1.Value = "2"
ElseIf Sheet10.CheckBox3.Value = True Then
TextBox1.Value = "3"
End If
End Sub

Private Sub TextBox2_Enter()
If Sheet11.CheckBox1.Value = True Then
TextBox2.Value = "1"
ElseIf Sheet11.CheckBox2.Value = True Then
TextBox2.Value = "2"
ElseIf Sheet11.CheckBox3.Value = True Then
TextBox2.Value = "3"
End If
End Sub

Private Sub TextBox3_Enter()
If Sheet13.CheckBox1.Value = True Then
TextBox3.Value = "1"
ElseIf Sheet13.CheckBox2.Value = True Then
TextBox3.Value = "2"
ElseIf Sheet13.CheckBox3.Value = True Then
TextBox3.Value = "3"
End If
End Sub





Re: Excel UserForm - TextBoxes - can't load data on open

Shasur


Have you tried using the code in UserForm_Initialize event

This event fires when before the form is Shown

Cheers
Shasur






Re: Excel UserForm - TextBoxes - can't load data on open

bi-lya

Or test this

Code Block

Private Sub UserForm_Activate()
uSheets = Array(10, 11, 13)
a = 1
For Each Sheet In uSheets
With Worksheets(Sheet)
If .CheckBox1.Value = True Then
Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
Next
End Sub






Re: Excel UserForm - TextBoxes - can't load data on open

suznal

The code works fine for three sheets (10, 11, 13) with each sheet having three checkboxes (cb1, cb2, cb3).

But when I add more sheets to the array I get an error - "Object does not support this property or method". I can't see what is wrong...

Here's what I have...

Code Block

Private Sub UserForm_Activate()
uSheets = Array(10, 11, 13, 12, 14, 29)
a = 1
For Each Sheet In uSheets
With Worksheets(Sheet)
If .CheckBox1.Value = True Then
Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
Next
End Sub

Private Sub TextBox1_Enter()
If Sheet10.CheckBox1.Value = True Then
TextBox1.Value = "1"
ElseIf Sheet10.CheckBox2.Value = True Then
TextBox1.Value = "2"
ElseIf Sheet10.CheckBox3.Value = True Then
TextBox1.Value = "3"
End If

End Sub

Private Sub TextBox2_Enter()
If Sheet11.CheckBox1.Value = True Then
TextBox2.Value = "1"
ElseIf Sheet11.CheckBox2.Value = True Then
TextBox2.Value = "2"
ElseIf Sheet11.CheckBox3.Value = True Then
TextBox2.Value = "3"
End If
End Sub

Private Sub TextBox3_Enter()
If Sheet13.CheckBox1.Value = True Then
TextBox3.Value = "1"
ElseIf Sheet13.CheckBox2.Value = True Then
TextBox3.Value = "2"
ElseIf Sheet13.CheckBox3.Value = True Then
TextBox3.Value = "3"
End If
End Sub

Private Sub TextBox4_Enter()
If Sheet12.CheckBox1.Value = True Then
TextBox4.Value = "1"
ElseIf Sheet12.CheckBox2.Value = True Then
TextBox4.Value = "2"
ElseIf Sheet12.CheckBox3.Value = True Then
TextBox4.Value = "3"
End If
End Sub

Private Sub TextBox5_Enter()
If Sheet14.CheckBox1.Value = True Then
TextBox5.Value = "1"
ElseIf Sheet14.CheckBox2.Value = True Then
TextBox5.Value = "2"
ElseIf Sheet14.CheckBox3.Value = True Then
TextBox5.Value = "3"
End If
End Sub

Private Sub TextBox6_Enter()
If Sheet29.CheckBox1.Value = True Then
TextBox6.Value = "1"
ElseIf Sheet29.CheckBox2.Value = True Then
TextBox6.Value = "2"
ElseIf Sheet29.CheckBox3.Value = True Then
TextBox6.Value = "3"
End If
End Sub

It works fine, but when I add the other sheets to the array then I get an error, the debugger points to this line...

Code Block
If .CheckBox1.Value = True Then

Anybody see something I can't

(By the way, the userform loads the data into textboxes 1 - 5. The error is being called from textbox 6. )






Re: Excel UserForm - TextBoxes - can't load data on open

bi-lya

Hi!

Use names of sheets. It's better.
Remove all "Private Sub TextBox_Enter()" - they are useless
And so you may use "error":

Code Block
Private Sub UserForm_Activate()
uSheets = Array("Sheet10", "Sheet11", "Sheet13", "Sheet29")
a = 1
For Each Sheet In uSheets
On Error Resume Next
With Worksheets(Sheet)
If .CheckBox1.Value = True Then
Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
On Error GoTo 0
Next
End Sub





Re: Excel UserForm - TextBoxes - can't load data on open

suznal

bi-lya, when I run the code as you have it all the textbox values display as "1" regardless of which CheckBoxes are ticked.

When I remove the "error" lines, I get a "subscript out of range" error and it points to the following line in the code...

Code Block

Private Sub UserForm_Activate()
uSheets = Array("Sheet10", "Sheet11", "Sheet13", "Sheet12", "Sheet14", "Sheet29")
a = 1
For Each Sheet In uSheets
'On Error Resume Next
With Worksheets(Sheet)
If .CheckBox1.Value = True Then
Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
'On Error GoTo 0
Next
End Sub

If instead I run the "Private Sub TextBox_Enter()", the correct values are used, but are not loaded until after the user tabs through the textbox. Is there no event that will display the values when the userform loads

Also, with the above code, how do I know which textbox recieves the value for each sheet Does it run through the sheet array in the order they are listed






Re: Excel UserForm - TextBoxes - can't load data on open

Cringing Dragon

suznal wrote:

Also, with the above code, how do I know which textbox recieves the value for each sheet Does it run through the sheet array in the order they are listed

Yes, it runs through in the order listed in the array, but it relies on the textboxes being numbered in exactly that order. In the code, just before the For Each, there is a line that assigns a = 1. Inside the For loop, all the lines assigning values to the textbox use the code Controls("textbox" & a). The first time through the For loop, a=1, so all those lines will refer to Controls("textbox1"). Before the Next line, there is a line assigning a = a + 1. So the second time through the For loop, a=2 and values are assigned to textbox2. And the third a=3, so it applies to textbox3, etc. If your textboxes were numbered in a different order, this would not work.

Note that the name of a control can be changed to whatever you like, you don't have to be stuck with the default names that Excel has given them. You can name them so that there is a logical sequence to follow.

suznal wrote:

If instead I run the "Private Sub TextBox_Enter()", the correct values are used, but are not loaded until after the user tabs through the textbox. Is there no event that will display the values when the userform loads

The UserForm_Activate event might be the right one. Your comment about the correct values being used but not displaying until the user tabs through makes me think that what is needed is to refresh the controls so that they display the right numbers after your calculations run. So add the following line into your code in between the End If and the End With lines:

Code Block
.Refresh

If that doesn't work, try replacing Refresh with Repaint.

suznal wrote:

When I remove the "error" lines, I get a "subscript out of range" error and it points to the following line in the code...

Sheet is a VBA keyword. Try using another name for the variable, such as sSheet.

Code Block

For Each sSheet In uSheets
'On Error Resume Next

With Worksheets(sSheet)

Also all your lines starting with the word Controls need a . in front of them. Eg:

Code Block

.Controls("textbox" & a).Text = "1"






Re: Excel UserForm - TextBoxes - can't load data on open

bi-lya

Cringing Dragon, thank you!
But only one correction:
you wrote:

"Also all your lines starting with the word Controls need a . in front of them. Eg:

Code Block

.Controls("textbox" & a).Text = "1"


Thera are TextBoxes on the UserForm, therefore (if it's need):
Code Block
Me.Controls("textbox" & a).Text = "1"





Re: Excel UserForm - TextBoxes - can't load data on open

Cringing Dragon

I should have realised the controls were on a form, not on the sheet!

I leapt to the false conclusion that because it was inside the "With" statement it belonged to the sheet.






Re: Excel UserForm - TextBoxes - can't load data on open

suznal

bi-lya and Dragon,

Thanks for hanging in there with me. I probably won't do anything with the file until tomorrow or Sunday - my brain is a little fried right now...

But honestly I appreciate all your help so far. Perhaps one day when I start a project from scratch instead of cleaning up other peoples messes, you won't see my problems as much as maybe a little bit of help on other's posts.

Keep your fingers crossed!!






Re: Excel UserForm - TextBoxes - can't load data on open

suznal

I have to tell you, this is proving more difficult than I had imagined. Here's what happened...

If I run the following code...

Code Block

Private Sub TextBox1_Enter()
If Sheet10.CheckBox1.Value = True Then
TextBox1.Value = "1"
ElseIf Sheet10.CheckBox2.Value = True Then
TextBox1.Value = "2"
ElseIf Sheet10.CheckBox3.Value = True Then
TextBox1.Value = "3"
End If
End Sub


Private Sub TextBox2_Enter()
If Sheet11.CheckBox1.Value = True Then
TextBox2.Value = "1"
ElseIf Sheet11.CheckBox2.Value = True Then
TextBox2.Value = "2"
ElseIf Sheet11.CheckBox3.Value = True Then
TextBox2.Value = "3"
End If
End Sub

Private Sub TextBox3_Enter()
If Sheet13.CheckBox1.Value = True Then
TextBox3.Value = "1"
ElseIf Sheet13.CheckBox2.Value = True Then
TextBox3.Value = "2"
ElseIf Sheet13.CheckBox3.Value = True Then
TextBox3.Value = "3"
End If
End Sub

Private Sub TextBox4_Enter()
If Sheet12.CheckBox1.Value = True Then
TextBox4.Value = "1"
ElseIf Sheet12.CheckBox2.Value = True Then
TextBox4.Value = "2"
ElseIf Sheet12.CheckBox3.Value = True Then
TextBox4.Value = "3"
End If
End Sub

Private Sub TextBox5_Enter()
If Sheet14.CheckBox1.Value = True Then
TextBox5.Value = "1"
ElseIf Sheet14.CheckBox2.Value = True Then
TextBox5.Value = "2"
ElseIf Sheet14.CheckBox3.Value = True Then
TextBox5.Value = "3"
End If
End Sub

Private Sub TextBox6_Enter()
If Sheet29.CheckBox1.Value = True Then
TextBox6.Value = "1"
ElseIf Sheet29.CheckBox2.Value = True Then
TextBox6.Value = "2"
ElseIf Sheet29.CheckBox3.Value = True Then
TextBox6.Value = "3"
End If
End Sub

The Userform opens, but the textboxes are blank. As the user tabs through them the correct values are displayed. I like that it pulls the correct values, but I don't like the user having to 'tab through' to see the values.

But if I run the following code instead...

Code Block
Private Sub UserForm_Activate()
uSheets = Array("Sheet10", "Sheet11", "Sheet13", "Sheet12", "Sheet14", "Sheet29")
a = 1
For Each sSheet In uSheets
On Error Resume Next
With Worksheets(sSheet)
If .CheckBox1.Value = True Then
Me.Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Me.Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Me.Controls("textbox" & a).Text = "3"
End If
.Repaint
End With
a = a + 1
On Error GoTo 0
Next
End Sub

The Userform loads and displays a value of "1" in each textbox(which are incorrect). The values do not change when tabbed through or "clicked" on.

Hiding the lines...

Code Block

'On Error Resume Next

'On Error GoTo 0

Results in a "Subscript out of range" on the following line...

Code Block
With Worksheets(sSheet)

I have tried every combination of Refresh and Repaint with and without 'Me.Controls', and there were no noticable differences.

When I initially ran the code under the 'UserForm_Activate' with the first three textboxes it pulled the correct values, (but users still had to tab through).

I don't understand why when I load through the "Private Sub TextBox", it finds the correct value, but loading through "Private Sub UserForm" finds all values of "1". Is TextBox maybe not the best control to use Would there be something better

I just need to display the results, there will not be any changes being made through the userform. Maybe MsgBox






Re: Excel UserForm - TextBoxes - can't load data on open

suznal

OK, I couldn't give up - so I thought I would try something. I inserted the following code into the userform...

Code Block

Private Sub UserForm_Activate()

If Sheet10.CheckBox1.Value = True Then
TextBox1.Value = "1"
ElseIf Sheet10.CheckBox2.Value = True Then
TextBox1.Value = "2"
ElseIf Sheet10.CheckBox3.Value = True Then
TextBox1.Value = "3"
End If
If Sheet11.CheckBox1.Value = True Then
TextBox2.Value = "1"
ElseIf Sheet11.CheckBox2.Value = True Then
TextBox2.Value = "2"
ElseIf Sheet11.CheckBox3.Value = True Then
TextBox2.Value = "3"
End If
If Sheet13.CheckBox1.Value = True Then
TextBox3.Value = "1"
ElseIf Sheet13.CheckBox2.Value = True Then
TextBox3.Value = "2"
ElseIf Sheet13.CheckBox3.Value = True Then
TextBox3.Value = "3"
End If
If Sheet12.CheckBox1.Value = True Then
TextBox4.Value = "1"
ElseIf Sheet12.CheckBox2.Value = True Then
TextBox4.Value = "2"
ElseIf Sheet12.CheckBox3.Value = True Then
TextBox4.Value = "3"
End If
If Sheet14.CheckBox1.Value = True Then
TextBox5.Value = "1"
ElseIf Sheet14.CheckBox2.Value = True Then
TextBox5.Value = "2"
ElseIf Sheet14.CheckBox3.Value = True Then
TextBox5.Value = "3"
End If
If Sheet29.CheckBox1.Value = True Then
TextBox6.Value = "1"
ElseIf Sheet29.CheckBox2.Value = True Then
TextBox6.Value = "2"
ElseIf Sheet29.CheckBox3.Value = True Then
TextBox6.Value = "3"
End If

End Sub

And it works perfectly!!!

Although, I really don't want to have such a messy looking and uneccesarily lengthy code for the form. There has to be a way to make it work with the examples supplied by bi-lya and Dragon.

Any ideas as to why it would work like this, but not with the others







Re: Excel UserForm - TextBoxes - can't load data on open

Cringing Dragon

The .Repaint method might still help - but because I somehow overlooked the fact that the textboxes are on a form, not on a sheet, my example refreshed the sheets not the form. So instead of .Repaint, it should have been

Me.Repaint

Try putting that line at the end of your code (immediately before the End Sub). That might force a redraw and save you from tabbing through the textboxes.

If that doesn't work, you could try to replicate the "tabbing through the textboxes" behaviour by activating each textbox after the value has been changed. Something like:

TextBox1.SetFocus

might work, but I don't know if SetFocus will have quite the same behaviour as placing the cursor inside the textbox. If it doesn't, try adding after the SetFocus:

TextBox1.SelStart = 0

TextBox1.SelLength = TextBox1.TextLength

Unfortunately this is getting messier and messier. Hopefully the Me.Repaint does the trick.




Re: Excel UserForm - TextBoxes - can't load data on open

bi-lya

Hi, suznal!
I make userform with 6 textbox. Write the code (in module of the userform!):

Code Block
Private Sub UserForm_Activate()
DisplayCheck
End Sub

Don't forget property ShowModal of the form to set "False"

I make Module1 and write code:

Code Block

Public Sub SingleCheckbox(OnSheet As Worksheet, ThisControl As Object)
Dim oleTemp As OLEObject

If ThisControl.Object.Value Then
For Each oleTemp In OnSheet.OLEObjects
If TypeName(oleTemp.Object) = "CheckBox" Then
If oleTemp.Object.GroupName = ThisControl.Object.GroupName Then
If oleTemp.Name <> ThisControl.Name Then
oleTemp.Object.Value = False
End If
End If
End If
Next
End If
DisplayCheck
End Sub

Public Sub DisplayCheck()
Dim a As Integer

uSheets = Array("Sheet10", "Sheet11", "Sheet13")
a = 1
For Each Sheet In uSheets
With Worksheets(Sheet)
If .CheckBox1.Value = True Then
UserForm1.Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
UserForm1.Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
UserForm1.Controls("textbox" & a).Text = "3"
Else
UserForm1.Controls("textbox" & a).Text = ""
End If
End With
a = a + 1
Next
End Sub

I write the following code in modules of all Sheets:

Code Block

Private Sub CheckBox1_Click()
SingleCheckbox ActiveSheet, CheckBox1
End Sub

Private Sub CheckBox2_Click()
SingleCheckbox ActiveSheet, CheckBox2
End Sub

Private Sub CheckBox3_Click()
SingleCheckbox ActiveSheet, CheckBox3
End Sub

And all works fine!
Try...





Re: Excel UserForm - TextBoxes - can't load data on open

Cringing Dragon

If you wanted to try again with the For Each loop...

suznal wrote:

Results in a "Subscript out of range" on the following line...

Code Block
With Worksheets(sSheet)

That implies that sSheet (representing each sheet name in the array) is not a Worksheet.

... and I've just realised that in your examples Sheet10, Sheet11, etc are not referenced as worksheets, but as objects. I've think I've been overlooking the obvious again. Sheet10 isn't a worksheet, is it Is it another form

Now that I've realised that, try changing bi-lya's code to use object references, not sheet names:

Code Block

Private Sub UserForm_Activate()
uSheets = Array(Sheet10, Sheet11, Sheet13, Sheet12, Sheet14, Sheet29)
a = 1
For Each sSheet In uSheets
On Error Resume Next
With sSheet
If .CheckBox1.Value = True Then
Me.Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Me.Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Me.Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
On Error GoTo 0
Next
Me.Repaint
End Sub