[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,
Re: Link TextBox in UserForm to Formula-Based Cell
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...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Re: Link TextBox in UserForm to Formula-Based Cell
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...
Re: Link TextBox in UserForm to Formula-Based Cell
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.
Re: Link TextBox in UserForm to Formula-Based Cell
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...
Re: Link TextBox in UserForm to Formula-Based Cell
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!