#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 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 |
|
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 |
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 |
text becomes pages of these *** and then coding | EkkoJohnny | Word | 1 | 11-22-2013 12:37 AM |
Coding into a macro a carriage return | sinbad | Word VBA | 6 | 02-27-2012 03:51 AM |