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