Search code examples
vbaexceluserform

Avoid Extra White Space VBA


I have a userform that enters data into another userform, with data being entered on a new line for each submission. I am running into a problem where the 1st entered data skips a line on my userform. How can I adjust my code to avoid having the extra white line in the beginning, here is my code:

Private Sub CommandButton1_Click()
 opsvision.opsfinding.Value = opsvision.opsfinding.Value & vbNewLine & "Employees" & "---" & generalbuilder.employees.Value & " -" & Space(2) & Space(1) & """" & Me.findings.Value & """" & Space(5) & "----" & Space(3) & "Finding Conducted by: " & Worksheets("userform").Range("B3") & vbNewLine

Unload Me
End Sub

The red line shows the extra white space at the top of the text box The red line shows the extra white space at the top of the text box


Solution

  • Test to see if it's empty first. Otherwise you concatenate a vbNewLine to the empty starting value:

    Private Sub CommandButton1_Click()
        Dim line As String
    
        line = "Employees" & "---" & generalbuilder.employees.Value & " -" & Space(2) & _
            Space(1) & """" & Me.findings.Value & """" & Space(5) & "----" & Space(3) & _
            "Finding Conducted by: " & Worksheets("userform").Range("B3") & vbNewLine
    
        If opsvision.opsfinding.Value = vbNullString Then
            opsvision.opsfinding.Value = line
        Else
            opsvision.opsfinding.Value = opsvision.opsfinding.Value & vbNewLine & line
        End If
    
        Unload Me
    End Sub