Eliminate blank row at top of Text Box entries

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
 
G

GS

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
 
H

Howard

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
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dim IndxNum As Long & Dim c As Variant 7
Active XText box vbcr shows symbol instead of an actual carraige return 2
Concat a three column range into one cell Then Next... 2
Convert list to UPPER, lower & Proper cases. 21
InputBox entries & advice 16
Help with loop 4
Eliminate blanks while copy row and transpose 11
Fill blanks with find results 4

Top