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

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
480
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.

VBA Code:
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....
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 18, 2008
Messages
480
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 18, 2008
Messages
480

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
I see some kind of loop like

VBA Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Feb 18, 2008
Messages
480
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,564
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top