Userform Textbox's Multiples - Easier way to get cell data to these boxes

[Dingoz2012]

Hi.

Would appreciate any help.

I have a userform with 100 Textboxes on it. The majority of the textboxes will be for the DISPLAY of cell values only. With some being used for the input of new data which the goes onto the worksheet.

My question is. Is there an easier way to get the cell data into the textboxes, than what is currently being done, which is the below.

'Required Number of Components
'These go from textbox1 to 14   Cell range b9 to b22

Textbox1.value = sheets("Data").range("b9").value 
Textbox2.value = sheets("Data").range("b10").value

' Already Built
' These go from textbox15 to textbox28 cells c9 to c22

Textbox15.value = sheets("Data").range("c9").value
textbox16.value =sheets("Data").range("c10").value 


' Etc Etc, multiple columns, with groupings of data primarily in range B9 to B22, C9 to C22, E9 to E22 etc etc

Any help greatly appreciated.

 

[dmt32]

Hi,
untested but you can try

    Dim TextBoxData As Variant
    Dim i As Integer, r As Integer, c As Integer


    TextBoxData = ThisWorkbook.Worksheets("Data").Cells(9, 2).Resize(14, 7).Value2


    For c = 1 To UBound(TextBoxData, 2)
        For r = 1 To UBound(TextBoxData, 1)
            i = i + 1
            Me.Controls("TextBox" & i).Value = TextBoxData(r, c)
        Next r
    Next c

Dave

 

[Dingoz2012]

Hi,
untested but you can try

    Dim TextBoxData As Variant
    Dim i As Integer, r As Integer, c As Integer


    TextBoxData = ThisWorkbook.Worksheets("Data").Cells(9, 2).Resize(14, 7).Value2


    For c = 1 To UBound(TextBoxData, 2)
        For r = 1 To UBound(TextBoxData, 1)
            i = i + 1
            Me.Controls("TextBox" & i).Value = TextBoxData(r, c)
        Next r
    Next c

Dave

Thank you so much. It work exactly how I was after.

 

[dmt32]

Most welcome - thanks for feedback

Dave

 

[Dingoz2012]

No worries, I greatly value the suggestions by people on here. I go away and dissect the code to see if I can figure out how it works. I'm now onto the second part of my problem. Been struggling for a few hours trying to figure out the most efficient way of getting new data back to the sheet.

So that above code populates the boxes that are on the user form. Is there a way of if I change a value in any of those textboxes that it can be sent to the worksheet in the corresponding cell. ? Without having to type out a line like sheets("Data").range("G9").value = me.textbox71.value etc etc for a 100 textboxes..

Nothing like a challenge

 

[dmt32]

Hi,
just use same approach but in reverse

    Dim i As Integer, r As Integer, c As Integer
    
    Dim TextBoxData(1 To 14, 1 To 7) As Variant


    For c = 1 To UBound(TextBoxData, 2)
        For r = 1 To UBound(TextBoxData, 1)
            i = i + 1
             TextBoxData(r, c) = Me.Controls("TextBox" & i).Value
        Next r
    Next c
    
    ThisWorkbook.Worksheets("Data").Cells(9, 2).Resize(14, 7).Value = TextBoxData

Dave

 

[Dingoz2012]

Hi,
just use same approach but in reverse

    Dim i As Integer, r As Integer, c As Integer
    
    Dim TextBoxData(1 To 14, 1 To 7) As Variant


    For c = 1 To UBound(TextBoxData, 2)
        For r = 1 To UBound(TextBoxData, 1)
            i = i + 1
             TextBoxData(r, c) = Me.Controls("TextBox" & i).Value
        Next r
    Next c
    
    ThisWorkbook.Worksheets("Data").Cells(9, 2).Resize(14, 7).Value = TextBoxData

Dave

Of course, why didn't I think of this. hahahahaha.

It kind of works but then it doesn't at the same time.

The original one works, to get the data into the text boxes from column 2 row 9-22 through to column 17 rows 9-22. But when I use the second one to add data back to the sheet I only need to add data back to the range of cells g9:q22 I'm stuck on how to get that working, because at the moment it's only changing the values in 2 columns

 

[dmt32]

If you want to post data to another range then amend code as follows

ThisWorkbook.Worksheets("Data").Cells(9, 7).Resize(14, 7).Value = TextBoxData

Dave