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()
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,752
Office Version
  1. 365
Platform
  1. Windows
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
 

lizziegirl

New Member
Joined
Apr 3, 2006
Messages
31
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!
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

lizziegirl

New Member
Joined
Apr 3, 2006
Messages
31
:LOL: jindon - you are brilliant, thank you so much for that - it works a treat!
lizziegirl
 

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,576
Latest member
az168

This Week's Hot Topics

Top