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.
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.
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.
It doesn't work. There is a Run Time error that follows.
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.
Yes and there are 1 to 24 TextBoxes within the Userform.
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
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.
In the actual sub I have parameters for several variables. But you're right, the error highlights the me.controls section.
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.
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)
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).
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.
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."
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).
the point is that the line
doesn't do anythingPlease Login or Register to view this content.
Josie
if at first you don't succeed try doing it the way your wife told you to
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.Please Login or Register to view this content.
Last edited by Solus Rankin; 07-09-2013 at 12:14 PM.
you also have many unqualified range references-for instance
oughta bePlease Login or Register to view this content.
or better stillPlease Login or Register to view this content.
Please Login or Register to view this content.
Yes, but I would like to do something that like make something of this nature occur.
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:So if A = 2 then Me.Controls("TextBox2") would have a 2 in its contents?Please Login or Register to view this content.
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)
Thank you for the suggestion. I need the help.
Are trying are you trying to place the product of the formula in the textbox?
in a series of textboxes (1 - 24), each having unique values. That is why I am trying to do TextBox & A.
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?
Maybe try:Please Login or Register to view this content.
No, I'm trying to do something else.
Could you be a little more specific?
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)
If you want to set the control to a variable, the syntax would be
Please Login or Register to view this content.
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)
....
try
Please Login or Register to view this content.
This gives me a Run Time error.
Run-time error '-2147024809 (80070057)': 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 userformCould not find the specified object.
Press debug and check the line which is highlighted, the control used in that line does not exist
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
I thought it would be highlighted:Actually I have the same error when running the code with no textbox on the Userform, but it's not in the declaration.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks