Results 1 to 11 of 11

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    Resolved [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,

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    Re: Link TextBox in UserForm to Formula-Based Cell

    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.

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Link TextBox in UserForm to Formula-Based Cell

    If your sheet name is "OJT Database" then change it to "OJTDatabase" (without space) and then set the control source property to OJTDatabase!T12
    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    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...

  6. #6
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Link TextBox in UserForm to Formula-Based Cell

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

    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
    Last edited by Siddharth Rout; Feb 4th, 2010 at 03:36 PM.
    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

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    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.

  8. #8
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Link TextBox in UserForm to Formula-Based Cell

    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.
    Attached Files Attached Files
    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

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    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...
    Attached Files Attached Files

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    27

    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!

  11. #11
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Link TextBox in UserForm to Formula-Based Cell

    kool

    If your query is solved then do remember to mark this thread resolved.
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width