Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-08-2015, 07:04 PM
Niclasfa Niclasfa is offline UserForm VBA coding Windows 8 UserForm VBA coding Office 2013
Novice
UserForm VBA coding
 
Join Date: Apr 2015
Posts: 13
Niclasfa is on a distinguished road
Default UserForm VBA coding

Ok so I have this problem that I have to find a solution for.



Basically the boss has asked me to create a User Form based on a Word 2013 template that pops up every time someone opens it.

My problem is this....
Ok, so I have created the user form based on a Word 2013 template. The user form has data entry areas. I have also created three buttons on the bottom of the form (OK, LOG, CLEAR)

I haven't created any of the macros yet because I don't really know how the VBA language works.

I have come up and with names for the macros but have not yet assigned any and I am not sure exactly how I go about doing that.

1. mcrOk:- needs to populate the bookmarks created in the template (I have attached the button but not the code to link it)

2. mcrLog:- I need this macro to append person data to the text file Latelog.txt and hide the UserForm. Obviously this will be attached to the button with the caption Log

3. mcrClose:- This macro needs to close the form and it will be attached to the user form button close

I hope this is helpful to anyone with experience in this field.

Thanks in advance for any positive feedback and or suggestions.

Last edited by Niclasfa; 04-08-2015 at 07:06 PM. Reason: repition
Reply With Quote
  #2  
Old 04-08-2015, 10:23 PM
gmayor's Avatar
gmayor gmayor is offline UserForm VBA coding Windows 7 64bit UserForm VBA coding Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The code and the userform should be stored in the document template project and not the normal template. The document template should be saved as macro enabled (DOTM) format. Create a new document from the template to run the macros.

You only need 2 buttons on the form - one to cancel, the other to perform the process(es).

The code associated with the form is

Code:
Option Explicit

Private Sub btnCancel_Click()
    With Me
        .Tag = 0
        .Hide
    End With
End Sub

Private Sub btnOK_Click()
    With Me
        .Tag = 1
        .Hide
    End With
End Sub
The processing is best done in the macro that calls the form, so in a standard module
http://www.gmayor.com/installing_macro.htm use something like the following, which includes two standard functions to fill bookmarks and write the log. Substitute (and/or add) the bookmark names, text field names and log file path as appropriate.

Insert the bookmarks where you require the data to be printed. You don't have to inbsert all the data in the document or in the log file.

Code:
Option Explicit

Sub AutoNew() 'Run when a new document is created from the template
    Call RunMyForm
lbl_Exit:
    Exit Sub
End Sub

Sub RunMyForm()
Dim oFrm As New UserForm1        'UserForm1 = The name of the userform
Dim oDoc As Document
Dim strLogEntry As String
    strLogEntry = ""
    On Error GoTo lbl_Exit
    Set oDoc = ActiveDocument
    With oFrm
        .Show
        Select Case .Tag
            Case 0: GoTo lbl_Exit        'Quit
            Case 1
                'Fill the named bookmark with the value from the userform
                FillBM "BookmarkName1", .TextBox1.Text
                'repeat for each bookmark/textbox name
                FillBM "BookmarkName2", .TextBox2.Text
                'Repeat for each textbox/logged item
                strLogEntry = strLogEntry & .TextBox1.Text & ", "
                'Omit ", " from the last named item.
                strLogEntry = strLogEntry & .TextBox2.Text
                'Update the log
                UpDateLog strLogEntry
        End Select
    End With
lbl_Exit:
    Unload oFrm
    Set oFrm = Nothing
    Set oDoc = Nothing
    Exit Sub
End Sub

Private 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 UpDateLog(strMessage As String)
Const strFileName As String = "C:\PATH\Latelog.txt"
Dim FileNum As Integer
    FileNum = FreeFile
    Open strFileName For Append As #FileNum        ' creates the file if it doesn't exist
    Print #FileNum, strMessage        ' write information at the end of the text file
    Close #FileNum        ' close the file
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Code in a UserForm module to delete a Command Button which opens the userform Simoninparis Word VBA 2 09-21-2014 03:50 AM
What kind of coding? ep2002 Excel 3 07-22-2014 07:52 AM
UserForm VBA coding Is it possible to take an input from a UserForm in one document to a UserForm in a do BoringDavid Word VBA 5 05-09-2014 09:08 AM
UserForm VBA coding text becomes pages of these *** and then coding EkkoJohnny Word 1 11-22-2013 12:37 AM
UserForm VBA coding Coding into a macro a carriage return sinbad Word VBA 6 02-27-2012 03:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft