PDA

View Full Version : Solved: Assistance with character counting (Excel '97)



phendrena
03-16-2009, 07:42 AM
Hi there,

i'm using the following to count the amount of characters entered into a field.
Private Sub txtComments_Change()
Dim tCount
tCount = Len(Me.txtComments.Value)
Me.txtCount.Value = tCount
End Sub
Using the above it counts line breaks as 2 characters.
How can I modify the above code to either count them as 1 or ignore them completely?

Thanks,

Bob Phillips
03-16-2009, 08:02 AM
How are you inserting linebreaks in the textbox?

phendrena
03-16-2009, 08:26 AM
How are you inserting linebreaks in the textbox?Yes, when the user presses the [Enter] key
BTW, is your avatar coming back?

Bob Phillips
03-16-2009, 09:05 AM
Private Sub txtComments_Change()
Dim tCount
tCount = Len(Replace(Me.txtComments.Value, vbCrLf, ""))
Me.txtCount.Value = tCount
End Sub

Bob Phillips
03-16-2009, 09:14 AM
BTW, is your avatar coming back?

Unlikely, it was lost when VBAX was down recently, and I did not create it, so I don't have a copy.

phendrena
03-16-2009, 09:29 AM
Private Sub txtComments_Change()
Dim tCount
tCount = Len(Replace(Me.txtComments.Value, vbCrLf, ""))
Me.txtCount.Value = tCount
End Sub
Errors with "sub or function not defined" and highlights Replace.

Is replace not supported in '97?

Thanks,

Bob Phillips
03-16-2009, 09:33 AM
No, actually it isn't.

Put this code in a standard code module.



'-----------------------------------------------------------------
Function Replace(expression As String, _
find_string As String, _
replacement As String)
'-----------------------------------------------------------------
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String


sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen - i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replace = sTemp

End Function

phendrena
03-16-2009, 10:42 AM
thanks. I'll have a crack tomorrow as i'm currently heading home on the bus and as much as i'd like it I don't have a VBA compatible office program - ms office mobile is pants. I do have softoffice which is much better but lacks a vba environment :(

phendrena
03-16-2009, 10:45 AM
Unlikely, it was lost when VBAX was down recently, and I did not create it, so I don't have a copy.
Marble effect with embossed roman style typeface if I remember? i'll see what I can create although i'm no artist :)

phendrena
03-17-2009, 01:17 AM
Thanks xld, that does work quite nicely now.

On a side note, i've created a basic 'xld' avatar.
It'll need resizing to meet the board requirements but i thought you might want to see it larger first. As i said though i'm no artist.