[RESOLVED] Link TextBox in UserForm to Formula-Based Cell

In my worksheet, I have a cell containing the following formula:

=SUBTOTAL(3,A:A)-1

This counts the number of rows that I have displayed, with the exception of the first two rows (which, contain frozen headers, so they don't need to be counted). So, when I apply a filter to my data, this number changes accordingly to tell me how many rows I have displayed.

I also have a UserForm that contains a lot of buttons, which do a bunch of sorting operations on my data. I want to add a textbox to this UserForm that will display the number of rows that are displayed after each sorting operation.

I am able to link ActiveX textboxes to the cell that contains my formula, and it works perfectly. However, I don't think you can use ActiveX textboxes in a Userform; just Form Controls.

I tried using a Form Control textbox in my UserForm, then linked the TextBox's Properties, I set the ControlSource to the cell containing my formula. This works for one sorting operation. Then, the cell containing my formula gets overwritten with the value to which the formula evaluated on the first sort operation. The formula gets deleted, and thus the number ceases to change with subsequent filters.

How can I link a Form Control TextBox to a Formula-Based cell in my worksheet so that the formula continues to update as I apply filters to my data? Thanks,

Quote:

---

How can I link a Form Control TextBox to a Formula-Based cell in my worksheet so that the formula continues to update as I apply filters to my data? Thanks,

---

Let's say that the formula is in Cell B5 in Sheet1 then right click on the textbox in the userform and in the 'ControlSource' property type

Sheet1!B5

That's it...

That is where I had typed

=T12

which resulted in the problem I had described above. When I tried typing in

OJTDatabase!T12

it gave me this error: "Could not set the ControlSource property. Invalid property value."

My sheet is named OJT Database. I tried entering the following into the ControlSource property:

OJT Database!T12
OJTDatabase!T12
=OJT Database!T12
=OJTDatabase!T12
=Sheet1!T12
Sheet1!T12

They all gave the same error message.

If your sheet name is "OJT Database" then change it to "OJTDatabase" (without space) and then set the control source property to OJTDatabase!T12

I removed the space, then entered OJTDatabase!T12 as the ControlSource. It accepted it, but did the same thing as when I had entered =T12. It replaced the formula with its result...

ouch My Bad.... I think it's enough for today... need to go home and sleep :lol:

Give me a moment...

Edit

Try this

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    UserForm1.TextBox1.Value = Sheets("OJTDatabase").Range("T12").Value
End Sub

---

Nope, thanks, but it doesn't work. I've tried a million things. I guess I'm just going to have to leave it out of the UserForm. By the way, do you know of a way to have a UserForm open and still be able to select cells in the spreadsheet? Thanks a lot, Koolsid.

But it works...

See the example attached.

I am using the sum formula as an example. If you change any value in the grey area then you sum will change and the textbox value will also change.

All right! You're right, it works, but I didn't realize it because I wasn't changing my selection in the spreadsheet. I didn't think about the face that the

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    UserForm1.TextBox1.Value = Sheets("OJTDatabase").Range("T12").Value
End Sub

---

will only update the textbox's value when you change your selection in the worksheet. Is there any way to modify this so that it updates the textbox whenever the user sorts on the data being displayed? I modified the spreadsheet you created to more accurately demonstrate the application to which I am putting the UserForm. I need to have the textbox update without having the user change the selection in the spreadsheet. Thanks Koolsid...

Nevermind, I've got it. I just needed to use the Worksheet_Calculate() procedure instead of the Worksheet_ChangeSelection(ByVal Target As Range) procedure. It works perfectly now. THanks Koolsid, for all your help!

kool :)

If your query is solved then do remember to mark this thread resolved. ;)