userform textbox sends values to two cells, or three or four....

[rickblunt]

Greetings, I am working up another userform for quick data entry into a worksheet. Basically just five text boxes passing the name, date, item name, comments and serial number to the next blank row on the columns - simple stuff. Typically they user only enters one item at at time but occasionally they may do two or three. I was going to add in multiple sets of text boxes for each set of data but then it occurred to me, "I wonder if you could put all of the items in the same text box, maybe separated by a comma, and then just have the code parse it out to each subsequent row?" So for three items I guess I would have to type my name in the name TB three times, and then the item name and such in their own TB's separated by commas. Is this even practical? I have included the snippet of code that I am using to pass the values to the worksheet and I appreciate any input from everyone - thanks.

Private Sub EnterData_Click()

 Dim Found As Range
 Dim i As Long
 Dim Lastrow As Long
                           
            
  Worksheets("CDS History").Activate


With Sheets("CDS History")
 
     nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
 
     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = Now()
     .Range("E" & nextrow) = CommentBox
     .Range("G2") = ChemicalBox.Value
     .Range("H2") = Now()
     .Range("I2") = TechName.Value
     .Range("J2") = CommentBox.Value

' and some other stuff....

 

[steve the fish]

You should use separate textboxes for this from what i can tell. The way you describe is subject to too much user error. Its possible to split out the textbox text if separated by commas but im not sure its practical in this instance. For example as i user i want to fill in ChemicalBox and Techname. Id have to remember to use two commas to split the text else the Techname would go in column B not C.

 

[rickblunt]

I typically do that, some of my forms have 50+ textboxes, lol. It just popped in my head and I was wondering if it made sense. Off hand I am thinking the coding to split out would get kind of whacky anyway. Thanks for the input Steve,

 

[steve the fish]

Its one line of code to split out the text if it has a separator. That isnt the problem. Its the users that you need to be confident in before you could use that approach.

 

[rickblunt]

wow, is that all for the code? man, I was way off on how I thought I would have to do it, lol For this first one the users are pretty competent so I have confidence in them, enough to try it out anyway. I would be curious to see how you would tweak the code so I could learn from it.

 

[mikerickson]

I see some kind of loop like

For i = 0 to Ubound(Split(ChemicalBox.Text, ",")
    Range("A" & nextRow).Offset(i, 0).Value = Split(ChemicalBox.Text, ",")(i)
Next i

The problem I see is what to do if the user enters different number of rows in one invocation of the userform, what happens the next time?

 

[steve the fish]

Run this and see if you understand it. It creates an array based on your text box which here ive just made a string to test.

mytextboxtext = "hi,how,are,you"
arr = Split(mytextboxtext, ",")

MsgBox "My first value is " & arr(0)
MsgBox "My second value is " & arr(1)
MsgBox "My third value is " & arr(2)
MsgBox "My last value is " & arr(3)

 

[rickblunt]

Thanks Mike for the input, and the lesson on using the comma. Yes, the user usually only enters one item, maybe 3 out of 10 using multiple entries, and if the textboxes didn't have the same number of values in each one as Steve suggested then you are going to end up with blanks throwing things off as well... Sounds like it would be better to just have the userform have multiple TB's...

I appreciate the insight - thank you