Cycling through textboxes in a loop

[DaveMcPhet]

Hi all,

I am working on a sheet I created for work. What I am trying to do is use a loop to put the data from a user form onto a sheet. The number of textboxes varies on the number of nodes on a line I have. i could have from 1 to 8 text boxes. each named tbmux1, tbmux2, and so on. What i need is to use the counter to determine which textbox i am pulling the info from. here is what i have been trying and cannot seem to get it to work. as you can tell by what I have commented out I have been trying a lot of different things. the ones that are commented out, i will be able to get one I can figure out how to do this. I am still learning so excuse my work if it looks a little rudimentary

For counter = 1 To muxqty
                        nextline = counter + 1
                        boxno = counter
                        Worksheets("results").Range("STOP_MUX1").Offset(counter, 0) = tbmux & boxno [B]<this is what i need to say tbmux1, tbmux2, etc>[/B]
                        Worksheets("results").Range("START_MUX1").Offset(nextline, 0) = tbmux & boxno
                        'Worksheets("results").Range("INCHES1").Offset(counter, 0) = mux(counter)mark
                        'Worksheets("results").Range("mtype1").Offset(counter, 0) = cbtype(counter)
                        'Worksheets("results").Range("FEET1").Offset(counter, 0) = Worksheets("results").Range("INCHES1").Offset(counter, 0) * _
                            Worksheets("results").Range("G6").value
                    Next counter

.

 

[dmt32]

Hi,
welcome to forum
to refer to controls in a loop with naming convention you describe you would do following

Worksheets("results").Range("STOP_MUX1").Offset(counter, 0) = Me.Controls("tbmux" & boxno).Value

where Me keyword refers to your Userform object.

Dave

 

[DaveMcPhet]

Dave,

Thanks that worked perfectly. I thought about this because I have used it to fill a user form from a search function, but everything else I was trying didn't seem to work and was so frustrated that i forgot to go back and give this a try. thanks for the help.

Dave

 

[dmt32]

Thanks that worked perfectly. I thought about this because I have used it to fill a user form from a search function, but everything else I was trying didn't seem to work and was so frustrated that i forgot to go back and give this a try. thanks for the help.

Your welcome - glad suggestion helped

As an aside, rather the keep typing the worksheet on each line of your code, consider declaring an object variable & set a reference to the required worksheet
You can then use this throughout your code to qualify the worksheets ranges etc.

Dim wsResults As Worksheet
Set wsResults = ThisWorkbook.Worksheets("results")

For counter = 1 To muxqty
    boxno = counter
      wsResults.Range("STOP_MUX1").Offset(counter, 0) = Me.Controls("tbmux" & boxno).Value
Next counter

If there are many lines in same section of code that refer to same worksheet you can use a With Statement

With wsResults
        .Range("STOP_MUX1").Offset(counter, 0) = Me.Controls("tbmux" & boxno).Value
        'other ranges etc
        
    End With

This is just a very basic guide - Further guidance available in VBA helpfile

Hope Helpful

Dave

 

[DaveMcPhet]

That is a great tip. I may use it so I can add it to my tool box. There are alot of lines where i name the sheet so this would make my life easier. I usually just copy/paste then change what I need. but this will make it even more efficient.


Dave

 

[DaveMcPhet]

I think I need to create a "notebook" with tips and tricks so I don't have to keep going back through other code i've written to find stuff like this that i've done.

 

[dmt32]

That is a great tip. I may use it so I can add it to my tool box. There are alot of lines where i name the sheet so this would make my life easier. I usually just copy/paste then change what I need. but this will make it even more efficient.


Dave

In a userform, if you place the variable at the very TOP of the userforms code page OUTSIDE any procedure this makes it available to all the codes in it whilst it is loaded.

You intilalize the variable using the forms intialize event

Dim wsResults As Worksheet

Private Sub UserForm_Initialize()
    Set wsResults = ThisWorkbook.Worksheets("results")
End Sub

Don't make the mistake some do of renaming this event to match your userforms name - just use it as published.

Dave

 

[DaveMcPhet]

Well I ran into another issue outside my knowledge range. I am trying to add aggregate current through a node and would love to loop the process to reduce the amount of code. I am not sure if this is even possible. Below is the "long way" of what I am trying to do. It is written for 8 nodes whereas most of the time it will be 3-6 nodes. The value of "Ixx" is the sum of column G of the same row and everything below it in column g. iF you need more information, let me know and I can get it for you.

                    wsResults.Range("I24").Formula = ("=G31+G30+G29+G28+G27+G26+G25+G24")
                    wsResults.Range("I25").Formula = ("=G31+G30+G29+G28+G27+G26+G25")
                    wsResults.Range("I26").Formula = ("=G31+G30+G29+G28+G27+G26")
                    wsResults.Range("I27").Formula = ("=G31+G30+G29+G28+G27")
                    wsResults.Range("I28").Formula = ("=G31+G30+G29+G28")
                    wsResults.Range("I29").Formula = ("=G31+G30+G29")
                    wsResults.Range("I30").Formula = ("=G31+G30")
                    wsResults.Range("I31") = ("=G31")

I tried to write this but kept getting a compiler error saying i needed a list seperator at "muxcurr1"

wsResults.Range("nodecurr1").Offset(counter, 0) = Application.WorksheetFunction.Sum(wsResults.Range("wsResults.range("muxcurr1").offset(counter, 0):G31"))

 

[dmt32]

really a new question & probably should post to new thread.

As a guess at what you want

wsResults.Range("nodecurr1").Offset(counter, 0) = _
    Application.WorksheetFunction.Sum(wsResults.Range(wsResults.Range("muxcurr1").Offset(counter, 0).Address & ":G31"))

Dave