Returning only one word from the textbox as a value

lizziegirl

New Member
Joined
Apr 3, 2006
Messages
31
Afternoon,

I was hoping someone could help me regarding a textbox I am trying to manipulate.

This textbox has a default value of John Smith. The user can either accept this as is or amend to another name.

On CommandButton execution, I want John Smith (or whatever the user has entered) to go to one cell and then only the word John to go to another cell.

Is this possible? The code below takes John Smith to d13, however I want only the first word (in this case the first name John) to go to b43 also.

Code:
ThisWorkbook.Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value()
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
try;
Code:
ThisWorkbook.Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value()
a = Split(Order_Details.TextBox1.Text, " ")
ThisWorkbook.Sheets("Order").Range("b43").Value = a(0)
 
Upvote 0
Lizziegirl
Just out of interest..... I was thinking, you could also look to optimise your code a little here if you were intereted in keeping it shorter.....
As long as you're just working within the one workbook, you shouldn't need to keep referring to "ThisWorkbook", neither should you need the parentheses after your "TextBox1.value", so that:
Code:
ThisWorkbook.Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value()
could be written:
Code:
Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value
...and
Code:
ThisWorkbook.Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value()
a = Split(Order_Details.TextBox1.Text, " ")
ThisWorkbook.Sheets("Order").Range("b43").Value = a(0)
.....should work fine written as:
Code:
Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value
a = Split(Order_Details.TextBox1.Text, " ")
Sheets("Order").Range("b43").Value = a(0)

I'm not decrying Agihcam's fine answer at all, neither do I wish to put you off, I only mention this because with the help of this board I've learnt a huge amount about VBA and it's possibilities, and as my projects get larger, I'm starting to find that when you come to re-visit some code a few years on, it's much easier to understand it, and therefore remember what does what if:
a) you keep it as short as possible, and
b) you put explanatory lines in where possible to explain what the code does, line by line if necessary. If you've not come accross this yet, it's called "commenting out" - any line that begins with the apostrophe symbol will not be read as code - you can therefore type in anything you like after the apostrophe to help you remember what the next line (or series of lines) is about to do.
Very sorry if you know this already, it's just that it was ages before I knew about this, and would have been sooooooo handy to have known earlier on........... especially when you're "testing" many lines, and want to try the code without running one of the lines within it - you just comment that line out, try the code, and if you want to re-instate that line again, just uncomment it (take the apostrophe away again.)

All the best
 
Upvote 0
agihcam and sykes,

Thanks very much for your responses - they are very much appreciated. The problem is the code does not seem to be working for me?

I get the following error code?

Run-time error '-2147352571 (800200005)':
Could not set the Value Property. Type mismatch.

at the following line..

Code:
A = Split(Order_Details.TextBox1.Text, " ")

I did copy and paste as is - I hope I am not being useless and was meant to have tailored it to suit me in anyway.

sykes, I was aware of the comment blocks, thanks for your advice....I am definitely still in the learning phase, having taught myself most of what I know. This site has been amazing - I wish I had half the knowledge some of you guys have. I am still to get my head around the language - and do not find VB HELP very useful at all!
 
Upvote 0
Hi

Aren't you on xl '97?
If so, try
Code:
Dim txt As String
txt = Order_Details.TextBox1.Text
With Thisworkbook.Sheets("Order")
      .Range("d13").Value = txt
      .Range("d43").Value = Trim(Left(txt, InStr(txt,Chr(32))))
End With
Else add Dim A As Variant to your current code
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top