Concatenation Question

[reberryjr]

I have what should be a simple task. I'm trying to concatenate some cells to complete a name. I didn't have an issue before I added the middle initial to the formula, but now I'm struggling. I've searched the forum, but only see concatenation examples for First and Last names, not anything with 4 parameters that accounts for missing data (MI and/or Suffix).

The basic layout is below, with expected results. Here's the formula that I'm using, that is not working:

=IF(G3="",IF(E3="",D3&" "&F3,IF(G3="",D3&" "&E3&" "&F3,IF(E3="",D3&" "&F3&" "&G3,D3&" "&E3&" "&F3&" "&G3))))

Column D	Column E	Column F	Column G	Column H
First	MI	Last	Suffix	Name
Tommy	J.	Boy	Sr.	Tommy J. Boy Sr.
Tommy		Boy	Sr.	Tommy Boy Sr.
Tommy	J.	Boy		Tommy J. Boy
Tommy		Boy		Tommy Boy

<tbody>
</tbody>

 

[reberryjr]

hmmm....here's where the code lies within the userform data entry piece. I wonder (and am looking it up), if instead of clicking on the txt box, can the formula be invoked when the user tabs to the field? It would be natural progression as they work through the form.

 

[Fluff]

Like this

Private Sub txt_Name_Enter()
Me.txt_Name.Value = Trim(Me.txt_First.Value & Me.txt_MI.Value & Me.txt_Last.Value & Me.txt_Suff.Value)

End Sub

But you may want to lock the txtbox to prevent it from being changed

 

[reberryjr]

Ok, so I implemented that sub and it's concatenating the text fields, but it's not adding any spaces. I changed the code to this, and it's still omitting the spaces. I also tried it without the TRIM and it's still omitting the spaces. What I'm seeing is TommyJ.BoySr. instead of Tommy J. Boy Sr.

Me.txt_Name = Trim(Me.txt_First.Value & " " & Me.txt_MI.Value & " " & Me.txt_Last.Value & " " & Me.txt_Suff.Value)

 

[Fluff]

I'm the exact opposite! Its adding all the spaces.
It looks as though the VBA function Trim & the Excel function work differently.
Whilst this worked for me

Private Sub txt_Name_Enter()
Me.txt_Name = WorksheetFunction.Trim(Me.txt_First.Value & " " & Me.txt_MI.Value & " " & Me.txt_Last.Value & " " & Me.txt_Suff.Value)
End Sub

I'm not sure what to advise, if you're not getting any spaces.

 

[reberryjr]

Now we're cooking with crisco! Thank you so much!!! Especially for not making me feel like an idiot for asking what are probably simple questions.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[reberryjr]

Hopefully you don't mind, but I have another question that's been plaguing me for a few weeks. I have 5 sets of similar elements as the ones below; that represent different services offered to the Client. What I'm trying to do is to have VBA identify when it's time to insert the next due date (shown in row3). Right now, I have it calculated off the value in the DP Pymt Status column, but that's not through VBA.

A few issues that complicate this for me are:
1. A Client can sign up for, or end services at any time.
2. Not all payment frequencies are the same (Monthly, Weekly, Bi-Weekly).
3. I don't want to get more than 1 payment into the future, as it would really clutter the spreadsheet up.
4. I will have to be able to navigate to the next payment due via a userform. I haven't really gotten far enough into this to think how I would do that.
5. I anticipate having 50+ sheets (that can grow), one for each Client, so I'm hoping it's some sort of code that can be entered in one module, without having to name each sheet.

DP Start	DP Next Pymt	DP Amt	DP Freq	DP Paid	DP Amt Paid	DP Pymt Date	DP Pymt Status	DP Net Due
9/4/2017	09/01/17	$200.00	B				Late
	09/15/17

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I'm not asking for the code to solve this, but instead, recommendations on how I best structure this to solve for it on my own.

 

[Fluff]

Is this a continuation of this thread?
https://www.mrexcel.com/forum/excel...insert-row-predictive-data-2.html#post4973323

 

[reberryjr]

Sort of. I was able to make a little progress on it since that post. Was hoping you might have some insight.

 

[Fluff]

I'll have a look at it tomorrow, but everything needs to remain on that thread.