+ Reply to Thread
Results 1 to 35 of 35

VBA, Userform, Textbox: Refer to TextBox Name as a Variable

  1. #1
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    I want to refer to a textbox, but also concatenate (perhaps this is not the correct wording) with an integer (i.e. TextBox1, where 1 is the variable A).

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 07-08-2013 at 06:52 PM. Reason: As per Forum Rule #3, please use code tags…Thanks.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Your answer is in your Generic code...

    Me.Controls("Textbox" & A).Value

    ...or I don't understand what you are asking.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    It doesn't work. There is a Run Time error that follows.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Is this code located in the UserForm's code module? The Me keyword refers to the userform that the code is located within.

    Do you have a TextBox5 ? In your code, A = 5.

  5. #5
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Yes and there are 1 to 24 TextBoxes within the Userform.

  6. #6
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    What does the runtime error say? which error number + description? Which line is highlighted when you debug it?
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Does the txt = Me.Controls("Textbox" & A).Value line get highlighted when you click the DeBug button. If not, perhaps the error is elsewhere in the code?

    All I can tell you is I have used this syntax often. I just ran this test code on a userform that had a CommandButton1 and a TextBox5.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    In the actual sub I have parameters for several variables. But you're right, the error highlights the me.controls section.

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Try using a case matching form. Also I note that the variable i1 might be mistaken for a cell address and shouldn't be used as a variable name.
    With that many controls, you might have renamed them, did a space sneak in where it shouldn't be?

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  10. #10
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Are matching forms like parameters in Java? Because that is where i1 is declared. And I don't think I have any spaces.

    Sub Generic(i1 as integer, i0 as integer)

  11. #11
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    I'm not sure whether its "good practice" or if its a limitation of Excel VBA, but I have been taught that one should not use variable names that can be confused for cell addresses (in either A1 or R1C1 notation).

  12. #12
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Here is an Excel file with the userform in it. If you respond to me, just refer to the subs and I will edit separently.
    Attached Files Attached Files

  13. #13
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    What are you trying to do here: Capture.PNG

    You aren't setting anyting to/from Me.Controls("Textbox" & A).Value
    Last edited by Solus Rankin; 07-09-2013 at 11:57 AM. Reason: vague
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  14. #14
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    These ranges are extracting data from specific areas of the spreadsheet, dependent on whether they select Drivers, Programs or Vehicles from ComboBox1 on the Userform.

    However, the ranges that contain P1, P2, i0 and i1 are ranges calculating sums of variable areas. Where P1 is 15 and P2 is 18, and i0 and i1 are consecutive numbers (either 1 and 2, or 3 and 4, etc).

  15. #15
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    the point is that the line
    Please Login or Register  to view this content.
    doesn't do anything
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  16. #16
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Please Login or Register  to view this content.
    is going to change which textbox you address based on the value of 'A'. But what do you want to do with the textbox you're addressing?

    Please Login or Register  to view this content.
    Or perhaps a with statement to modify the textbox several times. The satement as you have it addresses a variable textbox but doesn't do anything with it.
    Last edited by Solus Rankin; 07-09-2013 at 12:14 PM.

  17. #17
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    you also have many unqualified range references-for instance
    Please Login or Register  to view this content.
    oughta be
    Please Login or Register  to view this content.
    or better still
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Yes, but I would like to do something that like make something of this nature occur.

  19. #19
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    What nature? I'm still unsure what you're trying to do with the information in the textbox. Are you trying to set the textbox to its variable number?

    Like:
    Please Login or Register  to view this content.
    So if A = 2 then Me.Controls("TextBox2") would have a 2 in its contents?

  20. #20
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    For instance, I want to set up the following:

    Dim txt as TextBox (This might be inaccurate)

    txt = Me.Controls("Textbox" & A).Value

    txt.Value = Application.WorksheetFunction.SumIf(DRIVERS, ComboBox2.Value, RNGACT1)

  21. #21
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Thank you for the suggestion. I need the help.

  22. #22
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Are trying are you trying to place the product of the formula in the textbox?

  23. #23
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    in a series of textboxes (1 - 24), each having unique values. That is why I am trying to do TextBox & A.

  24. #24
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Okay if I'm understanding you correctly, the formula for each textbox will change based on the variable and you would like to loop through all textboxes?

  25. #25
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Maybe try:
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    No, I'm trying to do something else.

  27. #27
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Could you be a little more specific?

  28. #28
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    For instance, I want to set up the following:

    Dim txt as TextBox (This might be inaccurate)

    txt = Me.Controls("Textbox" & A).Value

    txt.Value = Application.WorksheetFunction.SumIf(DRIVERS, ComboBox2.Value, RNGACT1)

  29. #29
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    If you want to set the control to a variable, the syntax would be

    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    mikerickson, I used your code and I got a compiler error (Compiler error: User-defined type not define). Below is what my actual code looks like. The sub header is then highlighted in yellow.


    Sub FM(P1 As Integer, P2 As Integer, i0 As Integer, i1 As Integer)
    Dim DRIVERS As Range, PROGRAMS As Range, VEHICLES As Range
    Set DRIVERS = Sheet3.Range("F3:F2001")
    Set PROGRAMS = Sheet3.Range("H3:H2001")
    Set VEHICLES = Sheet3.Range("E3:E2001")

    Dim A As Integer
    A = i0 + i1

    Dim myTextBox As MS.Forms.TextBox
    Set myTextBox = Me.Controls("TextBox" & A)

    Dim myComboBox As MS.Forms.ComboBox
    Set myComboBox = Me.Controls("ComboBox" & A)

    Set RNGACT0 = Sheet3.Range(Cells(3, P1 + (i0 * 8)), Cells(2001, P1 + (i0 * 8)))
    Set RNGBUS0 = Sheet3.Range(Cells(3, P2 + (i0 * 8)), Cells(2001, P2 + (i0 * 8)))
    Set RNGACT1 = Sheet3.Range(Cells(3, P1 + (i1 * 8)), Cells(2001, P1 + (i1 * 8)))
    Set RNGBUS1 = Sheet3.Range(Cells(3, P2 + (i1 * 8)), Cells(2001, P2 + (i1 * 8)))

    If myComboBox = False Then
    myTextBox.Value = ""
    ElseIf ComboBox1.Value = "Drivers" And ComboBox3.Value = "Sum" And ComboBox4.Value = "Actuals" And CheckBox2.Value = True Then
    myTextBox.Value = Application.WorksheetFunction.SumIf(DRIVERS, ComboBox2.Value, RNGACT1)
    ....

  31. #31
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    try
    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    This gives me a Run Time error.

    Run-time error '-2147024809 (80070057)': Could not find the specified object.

  33. #33
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    Could not find the specified object.
    So we found out that you want to get/set a control (value) which does not exist on your userform

    Press debug and check the line which is highlighted, the control used in that line does not exist

  34. #34
    Registered User
    Join Date
    04-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    I did and it led me all the way to the place as before. It stops right here, highlighting it all in yellow.

    Dim myTextBox As MSForms.TextBox

  35. #35
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: VBA, Userform, Textbox: Refer to TextBox Name as a Variable

    I thought it would be highlighted:
    Please Login or Register  to view this content.
    Actually I have the same error when running the code with no textbox on the Userform, but it's not in the declaration.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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