User Form Problem

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
I run a code that extracts some text from a database.

I have two problems.
1. the line break character displays as a Carriage Return symbol rather than a line break.
2. [SOLVED]<solved<solved>The actual information displayed is always one step behind.
By this I mean, I run it for part A it displays nothing.
Then run for part B it shows part A
then run is for part C it shows part B
then run is for part A it shows part C</solved<solved>


Code:
Sub get_tech()
Dim prod As Variant
Dim tech_desc As String
If Intersect(ActiveCell, Range("A4:AN5600")) Is Nothing Then
    MsgBox "Please select a product line", vbInformation
    Exit Sub
End If
Set prod = Cells(ActiveCell.Row, "A")
Sheets("Tech").Range("A1").Value = prod
Sheets("tech").Calculate
tech_desc = Sheets("Tech").Range("A1").Value


If Sheets("Tech").Range("N3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("N3").Value
End If
If Sheets("Tech").Range("B3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("B3").Value
End If
If Sheets("Tech").Range("C3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("C3").Value
End If
If Sheets("Tech").Range("D3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("D3").Value
End If
If Sheets("Tech").Range("E3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("E3").Value
End If
If Sheets("Tech").Range("F3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("F3").Value
End If
If Sheets("Tech").Range("G3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("G3").Value
End If
If Sheets("Tech").Range("H3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("H3").Value
End If
If Sheets("Tech").Range("I3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("I3").Value
End If
If Sheets("Tech").Range("J3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("J3").Value
End If
If Sheets("Tech").Range("K3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("K3").Value
End If
If Sheets("Tech").Range("L3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("L3").Value
End If
If Sheets("Tech").Range("M3").Value <> "" Then
    tech_desc = tech_desc & vbNewLine & Sheets("Tech").Range("M3").Value
End If


UF1.Show False
UF1.TB1.Value = tech_desc
UF1.Repaint


End Sub


any suggestions welcome
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
Problem 2 resolved, I added a manual query refresh rather than allowing the data to refresh on cell change :)

Still struggling with the & vbNewLine & displaying a character rather than a new line
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,455
Office Version
  1. 365
Platform
  1. Windows
Is your textbox set as multiline=true
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,455
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,402
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top