Results 1 to 2 of 2

Can't automate sums of variable Textboxes in User Form

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365 Version 2501 64-bit
    Posts
    1,474

    Can't automate sums of variable Textboxes in User Form

    Following Code works perfectly to add sum of three textboxes on User Form into a fourth box:

    
    Private Sub CommandButton6_Click()
    
    TextBox1318.Value = Val(TextBox1313.Value) + Val(TextBox1315.Value) + Val(TextBox1317.Value)
    
    End Sub
    But I need to "automate" it, as I need the same process across 368 sets, so tried the following two variations:

    
    Private Sub CommandButton6_Click()
    
    For T = 1318 To 1686 Step 8
    
    Val(TextBox & T.value) = Val(TextBox & (T - 5).Value) + Val(TextBox & (T - 3).Value) + Val(TextBox & (T - 1).Value)
    
    Next
    
    End Sub
    That generated the Error Message that "val" was an invalid qualified, so tried:

    Private Sub CommandButton6_Click()
    
    x = 1313
    y = 1315
    z = 1317
    
    For T = 1318 To 1686 Step 8
    
    ctrl("TextBox" & T).Value = ctrl("TextBox" & x).Value + ctrl("TextBox" & y).Value + ctrl("TextBox" & z).Value
    
    x = x + 8
    y = y + 8
    z = z + 8
    
    Next
    
    End Sub
    Which throws a Run-Time error 91 (Object Variable or With block variable not set)

    Hope someone can provide the "right" syntax?

    And would be even more useful if anyone can provide the Code that changes just the one textbox automatically if any of the three "trigger" columns in that row change, rather than looping through all of them if any row changes?

    All solutions, suggestions and ideas welcome as ever

    Ochimus
    Last edited by Ochimus; 02-12-2019 at 09:13 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop to refresh user form TextBoxes
    By lanceloz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-28-2017, 02:36 AM
  2. Given a User Form with 256 textboxes - how can I access a selected one?
    By joebbb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2015, 11:17 AM
  3. Sum 2 textboxes on user form to 3rd when data entered
    By edopts in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-17-2014, 04:04 PM
  4. Copy data from previous row into new row IF user form textboxes are not populated
    By syl127 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-03-2014, 10:35 AM
  5. grouping multiple textboxes on a user form
    By Obfuscated in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2013, 05:31 PM
  6. Can a command button make textboxes dissapear from a user form
    By shakey775 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-19-2012, 03:01 AM
  7. Formatting alignment in User Form Textboxes
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2011, 01:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1