1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Word 2010 VBA AutoOpen Macro to Insert Dates Into Text Boxes

Discussion in 'Business Applications' started by LukeB12345, Jul 3, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    I am trying to create an AutoOpen VBA Macro in Word 2010 files for automatically inserting the same dates into 2 different text boxes when I first open the file. Ideally, I would want 1 file to do this always for Yesterday's date (whatever that may be) and another file to do this for Friday's date (whatever that may be).

    Reason for this is because our mail room gets mail the day before I get it on my desk and I need to date stamp it using a Word 2010 template where I have 2 text boxes (please see attached file--oh, doesn't seem like I can attach the file???) for Tuesday through Friday. Right now, I am using the Macros I have created (1 for Yesterday's Date and another Macro for Friday's Date which is Date() -3).

    I want to use that Macro logic to put that into the Text boxes I have when it first opens? Please help me with this anyone......

    It doesn't seem like I can attach the file so here is the VBA code:

    Sub AutoOpen()
    '
    ' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
    If Application.ActiveProtectedViewWindow Is Nothing Then
    Application.GoBack
    End If

    ' InsertTextBox1 Macro
    Dim Shp As Shape
    Set Shp = ActiveDocument.Shapes.AddTextbox( _
    Orientation:=msoTextOrientationVertical, _
    Left:=22, Top:=252, Width:=25, Height:=170)
    Shp.TextFrame.TextRange.Text = "NMM RECEIVED: "
    Set Shp = Nothing
    Selection.InsertAfter Format(Now() - 1, "dddd, MMMM dd, yyyy")
    Selection.Font.Size = 8
    Selection.Font.Name = "Arial Narrow"
    Selection.Font.Bold = wdToggle
    Selection.Font.Bold = wdToggle

    ' InsertTextBox1 Macro
    Dim Shp2 As Shape
    Set Shp2 = ActiveDocument.Shapes.AddTextbox( _
    Orientation:=msoTextOrientationHorizontal, _
    Left:=432, Top:=752, Width:=170, Height:=18)
    Shp2.TextFrame.TextRange.Text = "NMM RECEIVED: "
    Set Shp2 = Nothing
    End Sub



    Thank you in advance,
    Luke
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Hi Luke,
    Check under Go advanced and then 'Manage Attchments' to select and attach the file, without the file it will be guessing what and how.
     
  3. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    Where is 'Go Advanced'??
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    You just replied, there is quite a large button next to 'Post Quick Reply' and what does it say ...? :)

    Go Advanced

    :) :) :)
     
  5. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    I hit "Go Advanced" .... Now where is Manage Attachments?? If I click the little paperclip icon or the arrow next to it, it does nothing??

    Can you tell me please how to attach a file (I don't understand why this has to be so complicated)??
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I do think you should try and read what's on the screen
     

    Attached Files:

    • TSG.jpg
      TSG.jpg
      File size:
      48.2 KB
      Views:
      139
  7. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    Yeah, I see your screenshot, but when I click Go Advanced, everything is the same except I don't have that "Manage Attachments" button like you do for some reason???

    So anyway, a lot of code has changed since we last talked and I could just post it up in the forum, then you can copy it into your a New Word Document (only thing is you would have to name that new word document "All Claims (Transparent).docx" if you have Word 2010 of course) since there's an If statement to apply the AutoOpen Macro to only that file.......
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Maybe your screen resolution is different, it's there or your IE is not functioning correctly.

    You can past your changed code and I'll see if I can figure out what you want.
     
  9. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    Ok here it is (so I am just reprinting these text boxes from this Word template onto thousands of claim forms already printed out by the time I get them):

    Sub AutoOpen()
    ' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
    If Application.ActiveProtectedViewWindow Is Nothing Then
    Application.GoBack
    End If
    If ActiveDocument.Name = "All Claims (Transparent).docx" Then
    ' InsertTextBox1 (Vertical)
    Dim Shp As Shape, sDate As String
    If Format(Now(), "ddd") = "Mon" Then
    sDate = Format(Now() - 3, "dddd, MMMM dd, yyyy")
    Else
    sDate = Format(Now() - 1, "dddd, MMMM dd, yyyy")
    End If

    Set Shp = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationVertical, _
    Left:=7, Top:=252, Width:=25, Height:=170)
    Shp.TextFrame.TextRange.Style = "Normal"
    Shp.TextFrame.TextRange.Font.Size = 8
    Shp.TextFrame.TextRange.Font.Name = "Arial Narrow"
    Shp.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
    Shp.Line.Visible = msoFalse


    ' InsertTextBox2 (Horizontal)
    Dim Shp2 As Shape, sDate2 As String
    If Format(Now(), "ddd") = "Mon" Then
    sDate2 = Format(Now() - 3, "dddd, MMMM dd, yyyy")
    Else
    sDate2 = Format(Now() - 1, "dddd, MMMM dd, yyyy")
    End If

    Set Shp2 = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
    Left:=462, Top:=765, Width:=142, Height:=18)
    Shp2.TextFrame.TextRange.Style = "Normal"
    Shp2.TextFrame.TextRange.Font.Size = 8
    Shp2.TextFrame.TextRange.Font.Name = "Arial Narrow"
    Shp2.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
    Shp2.Line.Visible = msoFalse
    Shp2.IncrementRotation (180)
    End If
    End Sub

    Oh yeah, the only thing left is to make the text inside the text boxes transparent (50%)??? That's what I need help with please???

    By the Way, I tried the same thing under Google Chrome, and it didn't have the Manage Attachments button (I think that is only for Premium Users or something right??)??
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I see what the macro does. AutoOpen only works if the docuemnt already has the macro in them
    A word file with macro's has the extension docm and not docx
    First a liitle ho to when pasting code:

    Start the section with [ code ] no blanks inside the square brackets
    paste the code and the close that section with [ / code ] and again no blanks inside the square brackets


    then you'll see something like this

    Code:
    Sub AutoOpen()
    ' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
    If Application.ActiveProtectedViewWindow Is Nothing Then
     Application.GoBack
     End If
    If ActiveDocument.Name = "[COLOR="Red"]All Claims (Transparent).docm[/COLOR]" Then
    ' InsertTextBox1 (Vertical)
    Dim Shp As Shape, sDate As String
     If Format(Now(), "ddd") = "Mon" Then
     sDate = Format(Now() - 3, "dddd, MMMM dd, yyyy")
     Else
     sDate = Format(Now() - 1, "dddd, MMMM dd, yyyy")
     End If
    
     Set Shp = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationVertical, _
     Left:=7, Top:=252, Width:=25, Height:=170)
     Shp.TextFrame.TextRange.Style = "Normal"
     Shp.TextFrame.TextRange.Font.Size = 8
     Shp.TextFrame.TextRange.Font.Name = "Arial Narrow"
     Shp.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
     Shp.Line.Visible = msoFalse
    
    
    ' InsertTextBox2 (Horizontal)
    Dim Shp2 As Shape, sDate2 As String
     If Format(Now(), "ddd") = "Mon" Then
     sDate2 = Format(Now() - 3, "dddd, MMMM dd, yyyy")
     Else
     sDate2 = Format(Now() - 1, "dddd, MMMM dd, yyyy")
     End If
    
     Set Shp2 = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
     Left:=462, Top:=765, Width:=142, Height:=18)
     Shp2.TextFrame.TextRange.Style = "Normal"
     Shp2.TextFrame.TextRange.Font.Size = 8
     Shp2.TextFrame.TextRange.Font.Name = "Arial Narrow"
     Shp2.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
     Shp2.Line.Visible = msoFalse
     Shp2.IncrementRotation (180)
    End If
    End Sub
    
    Notice the document name in the macro it's doc instead of docx.

    Run it and it qorks, but it will run every time you open this document, I don't think that is what you want but ...
     
  11. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    Ok, thanks for the tips (I knew that but don't use Word too much for Macros, mostly .xlsm files). The file works as of now (not sure what you mean "AutoOpen only works if the docuemnt already has the macro in them"??) and it is working exactly as I want it to work except that the text boxes don't have transparency applied to them when the document is first opened.

    Anyway, I do want it to run everytime I open the document since that document serves as my template for date stamping the claim forms.

    Can you please let me know how to make the text boxes transparent??
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Default is no fill so transparant
    see attachment

    One thing you'll have to keep in mind you're creating a new textbox everytime which is place over the previous one.
    I'm no Word guru so you'll have to figure out something like if shp.... exists then use it and update it or else delete all shapes before you begin
     

    Attached Files:

  13. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    So I see the TimeStampModule code is different than the code I had shared with you...
    So what do I need to put in the .Fill.Backcolor = line (which is commented out at the moment)??

    I am not sure if .Fill.Backcolor will make the text inside the Text box transparent, either??
    I tried, instead, .Fill.Transparency = 0# (not sure if it suppose to be 1#, tried both on both shapes\text boxes and neither worked)??

    Also, only 1 text box shows up halfway in, halfway out?? (I guess the margins for that .docm file are different--did you change the positions of the text boxes??)
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I don't know, that was a test of mine but I couldn't work that one out.
    You'll have toGoogle.

    I changed the formatting for the date in that sense that the values is the same only another shape and orientation, so why reset the value twice, onec is enough
     
  15. LukeB12345

    LukeB12345 Thread Starter

    Joined:
    Jul 3, 2013
    Messages:
    41
    So I actually have figured a way to make both text boxes transparent.

    Just had to use a Style in the VBA code to apply the Style I had created through the GUI. I am not sure if it would be better or not to pursue getting it to work in the VBA code alone without relying on any GUI Style created??
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1102703

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice