VBA Code vbNewLine Issue

[Tashat]

Hi All - I am not able to write my own VBA code without support and I have found some code on another site which works almost perfectly, but I need some advice on a tweak, but I don't know the syntax. Am I allowed to post the code here and credit the person who wrote it, but just ask for some support on changing it slightly?

It is code which allows a data validation drop-down box to accept multiple entries and puts each entry on a new line within the cell (example below without code). My issue is that the last entry in each cell automatically has a new line (vbNewLine) appended to it, making it "different" from an entry which appears at the top of the list, or mid-list when I produce a power query based on the data. I've included an example below (without the code) to illustrate my point. For example "Strawberries" in cells A2 and A5 are the "same", but in cell A4 it is "Strawberries" with a line break after, so therefore is "viewed" differently in my PowerQuery. Am I allowed to post the code in here to ask for help with changing it so the last entry in each cell doesn't have a line-break (vbNewLine) associated with it?

Book1
	A	B
1	List	Shop
2	Strawberries Apples Oranges	Sainsburys
3	Apples Pears	Morrisons
4	Blueberries Oranges Strawberries	Tesco
5	Oranges Apples Strawberries Blueberries	Asda
Sheet1

 

[Peter_SSs]

Am I allowed to post the code here and credit the person who wrote it,

Provided that does not breach the term and conditions/rules of the other site, "yes".
However, if the code comes from another site, why not ask there?

 

[Tashat]

Hi Peter_SSs - Thank you for your reply. I would ask on the other site, but no one responds to comments anymore from the looks of it. I am allowed to share it as long as I credit the author in the code, which I have.

The code is below and I just want to prevent the last entry in each of my cells having a new line appended to it. Any help would be much appreciated. I'm learning so much from this site.

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 7 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[johnnyL]

I can't duplicate your problem. can you post a link to your workbook?

 

[Tashat]

I can't duplicate your problem. can you post a link to your workbook?

Hi there

Here is an example to illustrate. The issue is that for example "Pears" in cell A2 is captured as "Pears (with a line break)" and "Pears" in cell A3 is just "Pears". So when I run a PowerQuery to allow me to extract the different fruits as individual lines, it "sees" "Pears" differently - because whichever fruit appears as the last entry in each cell has a line-break appended to it. So I want to edit the VBA so that it stops the last entry in each cell having a line break. Is that possible, bearing in mind that cells could have any number of entries?
Thanks in advance

Fruit Example.xlsm
	A	B	C
1	List	Shop
2	Strawberries Apples Pears	Sainsburys
3	Apples Pears Blueberries	Morrisons
4	Blueberries Oranges	Asda
5	Oranges Pears	Tesco
6	Pears Strawberries	Sainsburys
7
8
9
10
11
12
13
14
15			Strawberries
16			Apples
17			Oranges
18			Apples
19			Pears
20			Blueberries
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$C$15:$C$20

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 1 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

Starting with a blank cell in A2 with that code in place and Data Validation as shown ...
Pick Strawberries from the drop-down list
then pick Apples from the drop-down list
then pick Pears from the drop-down list
.. does not produce a line-break (or any other character) after the word Pears for me.

I have checked both by analysing the results and also stepping through the code a line at a time and it produces no character at all after the 's' at the end of pears.

If you are getting another character then I suspect there must be other code in the workbook somehow producing that. What evidence is there that there is a line-feed at the end?

What do you get returned if you put this formula in a blank cell?

=CODE(RIGHT(A2,1))

 

[Fluff]

it "sees" "Pears" differently - because whichever fruit appears as the last entry in each cell has a line-break appended to it.

I suspect that the problem is the last value does not have a line feed after it, whereas all the other values will.

 

[Tashat]

Yes perhaps I'm wrong about it adding a line break after the last entry. What I do know is that I have pulled a PowerQuery to extract each of the fruits into separate rows and then it repeats the supermarket name, that way I can pull a pivot table from that data to group all oranges together and then which supermarkets they apply to, all pears, all apples etc. However, the pivot table repeats oranges twice and I believe it's because there is something (not visibly) different about them and I believe it is dependent on where in the list the user has put them - i.e. at the top/middle or at the end of the list. I hope that makes sense.

 

[Fluff]

I believe it's because there is something (not visibly) different about them

There is, the last value in the cell does not have a line feed after, whereas all the others do.
I suspect the problem is with your Power Query & not the data validation. So I would suggest you start a thread in the Power BI section & post the code you are using for your PQ.

 

[Tashat]

There is, the last value in the cell does not have a line feed after, whereas all the others do.
I suspect the problem is with your Power Query & not the data validation. So I would suggest you start a thread in the Power BI section & post the code you are using for your PQ.

Amazing - thank you so much for helping me unpick this. I will post that soon. Thank you again.