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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[/TR]
[TR]
[TD]First[/TD]
[TD]MI[/TD]
[TD]Last[/TD]
[TD]Suffix[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]J.[/TD]
[TD]Boy[/TD]
[TD]Sr.[/TD]
[TD]Tommy J. Boy Sr.[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD][/TD]
[TD]Boy[/TD]
[TD]Sr.[/TD]
[TD]Tommy Boy Sr.[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD]J.[/TD]
[TD]Boy[/TD]
[TD][/TD]
[TD]Tommy J. Boy[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[TD][/TD]
[TD]Boy[/TD]
[TD][/TD]
[TD]Tommy Boy[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.
[TABLE="class: grid, width: 682"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]DP Start[/TD]
[TD]DP Next Pymt[/TD]
[TD]DP Amt[/TD]
[TD]DP Freq[/TD]
[TD]DP Paid[/TD]
[TD]DP Amt Paid[/TD]
[TD]DP Pymt Date[/TD]
[TD]DP Pymt Status[/TD]
[TD]DP Net Due[/TD]
[/TR]
[TR]
[TD="align: right"]9/4/2017[/TD]
[TD="align: right"]09/01/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Late[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]09/15/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.