Returning only one word from the textbox as a value

[lizziegirl]

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.

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

 

[agihcam]

try;

ThisWorkbook.Sheets("Order").Range("d13").Value = Order_Details.TextBox1.Value()
a = Split(Order_Details.TextBox1.Text, " ")
ThisWorkbook.Sheets("Order").Range("b43").Value = a(0)

 

[sykes]

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:

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

could be written:

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

...and

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:

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

 

[lizziegirl]

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..

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!

 

[agihcam]

sorry. but could not duplicate the error you're having.

 

[jindon]

Hi

Aren't you on xl '97?
If so, try

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

 

[lizziegirl]

jindon - you are brilliant, thank you so much for that - it works a treat!
lizziegirl