How to count in VBA?

[call_me_sol]

Hi -
I'm creating a userform with various textboxes, docvariables, and
listboxes. The problem I am now faced with is two fold:

Problem 1) I need a way to count count the data entered into the
textbox and the result of the count will perform a specific action.
For example: user enters 10 lines of text (each ended with a carriage
return) the resulted output would provide the word "apple". If a user
enters 100 lines of text the resulted output would provide the word
"orange". Basically, I don't know how to make the userform and
textbox smart enough to count.

Problem 2) With the data that's entered into the textbox, I need to
format it, and return it to the document. Ordinarily, I would do this
with docvariables. But, since I don't know how many lines a user is
going to enter, I don't know how many docvariables to build into the
document. So, what I need, is no matter how many lines the user
enters, I need the code to output the lines reformatted the proper
way. For example:

User enters:
1.1.1.0/24
2.2.0.0/16

Form ouputs:
ip prefix list AS2345 permit ip 1.1.1.0/24
ip prefix list AS2345 permit ip 2.2.0.0/16

As always, thanks for the help!

 

[Greg Maxey]

Something like this perhaps:

Private Sub CommandButton1_Click()
Dim myArray
Dim lngCount As Long
Dim i As Long
Dim pString As String
myArray = Split(Me.TextBox1.Text, Chr(13))
lngCount = UBound(myArray) + 1
Select Case lngCount
Case 2
MsgBox "Apple"
Case 4
MsgBox "Orange"
Case Else
MsgBox "Whatever"
End Select
For i = 0 To UBound(myArray)
If Left(myArray(i), 1) = Chr(10) Then
pString = Right(myArray(i), Len(myArray(i)) - 1)
Else
pString = myArray(i)
End If
ActiveDocument.Range.InsertAfter "ip prefix list AS2345 permit ip "
& pString & vbCr
Next i
Me.Hide
End Sub

 

[call_me_sol]

Hi Greg -

thanks again for your help. It's not quite working the way I need it
to - -but close. Let me explain:

1) I need the ability to enter carriage returns in the text box.
Users can enter their data (usually copied from elsewhere) as:
1.1.1.1
2.2.2.2
3.3.3.3

2) I really didn't need message boxes (i don't think i explained that
earlier -- sorry) so I adjusted your code a bit:
Dim pString As String
myArray = Split(Me.TextBox5.Text, Chr(13))
lngCount = UBound(myArray)
Select Case lngCount
Case 1
ActiveDocument.Range.InsertAfter "ip prefix list AS2345 permit ip "
& pString & vbCr

3) I can't make the number of lines work properly. i.e.:
If there are less than 10 lines -- the userform will return with: "ip
prefix maximum 100"
If there are more than 11 lines but less than 100 lines -- the
userform will return with: "ip prefix list maximum 1000"
if there are more than 101 lines but less than 1000 -- the userform
will return with "ip prefix list maximum 10000"

In the end, the code will eventually look like:

ip prefix list AS2345 permit ip 1.1.1.1
ip prefix list AS2345 permit ip 2.2.2.2
ip prefix list AS2345 permit ip 3.3.3.3
ip prefix maximum 100 <-- (Because there were only three lines
entered)


Thank you again!

 

[Greg Maxey]

Try:
Private Sub CommandButton1_Click()
Dim myArray
Dim lngCount As Long
Dim i As Long
Dim pString As String
Dim pString2
myArray = Split(Me.TextBox1.Text, Chr(13))
lngCount = UBound(myArray)
Select Case lngCount
Case Is < 10
pString2 = "ip prefix maximum 100"
Case Is > 11
Select Case lngCount
Case Is < 100
pString2 = "ip prefix list maximum 1000"
Case Is > 100
pString2 = "ip prefix list maximum 10000"
Case Else
End Select
Case Else
pString = "Whatever"
End Select
For i = 0 To UBound(myArray) - 1
If Left(myArray(i), 1) = Chr(10) Then
pString = Right(myArray(i), Len(myArray(i)) - 1)
Else
pString = myArray(i)
End If
ActiveDocument.Range.InsertAfter "ip prefix list AS2345 permit ip "
& pString & vbCr
Next i
ActiveDocument.Range.InsertAfter pString2
Me.Hide
End Sub

 

[call_me_sol]

Sorry -- it's still not working. Good think I'm posting in the
"beginner" forum, eh? I'm trying to learn how to do this because I
have to make several of these types of userforms, so once I learn this
one, I should be able to do the rest. So, here's what's still broken:

1) For some reason, in your newest revision of code, the text I'm
entering into textbox1 isn't appearing on the form after "ip prefix
list AS2345 permit ip" as it should. I tried adjusting the code to
make it work, but it simply doesn't.

2) I still cannot enter carriage returns in textbox1.


Thanks Greg!

 

[Greg Maxey]

You said the text would be pasted into the textbox. You can't entry
carriage returns in a textbox you can enter line breaks using shift+enter.
But those are Chr(10) like in my first code.

 

[Jonathan West]

You said the text would be pasted into the textbox. You can't entry
carriage returns in a textbox

Actually you can. Set the MultiLine and EnterKeyBehavior properties of the
textbox both to True


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Greg Maxey]

Thanks Jonathan. I hope I stop learning before you stop teaching.

With the properties set as Jonathan indicates, try:
Private Sub CommandButton1_Click()
Dim myArray
Dim lngCount As Long
Dim i As Long
Dim pString As String
Dim pString2
myArray = Split(Me.TextBox1.Text, Chr(13))
lngCount = UBound(myArray) + 1
Select Case lngCount
Case Is < 10
pString2 = "ip prefix maximum 100"
Case Is > 11
Select Case lngCount
Case Is < 100
pString2 = "ip prefix list maximum 1000"
Case Is > 100
pString2 = "ip prefix list maximum 10000"
Case Else
End Select
Case Else
pString = "Whatever"
End Select
For i = 0 To UBound(myArray)
pString = myArray(i)
ActiveDocument.Range.InsertAfter "ip prefix list AS2345 permit ip "
& pString & vbCr
Next i
ActiveDocument.Range.InsertAfter pString2
Me.Hide
End Sub

 

[call_me_sol]

Hi Greg, et.al, -

Its working now except for one last thing. It appears as if an extra
carriage return is being inserted in the wrong place:

Resulting in:
ip prefix list AS2345 permit ip 1.2.3.4
ip prefix list AS2345 permit ip
2.4.5.6
ip prefix maximum 100

It should be:
ip prefix list AS2345 permit ip 1.2.3.4
ip prefix list AS2345 permit ip 2.4.5.6
ip prefix maximum 100

Any suggestions to fix?

 

[Russ]

When you toggle on 'show/hide paragraph marks and spaces' with the backwards
P icon in your toolbar do actually see a paragraph mark where the line
splits. If not, then maybe your line is wrapping automatically when words
come to close to the end of a line.

 

[Tony Strazzeri]

You can simplifyt the case statements. You don't need to nest them in
order to test the ranges.
as shown in the example below
Case 1 To 10
pString2 = "value between 1 & 10 inclusive"
Case 11 To 100
pString2 = "value between 11 & 100 inclusive"
Case Is > 100
pString2 = "value greater than 100"
End select

Cheers
TonyS.