PDA

View Full Version : Values from Spreadsheet into Userform Textbox



Usoki
09-27-2008, 09:14 PM
:omg2: I cannot, for the life of me, figure out the proper commands. I've looked all over the help manuals, but I can't seem to get the right keywords to search for what I want the userform to do.

I have a series of textboxes in the userform, enabled set to false so that they cannot be written in, that I would like to display values from the spreadsheet the userform appears from. That is, I would like the value from a cell to appear inside of the textbox when I open the userform. If the value of the cell E5 is "4", then I'd like the matching textbox to have the value "4" inside of it when the userform is shown, and that value cannot be editted from the userform. The only way to change it would be to close the form, change the value in E5, and restart the userform.

But no combination of value, text, and range commands are working for me. What am I doing wrong?

GTO
09-27-2008, 09:23 PM
Could you either attach the workbook or at least the code to the userform. Makes it easier to respond.

GTO
09-27-2008, 10:11 PM
Usoki,

You can easily set the values of text boxes during the form's initialize or activate. But rather than using a disabled textbox, try using labels. Either will display the vals you want, but the labels won't look 'greyed-out' like the disabled text boxes.

Look at the attached and note where you change the back color and special effect of the labels.

hope this helps :hi:

Option Explicit
Private Sub cmdOK_Click()
Me.Hide
Unload Me
End Sub
Private Sub UserForm_Initialize()
With shtSHeet1
Me.txtOne.Value = .Range("A1").Value
Me.txtTwo.Value = .Range("A2").Value

Me.lblOne.Caption = .Range("A1").Value
Me.lblTwo.Caption = .Range("A2").Value
End With
End Sub

Bob Phillips
09-28-2008, 12:35 AM
You should use the Userform_Activate event to load the values in case there is a button that just hides the form, then next iteration with a changed value would show the old value as initialise wouldn't be re-invoked.

GTO
09-28-2008, 02:44 AM
Usoki - xld's comment is utterly correct.

Usoki
09-28-2008, 09:34 PM
I understand the theory behind it, I just have a poor knowledge of possible commands. Most of my VBA experience involves macro recording, and then rearrangement.

And I cannot, for the life of me, figure out what I'm doing wrong.

Bob Phillips
09-28-2008, 11:59 PM
Private Sub Userform_ACtivate()

With Data
Me.STRScore.Value = .Range("E5").Value
'etc.
End With
End Sub

Usoki
10-02-2008, 12:01 AM
Ah, okay, I got it. My problem was the Sub's name. ...I wasn't aware that actually mattered.

Thanks a bunch for the help.