Returning a value in the textbox on the form

[cal911]

Hi guys,

I have a set of data in excel and i am tying to display the name of the item associated with smallest value in the text box located on a user form when the user clicks a command button. I am using the following code:



Return_Value()
Worksheets("Sheet1").Range("B3").Sort Key1:=Worksheets("Sheet1"). _
Range("B3"), Order1:=xlAscending, Header:=xlYes
txbSmallestValue.Text = Worksheets("Sheet1").Range("A3")
End Sub


However, I get an error. Any help would be much appreciated!

[MINCUS1308]

what error are you receiving?

[MINCUS1308]

My Code varies a little from yours but I dont get errors :)
Try this and let me know how it goes...


Private Sub UserForm_Click()

Worksheets("Sheet1").Range("B3").Sort key1:=Worksheets("Sheet1"). _
Range("B3"), Order1:=xlAscending, Header:=xlNo

txbSmallestValue.Text = Worksheets("Sheet1").Range("b3")
End Sub

[MINCUS1308]

The code is really determined by how you have your worksheet laid out.
if you have a header on that column then you probably need to set the b3 in this code to b4 :
txbSmallestValue.Text = Worksheets("Sheet1").Range("b3")
and obviously you'll have to revert the Header:= back to xlYes

without knowing the error and without having the spread sheet it will be very difficult to "fix" the error

[cal911]

Hi MINCUS1308, I tried your way and it gave me an error msg again :(
It tells me "Compile Error: variable not defined" while highlighting tbxSmallestValue.Text
If I Dim tbxSmallestValue As Textbox it gives me a Run-Time 91 error saying "Object variable or block variable not set"
Could it have anything to do with not having the textbox on the worksheet, but instead having it on the user form?

[MINCUS1308]

when you built your user form did you name the textbox "tbxSmallestValue" ?
Check the properties bar on the right the (Name) should be tbxSmallestValue

[MINCUS1308]

could you attach the file so that I may see what is happening.

[MINCUS1308]

ah errr you might try something like UserForm1.tbxSmallestValue.text = blah blah blah


Private Sub UserForm_Click()

Worksheets("Sheet1").Range("B3").Sort key1:=Worksheets("Sheet1"). _
Range("B3"), Order1:=xlAscending, Header:=xlNo

UserForm1.txbSmallestValue.Value = Worksheets("Sheet1").Range("b3").value End Sub

[cal911]

ah errr you might try something like UserForm1.tbxSmallestValue.text = blah blah blah



UserForm1.txbSmallestValue.Value = Worksheets("Sheet1").Range("b3").value End Sub


Ahhhh How did I not think about that! Thank you soooo much - that did it!

[MINCUS1308]

Sometimes its the small things that are the hardest to recognize.
Best of luck with your coding.