Eliminate blank row at top of Text Box entries

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

This code works just fine, (notice Claus in the sub name, thank you).
It does produce a blank row at the top of any entries in the text box.
To me, that is just fine, in fact I prefer that over no top margin.

However, the end user wants it gone.
I've tried some variations of this line along with a parse of the text box properties and cannot find a way to just eliminate the blank row. I was able to eliminate it with one revamp of the code line but it then produced double entries of each entry.

I need to keep the current entries already in the box and add to them with additional entries, which this line just below here does. But with an empty box the first entry needs to go to the very top of the box.

Regards,
Howard


ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable


Option Explicit

Sub TheT_Box_Claus()
Dim MyStringVariable As String '
Dim IndexCol As Range '
Dim IndexLibry As Range '
Dim c As Range '
Dim rngC As Range '
Dim strIn As String '
Dim varOut As Variant '
Dim myCount As Integer '

Set IndexCol = Sheets("Input").Range("A2:A100")
Set IndexLibry = Sheets("Sort").Range("A3:A25")

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
IndexLibry.ClearContents

If myCount = 0 Then
[A3] = strIn
Else
varOut = Split(strIn, ",")
Cells(3, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If

For Each c In IndexLibry
Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)
If Not rngC Is Nothing And rngC <> "" Then

'*****/// add additional columns here ///******
' Lets just call this an add-column unit: & ", " & rngC.Offset(0, 10)
' the "&" appends the code to existing code.
' the ", " adds a comma between the column entries.
' then you need another "&"
' and this defines the column you want to add: rngC.Offset(0, 10)
' so the add-column shown above would add column K, _
' 10 columns to the right of the IndexCol
' the one with the header "G(#F,#R)"
' and you would insert it in the code to suit the order of _
' apperance you want in the text box

MyStringVariable = "(" & rngC & ") " & rngC.Offset(0, 4) _
& ", " & rngC.Offset(0, 1) & ", " & rngC.Offset(0, 2) _
& ", " & rngC.Offset(0, 14) & ", " & rngC.Offset(0, 15) _
& ", " & rngC.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If
Next

End Sub
 
This code works just fine, (notice Claus in the sub name, thank you).
It does produce a blank row at the top of any entries in the text
box. To me, that is just fine, in fact I prefer that over no top
margin.

However, the end user wants it gone.
I've tried some variations of this line along with a parse of the
text box properties and cannot find a way to just eliminate the blank
row. I was able to eliminate it with one revamp of the code line but
it then produced double entries of each entry.

I need to keep the current entries already in the box and add to them
with additional entries, which this line just below here does. But
with an empty box the first entry needs to go to the very top of the
box.

Regards,
Howard


ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable


Option Explicit

Sub TheT_Box_Claus()
Dim MyStringVariable As String '
Dim IndexCol As Range '
Dim IndexLibry As Range '
Dim c As Range '
Dim rngC As Range '
Dim strIn As String '
Dim varOut As Variant '
Dim myCount As Integer '

Set IndexCol = Sheets("Input").Range("A2:A100")
Set IndexLibry = Sheets("Sort").Range("A3:A25")

strIn = Application.InputBox("Enter one number or " _
& "more numbers comma delimited", Type:=3)
myCount = Len(strIn) - _
Len(WorksheetFunction.Substitute(strIn, ",", ""))
IndexLibry.ClearContents

If myCount = 0 Then
[A3] = strIn
Else
varOut = Split(strIn, ",")
Cells(3, 1).Resize(myCount + 1, 1) = _
WorksheetFunction.Transpose(varOut)
End If

For Each c In IndexLibry
Set rngC = IndexCol.Find(c, LookIn:=xlValues, lookat:=xlWhole)
If Not rngC Is Nothing And rngC <> "" Then

'*****/// add additional columns here ///******
' Lets just call this an add-column unit: & ", " & rngC.Offset(0,
10) ' the "&" appends the code to existing code.
' the ", " adds a comma between the column entries.
' then you need another "&"
' and this defines the column you want to add: rngC.Offset(0, 10)
' so the add-column shown above would add column K, _
' 10 columns to the right of the IndexCol
' the one with the header "G(#F,#R)"
' and you would insert it in the code to suit the order of _
' apperance you want in the text box

MyStringVariable = "(" & rngC & ") " & rngC.Offset(0, 4) _
& ", " & rngC.Offset(0, 1) & ", " & rngC.Offset(0, 2) _
& ", " & rngC.Offset(0, 14) & ", " & rngC.Offset(0, 15) _
& ", " & rngC.Offset(0, 18)
ActiveSheet.TextBox1.Text = ActiveSheet.TextBox1.Text _
& vbCr & MyStringVariable
End If
Next

End Sub
Click to expand...

What this tells me is that if the textbox is empty then it gets a vbCr
before the 1st line. Otherwise, if text exists then each new block of
text is separated from existing text by a vbCr. You can eliminate a
blank 1st row by...

With ActiveSheet.TextBox1
If Len(.Text) > 0 Then MyStringVariable = vbCr & MyStringVariable
.Text = .Text & MyStringVariable
End With

...where you prepend the vbCr to MyStringVariable only when there's
existing text so the 1st block of text starts at the top when the
textbox is empty.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
What this tells me is that if the textbox is empty then it gets a vbCr

before the 1st line. Otherwise, if text exists then each new block of

text is separated from existing text by a vbCr. You can eliminate a

blank 1st row by...



With ActiveSheet.TextBox1

If Len(.Text) > 0 Then MyStringVariable = vbCr & MyStringVariable

.Text = .Text & MyStringVariable

End With



..where you prepend the vbCr to MyStringVariable only when there's

existing text so the 1st block of text starts at the top when the

textbox is empty.
Click to expand...


CAUTION: Genius at work!

Thanks, Garry. Your analysis was correct and the solution is perfect.

Of course I will keep the code that produces the blank row also.
So someone can have it either way.

Regards,
Howard
 
What this tells me is that if the textbox is empty then it gets a
CAUTION: Genius at work!

Thanks, Garry. Your analysis was correct and the solution is
perfect.

Of course I will keep the code that produces the blank row also.
So someone can have it either way.

Regards,
Howard
Click to expand...

You're welcome! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top