Solved: Button Click and Loop problem

[petedw]

Hi Guys,

I have a form with 5 text boxes and 2 command buttons.
I have coding written so that when 1 of the command buttons is clicked, it draws out a table with 5 formfields and copies the info from the 5 text boxes into the formfields.

How do i get it so that the next time i click on the button, it does the same as above but names the formfields with a number 1 higher on the end.

I have iNum as an integer at the beginning of my code and
iNum = iNum + 1 at the end of my code, but im not sure how to loop it so that it works on each click of the command button

Hope you understand what i want.

Many Thanks

Pete

[xCav8r]

:hi:

I think you'll need to make a few functions for yourself. First, make one that determines if a name is already being used. Something like this:

Function FormFieldNameExists(FormFieldName As String) As Boolean
Dim MyFormField As FormField
FormFieldNameExists = False
For Each MyFormField In ActiveDocument.FormFields
If MyFormField.Name = FormFieldName Then
FormFieldNameExists = True
Exit Function
End If
Next
End Function


Then create another function that will generate a unique name. This is where you'll put the loop you're looking for. It will look something like this:

Function GetUniqueFormFieldName(FormFieldName As String) As String
Do While FormFieldNameExists(GetUniqueFormFieldName) = True
'do concatenation here to try for something unique
Loop
End Function

Here's an example from my library. This is part of a function I use to generate a unique file name.


Do While MyFileExists(GetUniqueFileName) = True

GetUniqueFileName = strPathWithoutExtension _
& intUniqueNumber & strExtension
intUniqueNumber = intUniqueNumber + 1

Loop

HTH

[TonyJollans]

It isn't usual to create pre-filled FormFields (especially based on a different mechanism of User input). Why not just put text in your Table?

[petedw]

Option Explicit

Private Sub NewTransactionButton_Click()
Dim iRows As Integer, iCols As Integer, iCells As Integer, iNum As Integer
Dim rng As Range, tbl As Table


iRows = 5 ' this table will always have five rows
iCols = 2 ' this table will always have two columns
iCells = iCols * iRows
iNum = 2

ActiveDocument.Unprotect


Set rng = ActiveDocument.Bookmarks("TransDropper").Range
rng.Collapse Direction:=wdCollapseEnd
ActiveDocument.Bookmarks("TransDropper").Select

Set tbl = Selection.Tables.Add(rng, iRows, iCols)
tbl.Select
With Selection
.ParagraphFormat.KeepTogether = True
.ParagraphFormat.KeepWithNext = True
.Bookmarks.Add ("bkTransTbl")
.Font.Hidden = False
.Font.Color = wdColorBlack
End With

tbl.Cell(1, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Date:"
tbl.Cell(1, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "TransDate" & iNum
.FormFields(1).TextInput.EditType Type:=wdDateText, Format:="dd-MM-yy"
.FormFields(1).TextInput.Width = 8
.Font.Bold = False
End With
ActiveDocument.FormFields("TransDate" & iNum).Result = DateBox.Text

tbl.Cell(2, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Description:"
tbl.Cell(2, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "Description" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdRegularText, Format:=""
.Font.Bold = False
End With
ActiveDocument.FormFields("Description" & iNum).Result = DescriptionBox.Text


tbl.Cell(3, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Money Out:"
tbl.Cell(3, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "MOut" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
End With
ActiveDocument.FormFields("MOut" & iNum).Result = MoneyOutBox.Text


tbl.Cell(4, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Money In:"
tbl.Cell(4, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "MIn" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
End With
ActiveDocument.FormFields("MIn" & iNum).Result = MoneyInBox.Text


tbl.Cell(5, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Balance:"
tbl.Cell(5, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "Bal" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
.FormFields(1).Enabled = False
End With
ActiveDocument.FormFields("Bal" & iNum).Result = BalanceBox.Text

Selection.MoveEnd Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=1, Count:=2
Selection.TypeText Text:="" & vbCrLf

Selection.Bookmarks.Add ("TransDropper")

iNum = iNum + 1


'Protects document and selects first cell for input
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True

If ActiveDocument.Bookmarks.Exists("TransDate") Then
ActiveDocument.Bookmarks("TransDate1").Select
End If

End Sub


Above is my code. I just need to know how to loop it so that everytime i click on NewTransactionButton, it draws out my new table with + 1 on every formfield name.
The reason im not using text is because im doing sums within the formfields too

Pete

PS I know this code is probably very long winded and could be much neater, but i am not as advanced as you guys

[petedw]

Is there a way of getting vba to store my iNum to make things easier.
A variable counter or something along those lines??

Please help!! : pray2:

Pete

[MOS MASTER]

Hi Pete, :yes

Normally you begin with getting the count of all formfields in the document and just add 1 to it along the way.

I usually do this in a loop but you're code is in blocks and I don't have the time right now to help you make it more efficient.

But this works fine over here in repetitions:
Private Sub NewTransactionButton_Click()
Dim iRows As Integer, iCols As Integer, iCells As Integer, iNum As Integer
Dim rng As Range, tbl As Table


iRows = 5 ' this table will always have five rows
iCols = 2 ' this table will always have two columns
iCells = iCols * iRows
iNum = ActiveDocument.FormFields.Count

If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect


Set rng = ActiveDocument.Bookmarks("TransDropper").Range
rng.Collapse Direction:=wdCollapseEnd
ActiveDocument.Bookmarks("TransDropper").Select

Set tbl = Selection.Tables.Add(rng, iRows, iCols)
tbl.Select
With Selection
.ParagraphFormat.KeepTogether = True
.ParagraphFormat.KeepWithNext = True
.Bookmarks.Add ("bkTransTbl")
.Font.Hidden = False
.Font.Color = wdColorBlack
End With

tbl.Cell(1, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Date:"
tbl.Cell(1, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "TransDate" & iNum
.FormFields(1).TextInput.EditType Type:=wdDateText, Format:="dd-MM-yy"
.FormFields(1).TextInput.Width = 8
.Font.Bold = False
End With
ActiveDocument.FormFields("TransDate" & iNum).Result = DateBox.Text

iNum = iNum + 1
tbl.Cell(2, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Description:"
tbl.Cell(2, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "Description" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdRegularText, Format:=""
.Font.Bold = False
End With
ActiveDocument.FormFields("Description" & iNum).Result = DescriptionBox.Text

iNum = iNum + 1
tbl.Cell(3, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Money Out:"
tbl.Cell(3, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "MOut" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
End With
ActiveDocument.FormFields("MOut" & iNum).Result = MoneyOutBox.Text

iNum = iNum + 1
tbl.Cell(4, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Money In:"
tbl.Cell(4, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "MIn" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
End With
ActiveDocument.FormFields("MIn" & iNum).Result = MoneyInBox.Text

iNum = iNum + 1
tbl.Cell(5, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Balance:"
tbl.Cell(5, 2).Select
With Selection
.TypeText Text:=" "
.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
.FormFields(1).Name = "Bal" & (iNum)
.FormFields(1).TextInput.EditType Type:=wdNumberText, Format:="0.00"
.Font.Bold = False
.FormFields(1).Enabled = False
End With
ActiveDocument.FormFields("Bal" & iNum).Result = BalanceBox.Text

Selection.MoveEnd Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=1, Count:=2
Selection.TypeText Text:="" & vbCrLf

Selection.Bookmarks.Add ("TransDropper")


'Protects document and selects first cell for input
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True

If ActiveDocument.Bookmarks.Exists("TransDate") Then
ActiveDocument.Bookmarks("TransDate1").Select
End If

End Sub


HTH, :whistle:

[petedw]

Thanks again Joost, that'll do me nicely!

[MOS MASTER]

You're welcome Pete! :beerchug:

[fumei]

This is heavily over designed.

[MOS MASTER]

This is heavily over designed.

Hi Gerry, :yes

So how would you go about it? :whistle:

[fumei]

1. Take a look at what are the stated requirements (needs).

I have coding written so that when 1 of the command buttons is clicked, it draws out a table with 5 formfields and copies the info from the 5 text boxes into the formfields.
A. strictly speaking, from the above description, there is NO need whatsoever to have the inserted text (from the 5 text boxes) to be inserted into formfields. Why do they have to go into formfields? Perhaps if there is a legitimate reason, then sure, there is a legitimate reason, However, as stated there is NO need to have formfields accept this information. No formfields, no need to update (advance) their name/number of them. Just make the table, and put the darn text into the cell.....hmmmmm, done. It is poor design to make an INPUT object (a formfield) a passive recepticle for text. Yes, sometime it makes sense to do so. However, again, there is nothing in the stated requirements for this.

B. Why is there a need for increments of formfield names? While not stated, is there a requirements for getting a return out of them? If so....return the Cell value instead.

C. The code starts off with setting a range object as bookmark, collapsing it then Selecting the same bookmark. This is non-intuitive to me. I can not see the point to this at all.

D. Neither the Range object nor the Table objects are released. They are SET but never released.

E. Current code:
tbl.Cell(3, 1).Select
Selection.Font.Bold = True
Selection.TypeText Text:="Money Out:"
there is no need to Select this.
With tbl.Cell(3, 1).Range
.Font.Bold = True
.Text:="Money Out:"
End With works fine, and does not use the resources of the Selection object.

F. ....well it sounds like I am being hyper critical. I do not intend to be critical at all really. So I will stop here. Hey, if it works, that is better than NOT working. I simply think it could work better, and there may (MAY) be a way to achieve the requirements easier.

B.

2. Take a look at Tony's comment.

I do not see the requirements for a fair chunk of the code.

[MOS MASTER]

Hi Gerry, :yes

I agree on your comments.

But like I said in my comments when I've posted that code I only had time to make the code work. I didn't have time to clean it up.

So yes the code could be a whole lot smaller and smarter. :whistle:

[fumei]

Yes we are all busy, and I apologize if I was seeming hypercritical. That was not my intention. I just wanted to point out that conceptually the code could be better. For example, the use of the cell value, without selecting it. Selecting is not needed, nor is there any advantage in doing so.

It is not your responsibity to "clean up" code.

[MOS MASTER]

Yes we are all busy, and I apologize if I was seeming hypercritical. That was not my intention. I just wanted to point out that conceptually the code could be better. For example, the use of the cell value, without selecting it. Selecting is not needed, nor is there any advantage in doing so.

Actually it was not hypercritical (Nice word btw :yes ) I thought they where good comments. And yes I wish I had the time to answer all question with a 100% attention!



It is not your responsibity to "clean up" code.

At times I do tend to forget that! :eek: :rofl: