0

I am working in Excel 2010, developing a user form for data entry into a spreadsheet (we don't trust every end user to enter their data correctly in an open spreadsheet).

Currently, each textbox is named txtDataVal1 to N, with the number incrementing with each new box, and the following code is used to move data from textbox to spreadsheet:

'intRowSelect is given a value elsewhere, by the user's list box selection
Cells(intRowSelect, 2).Value = txtDataVal1.Value
Cells(intRowSelect, 3).Value = txtDataVal2.Value
....
Cells(intRowSelect, 20).Value = txtDataVal20.value

My question: Is there a way to write a simple loop to increment the numeric value at the end of txtDataVal, so I don't have 100+ lines of the bulky code above?

For example (I know this is a non-functional example, consider it pseudo code):

Dim i as Integer
Dim intColumnSelect as Integer
intColumnSelect = 1
i = 1

Do While i < intColumnSelect
intColumnSelect = intColumnSelect + 1
Cells(intRowSelect, intColumnSelect).Value = "txtDataVal" + i.Value
i = i + 1
Loop
Community
  • 1
  • 1
Munch
  • 25
  • 4

1 Answers1

0

In your while loop you can reference your textboxes as a part of the form's Controls collection to accomplish this. Assuming your form is named FORM1:

Do While i < intColumnSelect
  intColumnSelect = intColumnSelect + 1
  Cells(intRowSelect, intColumnSelect).Value = FORM1.Controls("txtDataVal" & i).Value
  i = i + 1
Loop
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I am now facing an unexpected error. Using the above code, I get "Run-time error '13': Type Mismatch". It seems to be having a problem with "txtDataVal" or + i, any idea? – Munch Sep 24 '14 at 16:32
  • My fault. I used a `+` instead of an `&`. One adds things together the other concatenates... can't add a string :( I have updated the answer. – JNevill Sep 24 '14 at 16:34
  • 1
    That's the ticket! Thanks. I discovered a flaw in my logic though, where i and intColumnSelect would eventually exceed the number of textboxes on the form, causing an error (textbox didn't exist to write to!) To fix this, I counted the used header columns on the worksheet with [code that counts non-blank columns, found here](http://stackoverflow.com/questions/6897347/finding-the-number-of-non-blank-columns-in-an-excel-sheet-using-vba) The code still handles a dynamic column number, as well as a dynamic number of text boxes. Just mentioning for future coders that find this. Thanks a lot, J! – Munch Sep 24 '14 at 17:19