Consulting

Results 1 to 2 of 2

Thread: Visual Basic coding problem

  1. #1

    Visual Basic coding problem

    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

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,888
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •