Dynamic userform

[Katherine]

I've got a pretty basic userform with 3 textboxes and a combobox which works ok as is. On clicking submit the entered details are entered into bookmarks in the document.

What i would like to be able to do is set the form so that on clicking submit the details would be inserted into the document and the form would reset itself ready to enter the next set of details.

Doing this isnt a problem, but i need the second set of details entered into the form to be inserted into the 2nd set of bookmarks in the document, same with the third set and so on.

Any clues on how to alter the code to take this into account?

Sub userform_initialize()
txtLocation.SetFocus
cboStatus.List = Array("For Sale", "For Rent")
End Sub

Private Sub cmdSubmit_Click()
With ActiveDocument
.Bookmarks(bmkPrice).Range _
.Text = txtPrice.Value
.Bookmarks("bmkLocation").Range _
.InsertBefore txtLocation
.Bookmarks("bmkAdtext").Range _
.InsertBefore txtAdtext
.Bookmarks("bmkStatus").Range _
.InsertBefore cboStatus
End With
frmEnterDetails.Hide
End Sub



Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Long
On Error GoTo Invalid
x = CLng(txtPrice1.Text)
Exit Sub
Invalid:
MsgBox "Price must be a number. Please don't use any pound signs(£) or other punctuation"
Cancel = True
End Sub

 

[Jay Freedman]

Hi Katherine,

For starters, the names of the bookmarks in the document need to be
different in each set -- any given bookmark name can appear only once per
document. I'd suggest appending the number of the set to each name, so
they'd be "bmkPrice1", "bmkLocation1", etc.,..."bmkPrice2", "bmkLocation2",
etc.,...

In the userform code, declare an integer variable to hold the current set
number, and append that to each base bookmark name:

' in general declarations:
Dim nSet As Integer

' in Userform_Initialize():
nSet = 1

' in cmdSubmit_Click():
...
.Bookmarks("bmkPrice" & nSet).Range _
.Text = txtPrice.Value
.Bookmarks("bmkLocation" & nSet).Range _
.InsertBefore txtLocation

...
' clear the textboxes, then
nSet = nSet + 1

You'll also need some error-prevention code to check that bookmarks with the
current number actually exist in the document.