Visual Basic coding problem

[Keelysmile]

Hi

I am so sorry to be an annoying newbie but I am having an absolute nightmare trying to create one form.

Is anyone able to identify what I am doing wrong please?



Private Sub UserForm_Initialize()
Me.Caption = "My Example Form"
Me.Label1.Caption = "Name"
Me.Label2.Caption = "Qualificaions"
Me.Label3.Caption = "Client name and address"
Me.Label4.Caption = "Address of Property"
Me.Label5.Caption = "Interest to be valued"
Me.Label6.Caption = "Purpose of Valuation"
Me.Label7.Caption = "Inspection Date"
Me.Label8.Caption = "RICS Valuation Standards"
Me.Label9.Caption = "Description of Report"
Me.Label10.Caption = "Fee"
Me.Confirm
Me.Cancel


End Sub




Public Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
Set oRng = .Bookmarks(strBMName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strBMName
End With
lbl_Exit:
Exit Sub
End Sub




Private Sub Confirm_Click()
'Hide the userform
Me.Hide
'Assign the values of the three text boxes to the three bookmarks
'Using the Function FillBM
FillBM "bm1", Me.TextBox1.Text
FillBM "bm2", Me.TextBox2.Text
FillBM "bm3", Me.TextBox3.Text
FillBM "bm4", Me.TextBox4.Text
FillBM "bm5", Me.TextBox5.Text
FillBM "bm6", Me.TextBox6.Text
FillBM "bm7", Me.TextBox7.Text
FillBM "bm8", Me.TextBox8.Text
FillBM "bm9", Me.TextBox9.Text
FillBM "bm10", Me.TextBox10.Text
'Unload the form
Unload Me
End Sub






Private Sub Cancel_Click()
Me.Hide
End Sub

Thank you for your help

[gmaxey]

Your form seems basically sound. I mean as long as you have the named controls in the form and the named bookmarks in the document it functions.

You don't really need the initialize event as you can apply the label and command button captions directly in the properties window. I would do it differently though i.g.,

1. Name the form e.g., frmDemo

2. Put the following code in a standard module:


Sub Demo()
Dim oFrm As frmDemo
Dim lngIndex As Long
Set oFrm = New frmDemo
oFrm.Show
If oFrm.Tag = "Run" Then
On Error Resume Next
For lngIndex = 1 To 10
FillBM "bm" & lngIndex, oFrm.Controls("Textbox" & lngIndex).Value
Next
On Error GoTo 0
End If
lbl_Exit:
Unload oFrm
Set oFrm = Nothing
Exit Sub
End Sub

Public Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
With ActiveDocument
Set oRng = .Bookmarks(strBMName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strBMName
End With
lbl_Exit:
Exit Sub
End Sub


3. Put the following code in the form module:


Option Explicit
Private Sub Confirm_Click()
Tag = "Run"
Hide
lbl_Exit:
Exit Sub
End Sub

Private Sub Cancel_Click()
Hide
lbl_Exit:
Exit Sub
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
Cancel_Click
End If
lbl_Exit:
Exit Sub
End Sub