• Word 2010 macro fails when inserting text box

    Home » Forums » AskWoody support » Productivity software by function » MS Word and word processing help » Word 2010 macro fails when inserting text box

    Author
    Topic
    #476442

    After upgrading to Office 2010, I tried to record a macro like one I’d used in Word 2007:
    1) In an open document, click on “Developer” tab, click on Record Macro, name it “InsertTextBox” and give short description, and click “OK” (“Store…” shows “Normal.dotm”.)
    2) Click on “Insert” tab, then on “Text Box” then on “Simple Text Box”
    3) Type “This is the text”
    3) Press “Escape” twice to regain regular cursor
    4) Click on “Developer” tab, then on “Stop Recording”

    When I try to run the macro, I get a Microsoft Visual Basic error window, saying:
    ====================================
    Run-Time Error
    ‘-2147024809(80070057)’
    The item with the specified name wasn’t found.
    =====================================

    If I click on “Debug”, I see:
    =====================================
    Sub InsertTextBox()

    ‘ InsertTextBox Macro
    ‘ InsertsTextBox

    ActiveDocument.Shapes.Range(Array(“Text Box 2”)).Select
    Application.Templates( _
    “C:UsersDaveAppDataRoamingMicrosoftDocument Building Blocks103314Built-In Building Blocks.dotx” _
    ).BuildingBlockEntries(” Simple Text Box”).Insert Where:=Selection.Range, _
    RichText:=True
    Selection.TypeText Text:=”This is the text”
    Selection.EscapeKey
    Selection.Collapse
    End Sub
    =========================================
    Highlighted in yellow is the line “ActiveDocument …. Select”

    I’ve tried this several times, with the same result, regardless of whether I choose to insert “simple text box” or any of the others, or “draw text box” and create my own.

    Any ideas of what I’m doing wrong or why it doesn’t work… since it did in Word 2007…?

    Many thanks.

    Dave

    Viewing 25 reply threads
    Author
    Replies
    • #1278283

      Hi Dave,

      Try something along the lines of:

      Code:
      Sub InsertTextBox()
      Dim Shp As Shape
      Set Shp = ActiveDocument.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, _
        Left:=72, Top:=72, Width:=288, Height:=72)
      Shp.TextFrame.TextRange.Text = "This is the text"
      Set Shp = Nothing
      End Sub

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

      • #1278351

        Paul,
        Thanks. I should probably have mentioned in my post that I never learned to “write” macros… just to “record” them in Word.
        So, I just tried replacing the code I’d posted with yours. It *does* work… but leaves me with two other quandaries: not knowing Basic, how do I make the changes I’ll need (e.g., having my text in red and adding an “updating” date; and, why doesn’t *recording* the macro actually produce a working macro?
        Cheers.
        Dave

      • #1399708

        Macropod, can you please help me expand that VBA coding? I am using that coding to insert 2 Textboxes (one horizontal in upper left corner of paper and one vertical along right side of paper) and I have changed the “This is the text” to suit what my company needs to have on the paper. The business process is date stamping (mail room gets claim forms and attachments one day before they show up on my desk and I have to date stamp all pages with Yesterday’s date) (also, on Monday’s, I have to date stamp Friday’s date).

        Good news is that I already have the coding to Insert Friday’s and Yesterday’s dates by running the 2 macros respectively. I also have created an AutoOpen macro that puts 2 text boxes in the right positions on the page with the Text I need it to say, but the part I need help with is how to insert Yesterday’s date and Friday’s date within the text boxes after the text (“This is the text”) and also be in the format I need it to be in.

        Here is the VBA coding I am using (wish I could just upload my file on this forum??):

        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

        ‘ InsertTextBox2 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

        Let me know how to do this please??

    • #1278335

      Dave,

      The code posted by macropod is generally going to be more reliable than the code you recorded, so you should give that a try.

      However, if you do want to get your recorded code running, it will work OK if you just delete the first line of code (the one you flagged as causing an error). Also, make sure there’s no space in the middle of the word “Document” as there is in the code you posted here.
      One possible benefit of the code as you recorded it, is that the text box it brings in as a building block, contains a content control with explanatory text in it – that might be helpful to inexperienced users (or annoying overkill to experienced users!)

      Gary

      PS: I removed the e-mail address that you added – it might not be a good idea to post your personal e-mail address here.

      • #1278353

        Gary,
        And, thanks to you, too.
        I tried what you suggested. It resulted in the words (no quotes) “This is the text” at the left margin, and a textbox anchored a short distance to its right, in which remains Word’s field code, “[Type a quote from the document or the summary of an interesting point. You can position the text box anywhere in the document. Use the Drawing Tools tab to change the formatting of the pull quote text box.]”.
        And, I’m still left with the quandaries I just posted as a reply to Paul.
        Again, though, many thanks.
        Cheers.
        Dave

    • #1278366

      Hi Dave,

      The underlying reason your recorded code didn’t work is because the line:
      ActiveDocument.Shapes.Range(Array(“Text Box 2”)).Select
      tells Word to select an exsiting textbox (which may or may not exist) and replace that with another one.

      As for:

      It *does* work… but leaves me with two other quandaries: not knowing Basic, how do I make the changes I’ll need (e.g., having my text in red and adding an “updating” date

      it’s never too late to start learning. The macro recorder can be used to create code showing you the basics of what’s required to make the text red and insert a date field. It’s then just a matter of adapting that code to your needs. You’ll soon start to get the hang of it, especially if you do searches for code that does similar things (eg: Word vba font color red, or Word vba date field)

      Cheers,
      Paul Edstein
      [Fmr MS MVP - Word]

    • #1278375

      Paul,
      Thanks again.
      I’ll give it a shot… 🙂
      Dave
      p.s. I’m still wondering, though, why Word’s own macro recorder produces a macro that doesn’t work… when I used the same process to create a macro that *did* work in Word 2007.. or is that just another “Microsoft feature”… 🙂

    • #1399714

      Does this work?

      Code:
          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:=22, 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
      • #1399910

        Thank you so much Andrew Lockton (my last name is Bourke like that city I heard about in Australia)

        Anyway, I would like 2 text boxes to populate automatically when this file opens. I plan on making 1 file for Yesterday’s date, 1 for 3 day weekends (Now() – 4), 1 for 4 day weekends (Now() – 5), and 1 for 2 days back (like yesterday we got off of work for 4th of July Independence Day but have to work today so that is Now() – 2) unless there is a way to handle that if I let you know the days our work schedule meets those conditions??

        Here is the code I have that is doing exactly what I want it to do:

        Sub AutoOpen()
        If ActiveDocument.Name = “test AutoOpen Macro.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

        1 Issue, though:

        I need both of the Text boxes to be transparent (50%) (because I superimpose the text boxes onto claim forms that have writing and form date that goes all the way to almost the edge of the paper already and if I make it not transparent, it will make the text unreadable. So I use transparency to make the text in the text box readable as well as the claim text that is already printed on there prior to running it them through the printer again).

        Besides that, perfection!!! Thank you so much!!!!

    • #1400019

      Luke
      This variation prompts the user to provide the number of lead days required. It provides the default starting point (1 day or 3 days) so you can usually just press enter unless there is a special holiday delay (in which case, you type in the number of days you need the date to roll back by).

      Code:
      Sub Temp1()
        Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
        If Format(Now(), “ddd”) = “Mon” Then
          iDelay = 3
        Else
          iDelay = 1
        End If
        iDelay = InputBox(“How many days ago do you need the logging box to display”, “Received x days ago”, iDelay)
        sDate = Format(Now() – iDelay, “dddd, MMMM dd, yyyy”)
        
        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.Fill.ForeColor = RGB(255, 255, 255)
        Shp.Fill.Transparency = 0.7
        Shp.Line.Visible = msoFalse
        
        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
        Shp.Fill.ForeColor = RGB(255, 255, 255)
        Shp.Fill.Transparency = 0.7
        Shp2.IncrementRotation (180)
      End Sub
      • #1400089

        So I see now that you have parameterized the macro….very cool….(I didn’t know how to do that in VBA, have been working in MS Access GUI using the [Enter parameter here] syntax in the Criteria row of the GUI)

        Proposal #1: Would it be possible to figure in parameters only for when holidays that my company has declared a day off of work (to check first the longest date range where business is closed due to holidays) and apply the parameter?

        Example1: I believe that the longest amount of days in the calendar year my company gets days off of work for is Thanksgiving (4 days off in a row: 4th Thursday and Friday of every November plus the Weekend days of Saturday and Sunday). So could we have the VBA check for specified dates in the calendar year and if those specified dates fall within a 5 day range, run the parameterized statement? If not, then it could just run the If statement checking for if it is Monday (Now() – 3 days) and if not, Now() – 1 day?
        I believe that we could put each specified holiday day off of work into a Case statement and show the parameterized Input Box….

        Proposal #2: Would there be a way to tie in those specific dates to the system calendar so that the code would
        1. First check if one of those specified dates falls into a weekday, if not just go to If statement for Monday (Now() – 3 days), else Now() – 1 day.
        2. If one of those specified dates happens to fall on a weekday between Tuesday and Thursday, apply a Now() – 2 days (since business would be open the day before and after one of those days)
        3. If one of those specified dates happens to fall on a Monday or a Friday, apply a Now() – 4 days (since it would be a 3 day weekend for employees)
        4. Could we specifiy a date range for a 4 day weekend (Thanksgiving weekend is always the 4th Thursday and Friday of November) and apply a Now() – 5 days
        5. I don’t think that this one would apply but: If it is a holiday where employees get 2 days off, then go back to work the 3rd day, then apply a Now() – 3 days
        Again, I believe that a Case statement could handle this sort of thing….??

        Code:
        Sub AutoOpen()
          Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
          If Format(Now(), "ddd") = "Mon" Then
            iDelay = 3
          Else
            iDelay = 1
          End If
          iDelay = InputBox("NMM RECEIVED How many day(s) ago?", "Received x days ago", iDelay)  [COLOR="#FF0000"]I am not sure why "Received x days ago" is written here??[/COLOR]
          sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
          
          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.Fill.ForeColor = RGB(255, 255, 255)  [COLOR="#FF0000"]I am not sure what this line is doing (guessing it is White)??[/COLOR]
          Shp.Fill.Transparency = 0.7                     [COLOR="#FF0000"]This isn't making the text inside the text box transparent??[/COLOR]
          Shp.Line.Visible = msoFalse
          
          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
          Shp.Fill.ForeColor = RGB(255, 255, 255)     [COLOR="#FF0000"]I am not sure what this line is doing (guessing it is White)??[/COLOR]
          Shp.Fill.Transparency = 0.7                        [COLOR="#FF0000"]This isn't making the text inside the text box transparent??[/COLOR]
          Shp2.Line.Visible = msoFalse
          Shp2.IncrementRotation (180)
        End Sub

        For sure, this sort of capability would be amongst the greatest most useful templates that many many people’s lives would be beneficiaries and of course, you would get all the CREDIT Andrew!!!
        Thank you so much for your help Andrew again. I really appreciate it!!!

    • #1400108

      To explain the red bits in the code
      1. The InputBox function is shown with three parameters: The first is the text shown inside the dialog, the second is the title of the dialog, the third is the string value which appears as the default answer.
      2. Yes, this is giving the textframe a white fill and the next line makes it partially transparent. If you made the text transparent then you won’t be able to read it.

      Yes it is possible to get the macro to work completely off a calendar of your choosing but that is something that may need to be maintained and updated by yourself every year (since holiday dates vary with location and year). As such, this is beyond my control and so I haven’t coded it that way. In addition, it would take a lot more work to put together and I only do little jobs for free :rolleyes:. What I will do for free is point you towards google which does have examples where others have tried to solve similar problems. Standing on the shoulders of those giants might get you what you require.

      http://www.ozgrid.com/forum/showthread/?t=75758
      http://www.vbaexpress.com/kb/getarticle.php?kb_id=42
      http://www.vbaexpress.com/forum/showthread/?t=38740

      • #1400173

        Thank you for the references Andrew,

        I will definitely try to get it to work off of the calendar and maintain it every year (even though I am not good at VBA)…

        As far as the transparency, I am not sure why the text won’t show as 50% transparent (or 70% transparent which is what it looks like the 0.7 means)?
        I mean, if I right click on the text within the text box and select Font, then hit the Text Effects button, then change the transparency to 50%, the text will show in a gray transparent color shade (which is what I need it to do since on many of the forms, printed letters are already present in black–so if I printed without transparency, it would make what was already printed and the new date stamp both unreadable). I need a way of making the text transparent at 50%, not so that it is totally 100% transparent which, like you are saying, would make it unreadable.
        I have actually tried a few different things from seeing examples on the internet and it looks like all the examples use the Selection.[something I forgot].Transparency = 0.5 (or whatever percentage) syntax (which is different from the syntax we are using here to get this thing to work). I don’t know if I have to completely scrap all of the syntax and figure out a way to make it transparent using the Selection.[next property].[next property] syntax??

        Please let me know how to figure out this transparency issue I am faced with because I can’t seem to find anything conclusive on the Internet examples I have seen…..

    • #1400187

      I can see how to make the text partially transparent using the Text Effects button but this is not captured by the Macro Recorder and the VBA editor’s intellisense doesn’t provide any assistance either. I was expecting something like the following would work but frustratingly it doesn’t …

      Shp2.TextFrame.TextRange.Font.Transparency = 0.5

      In the absence of any relevant VBA code being discovered, you may need to take another approach. For instance, place the text as a graphic or word art and make that partially transparent. Another alternative is to simply apply a style to that content which already has the attributes you require which you set using the GUI.
      Shp.TextFrame.TextRange.Style = “SlimShady”
      That second option sounds much simpler but it has a minor detail you would first need to overcome ie. Is the style already in the document!!. You would need to firstly copy this from the template where the macro resides before calling that line. There will be code to do this somewhere on this forum.

      • #1400188

        I see, like I said, the syntax I saw in many examples online is Selection.[something].Transparency = 0.5 (I might be a little off on that but I will repost with example(s)). So I guess it seems like I might want to scrap the syntax for something else because VBA doesn’t like the Shp2.TextFrame.[the rest of it] in that way??

        I actually have been playing with ways to minimize the number of steps it would take to make it perfect (hence, my objective of just opening the file and pressing the print button). I played with using an Excel Object in it, but only made the font look weird. I also played with using graphics but didn’t really solve any problem. I think the best one I came up with was that I created a Style that has 50% transparency, so I would have to 1. open the file 2. select each text box (can’t Control-A to select all for text boxes) and hit the Style I had created, then 3. press print

        Like I said, I will repost with some examples tomorrow (gotta go to bed here in California time).

        Oh, I love the first link you referenced to me. I can understand what the algorithm is doing and how that person put the Select Case statement, but I don’t understand all of the syntax (it is Excel VBA, not Word VBA which I believe is a little different). I am going to play around with it using my very limited knowledge of Word VBA.

        Andrew, can you work with me on that Excel VBA so we can modify it to make it work in Word please?? (I registered an account on Ozgrid.com and posted a reply to that person, but not sure if that person is going to reply (it is an old post by now, I just hope that person will reply).

    • #1400192

      Whoa!! I am kind of getting lost here…..I understand what you are saying about applying a style in the GUI, but I am getting confused when you say “Is the style already in the document!! You would need to firstly copy this from the template where the macro resides before calling that line. There will be code to do this somewhere on this forum.”

      I am not sure if MS Word applies the Style I created with 50% transparency to all existing and new MS Word documents (under my computer for anyone that logs in, under my login only, or maybe something else??)

      Can you kind of go a little slower for what I need to overcome here??

      So I tested it out with that syntax Shp.TextFrame.TextRange.Style = “DateStamp” and it totally worked!!!!
      I just don’t know if it will only work on that file or if it will work on all files??

    • #1400278

      Luke

      The line which applies DateStamp style will work if (and only if) the style is already in the document. It sounds like you are running this macro on files you are getting from outsiders so it is highly unlikely they already have a style with that name in their documents. Even if they did, could you trust that it has the same font settings that you require? If you created a template and got those users to supply only files created with the template then you might have a chance of this happening.

      So instead you put the style as you need it into Normal.dotm (where you also store the macro) and include code to copy the style into the current document prior to applying that style to your new text box content.

      Code:
      Sub AutoOpen()
        Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
        If Format(Now(), “ddd”) = “Mon” Then
          iDelay = 3
        Else
          iDelay = 1
        End If
        iDelay = InputBox(“NMM RECEIVED How many day(s) ago?”, “Received x days ago”, iDelay)
        sDate = Format(Now() – iDelay, “dddd, MMMM dd, yyyy”)
      
        ‘this line will fail if the style DateStamp doesn’t exist in the Normal Template
        Application.OrganizerCopy Source:=NormalTemplate.FullName, _
                  Destination:=ActiveDocument.Name, Name:=”DateStamp”, Object:=wdOrganizerObjectStyles
        
        Set Shp = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationVertical, _
              Left:=7, Top:=252, Width:=25, Height:=170)
        
        Shp.TextFrame.TextRange.Style = “DateStamp”
        Shp.TextFrame.TextRange.Text = “NMM RECEIVED: ” & sDate
        Shp.Fill.ForeColor = RGB(255, 255, 255)
        Shp.Line.Visible = msoFalse
        
        Set Shp2 = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
              Left:=462, Top:=765, Width:=142, Height:=18)
        Shp2.TextFrame.TextRange.Style = “DateStamp”
        Shp2.TextFrame.TextRange.Text = “NMM RECEIVED: ” & sDate
        Shp.Fill.ForeColor = RGB(255, 255, 255)
        Shp2.Line.Visible = msoFalse
        Shp2.IncrementRotation (180)
      End Sub
    • #1400279

      Luke

      I have had a look at your post on ozgrid and picked up a key fact that changes everything. It appears you are using this macro to create an overprint page which you then print on top of pages that have been already printed. If this is the case then you are not modifying electronic copies of the existing documents and there is no real need for a more complicated macro. A macro might save you three seconds a day but take 10 hours to create.

      The process I would use is:

        [*]Create a Word document with the two text boxes and put the required date into both these text boxes.
        [*]Format the content of the text boxes as grey (they don’t need to be transparent unless you are overlaying them inside each file prior to the first print)
        [*]Save the document because you will use it again tomorrow.
        [*]Print a single page and take it to the photocopier
        [*]Load the paper tray with the reports and put your single printed page on the glass.
        [*]Max the count on the copies and hit go – when it runs out of paper then your job is done and you can reset the copier
        [*]Throw away the piece of paper on the glass

      Tomorrow, edit the Word file you saved in step three (change the date to the date required today) and repeat steps 3 onwards.

      • #1400295

        So I guess that is one way to do it, but I actually prefer to open the Word document that runs the AutoOpen macro, inserting the correct date in the 2 transparent text boxes automatically and then just pressing print (specifying more pages than I have claim forms that I load prior to pressing print and canceling the print job once all papers have been date stamped).

        I think I know what you are saying about the Normal.dotm template in your previous post. I am not getting files from other peopleemployees, this is a 1 file project to make the tedious task of manually entering the date into 2 transparent text boxes in the correct format every day completely automated so I can just give it a quick visual check (to make sure the dates are correct), load the stack of preprinted claim forms into the printer tray, and press print. I really do appreciate seeing how to insert the VBA code into a Normal.dotm (macro template), but I don’t think that would be necessary for this project, right?? I believe I took care of that sort of issue by using the If statement (If Application.ActiveDocument.Name = “[name of document]” Then [rest of VBA code] End If) around the whole VBA code.

        And yes, that 3 seconds a day (more like 2 minutes a day) times many years of doing this adds up. Besides, it isn’t only about me, if I do, one day, move up and don’t have to ‘datestamp’ these claim forms every day, someone else will have to do it and feel as annoyed as me if that person has to manually write the correct date in the correct format every day, you know??

        What I do need help and time with is how to merge my VBA syntax that you have so graciously helped me with, with the VBA syntax of the OzGrid post section: ‘ official market holidays in USD Area (I hope I will have more time to commit to this undertaking tomorrow and in the days to come, however long it takes).

    • #1400301

      I am not going to put in the effort to produce the correct date every time. The rewards for solving that problem are IMO not worthy of the time which would need to be spent on it.

      The attached document is the best I am prepared to do. I have put in a duplicated Date content control and a macro. Every time you open this document it will already have the right date in it (assuming the macro runs). The exception is the day after a holiday. On those rare occasions, you click on either of the dates and a popup will appear so you can choose the day you wish to display. This solution doesn’t require anything in the Normal template. It also doesn’t require you to do anything other than open and print each day. I would recommend you make the document read-only so that uncontrolled changes don’t occur in the document from day to day.

      The code has been simplified to the bare minimum – there is no insertion of text boxes required because they are already in the document. There is no formatting required because that is also already in the file. All you need to do is pay enough attention to do two clicks on the day following a public holiday.

      • #1400315

        That is very cool (I believe that is called a Date Picker Control).

        Can I change the Title of the Content Control from “PublishDate” to “Mail Room Receive Date”? If I do it gives me one of those VBA Run-time error ‘5941’: The requested member of the collection does not exist.
        So I click Debug and it highlights that line where the Content Control Title exists….

        Also I see that the Macro you created says “Option Explicit” at the top (does this mean that that code makes the AutoOpen macro run exclusive to that file??)

        • #1400391

          If you change the title of the content control then you need to change the line of the code which selects by title

          Set oCC = ActiveDocument.SelectContentControlsByTitle(“Mail Room Receive Date”).Item(1)

          Option Explicit tells the module that all variables need to be declared before you can use them. The lines that start with ‘Dim’ are declaring what type each variable is. This is good programming practice as it allows the computer to allocate the minimum amount of space possible to a variable.

          In your posted code there are functions. These take an input and return an output. Looking at the code, if you wanted to find the date of easter you could try
          MsgBox(“Easter in 2013 falls on ” & EasterDate(2013))

          Or if you wanted to capture the date for easter in a variable you could use something like this
          sDate = Format(EasterDate(2013), “dddd, MMMM dd, yyyy”)

          A more complex function in that code starts with
          Function DOWsInMonth(yr As Integer, M As Integer, DOW As Integer) As Integer
          the above line declares three variables of type integer as inputs and returns another integer variable with the name of the function itself

          In this case, you pass in three variables in the order of year, month, day of week – such as
          MsgBox(“The number of Tuesdays in Jan 2013 is ” & DOWsInMonth(2013,1,3))

          • #1400397

            OK, so here are the days my work gets off:

            New Year’s Day 1/1/13 Always
            Martin Luther King Day 1/21/13 Variable (3rd Monday of January)
            President’s Day 2/18/13 Variable (3rd Monday of February)
            Memorial Day 5/27/13 Variable (Final Monday of May)
            Independence Day 7/4/13 Always
            Labor Day 9/2/13 Variable (1st Monday of September)
            Thanksgiving Day (& Friday) 11/28/13 and 11/29/13 Variable (4th Thursday and Friday of November)
            Christmas Day 12/25/13 Always

            I could go like this?: ( I don’t know that whole line of math they are doing. In Other words, why does it say d = (((255 – 11 * (yr Mod 19)) – 21) Mod 30) + 21
            EasterDate = DateSerial(yr, 3, 1) + d + (d > 48) + 6 – ((yr + yr 4 + d + (d > 48) + 1) Mod 7)
            What are all these numbers??

            sDate = Format(ChristmasDay(2013), “dddd, MMMM dd, yyyy”)
            If sDate = Wednesday, December 25, 2013 Then
            sDate = Format(Now() – 2, “dddd, MMMM dd, yyyy”)
            ElseIf Format(Now(), “ddd”) = “Mon” Then
            sDate = Format(Now() – 3, “dddd, MMMM dd, yyyy”)
            Else sDate = Format(Now() – 1, “dddd, MMMM dd, yyyy”)
            End If

            Or I could just go with that other code since it seems to be more adaptable (up to the year 2500 as part of the title was called) referencing:
            Select Case ISO_datum
            Case 102, 705, 1226
            calendar_USD = False
            Exit Function
            Case 115 To 121 ‘ Martin Luther King day ( it falls on 3rd Monday )
            calendar_USD = False
            Exit Function
            Case 215 To 221 ‘ President’s day ( it falls on 3rd Monday )
            calendar_USD = False
            Exit Function
            Case 525 To 531 ‘ Memorial day ( it falls on last Monday )
            calendar_USD = False
            Case 901 To 907 ‘ Labor day ( it falls on first Monday )
            calendar_USD = False

            So from that, I can just make an If statement like this??:

            If Case = 115 To 121 Then
            sDate = Format(Now() – 4, “dddd, MMMM dd, yyyy”)
            If Case = 215 To 221 Then
            sDate = Format(Now() – 4, “dddd, MMMM dd, yyyy”)
            If Case = 525 To 531 Then
            sDate = Format(Now() – 4, “dddd, MMMM dd, yyyy”)
            If Case = 901 To 907 Then
            sDate = Format(Now() – 4, “dddd, MMMM dd, yyyy”)
            If Case = 1225 Then ‘(or If Case = 1225 & Format(Now(), “ddd”) = “Mon” Then sDate = Format(Now() – 4, “dddd, MMMM dd, yyyy”) and a different one for each day??)
            sDate = Format(Now() – ?, “dddd, MMMM dd, yyyy”) ’ depends what day it falls on right?

            All of those except for Christmas are Mondays and that is why I can just go Now() – 4 for all of those, but don’t know how I would do Thanksgiving. But when I come to think of it, I would be putting wrong dates in for the rest of the ranges (I am getting more and more confused)??

    • #1400349

      I don’t know if this code is going to help at all, but I found this (would you be able to tell me how I am suppose to run the functions and produce output to go into the text boxes)??

      Code:
      Option Explicit
      Option Base 1
      
      Public Function EasterDate(yr As Integer) As Date
          Dim d As Integer
          d = (((255 - 11 * (yr Mod 19)) - 21) Mod 30) + 21
          EasterDate = DateSerial(yr, 3, 1) + d + (d > 48) + 6 - ((yr + yr  4 + d + (d > 48) + 1) Mod 7)
      End Function
      
      ' We can generalize this to holidays that are defined as the Nth Day of some month,
      ' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
      ' The following function will return the Nth DayOfWeek for a given month and year:
      'SYntax NDOW:
      '   y = Year
      '   M = Month
      '   N = Nth day of M month
      '   DOW = Day of the week:  1 = Sunday, 2= Monday, etc.
      Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
          NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
      End Function
      
      ' To compute this date, we first need a function to tell us how many Mondays there are in the month.
      Public Function DOWsInMonth(yr As Integer, M As Integer, DOW As Integer) As Integer
      On Error GoTo EndFunction
      Dim i As Integer
      Dim Lim As Integer
      Lim = Day(DateSerial(yr, M + 1, 0))
      DOWsInMonth = 0
      For i = 1 To Lim
          If WeekDay(DateSerial(yr, M, i)) = DOW Then
              DOWsInMonth = DOWsInMonth + 1
          End If
      Next i
      EndFunction:
      Err.Clear
      On Error GoTo 0
      End Function
      
      Public Function Thanksgiving(yr As Integer) As Date
         Thanksgiving = DateSerial(yr, 11, 29 - WeekDay(DateSerial(yr, 11, 1), vbFriday))
      End Function
      
      Public Function WkNr(AnyDate As Date, Optional WhichFormat As Variant) As Integer
      '
      ' WhichFormat: missing or  2 then returns week number,
      '              = 2 then YYWW
      '
      Dim ThisYear As Long
      Dim PreviousYearStart As Date
      Dim ThisYearStart As Date
      Dim NextYearStart As Date
      Dim YearNum As Integer
       
      ThisYear = Year(AnyDate)
      ThisYearStart = YearStart(ThisYear)
      PreviousYearStart = YearStart(ThisYear - 1)
      NextYearStart = YearStart(ThisYear + 1)
      Select Case AnyDate
          Case Is >= NextYearStart
              WkNr = (AnyDate - NextYearStart)  7 + 1
              YearNum = Year(AnyDate) + 1
          Case Is < ThisYearStart
              WkNr = (AnyDate - PreviousYearStart)  7 + 1
              YearNum = Year(AnyDate) - 1
          Case Else
              WkNr = (AnyDate - ThisYearStart)  7 + 1
              YearNum = Year(AnyDate)
      End Select
       
      If IsMissing(WhichFormat) Then
          Exit Function
      End If
      If WhichFormat = 2 Then
          WkNr = CInt(Format(Right(YearNum, 2), "00") & Format(WkNr, "00"))
      End If
       
      End Function
       
      'This function requires the YearStart function which returns the date of the first Monday of a given year.
      Function YearStart(WhichYear As Long) As Date
       
      Dim WeekDay As Integer
      Dim NewYear As Date
       
      NewYear = DateSerial(WhichYear, 1, 1)
      WeekDay = (NewYear - 2) Mod 7
      If WeekDay < 4 Then
          YearStart = NewYear - WeekDay
      Else
          YearStart = NewYear - WeekDay + 7
      End If
       
      End Function
      
      Public Function IsWeekend(wDate As Date) As Integer
          If WeekDay(wDate) = 7 Or WeekDay(wDate) = 1 Then IsWeekend = 1
      End Function
      
      Public Function isHoliday(wDate As Date) As Integer
          Dim sDate As Date
          Dim tDate, tYear As Integer
          tDate = EasterDate(Year(wDate))
          sDate = tDate
          Select Case wDate
              Case DateValue(Year(wDate) & "-01-01")
                  isHoliday = 1   '   New Year's day
              Case DateValue(Year(wDate) & "-06-04")
                  isHoliday = 1   '   Independance Day
              Case DateValue(Year(wDate) & "-05-01")
                  isHoliday = 1   '   Labor Day
              Case DateSerial(Year(wDate), 11, 29 - WeekDay(DateSerial(Year(wDate), 11, 1), vbFriday))
                  isHoliday = 1   '   Thanksgiving
              Case NDow(Year(wDate), 1, 3, vbMonday)
                  isHoliday = 1   '   Martin Luther King Day
              Case tDate
                  isHoliday = 1   '   Easter Sunday
              Case tDate + 1
                  isHoliday = 1   '   Easter Monday
              Case tDate + 39
                  isHoliday = 1   '   Ascension Day
              Case tDate + 49
                  isHoliday = 1   '   Whit Sunday  (Penteccost)
              Case tDate + 50
                  isHoliday = 1   '   Whit Munday
              Case DateValue(Year(wDate) & "-12-25")
                  isHoliday = 1   '   Christmas Day
          End Select
      End Function
      
      Public Function WorkDays(dDate1 As Date, dDate2 As Date) As Integer
          Dim tDays As Integer
          Dim dTemp As Date
          tDays = dDate2 - dDate1 + 1
          dTemp = dDate1
          Do While dTemp <= dDate2
              tDays = tDays - IIf(isHoliday(dTemp) Or IsWeekend(dTemp), 1, 0)
              dTemp = dTemp + 1
          Loop
          WorkDays = tDays
      End Function
      
      Public Function DaysOff(dDate1 As Date, dDate2 As Date) As Integer
          Dim tDays As Long
          Dim dTemp As Date
          tDays = 0
          If dDate1 <= dDate2 Then
              dTemp = dDate1
              Do While dTemp <= dDate2
                  tDays = tDays + IIf(isHoliday(dTemp) Or IsWeekend(dTemp), 1, 0)
                  dTemp = dTemp + 1
              Loop
          End If
          DaysOff = tDays
      End Function
      
      
      Public Function lastDay(xM As Integer, Optional tYear As Long) As Integer
         tYear = IIf(tYear = 0, Year(Date), tYear)
         Select Case xM
            Case 1, 3, 5, 7, 8, 10, 12
               lastDay = 31
            Case 4, 6, 9, 11
               lastDay = 30
            Case 2
               lastDay = IIf(Int(tYear / 4) = tYear / 4, 29, 28)
         End Select
      End Function
      
    • #1400412

      Luke

      If you think the date functions are complicated you ain’t seen nothing yet. The basic flow I would use to solve this is:
      1. Build an array of all the dates of public holidays for the current year
      2. Use that array to build a dictionary of all the dates that are the working days that follow a public holiday
      3. Populate the dictionary with the nearest preceding work day (iterating to check that day is not also a public holiday)
      4. Amend the date stepping code to hijack the usual 1day/3days pattern if the current date happens to be in the dictionary

      The dictionary object would be used to create a list of date pairs (today’s date and last working date) which can be searched without looping an array. Excellent code for working with arrays and dictionaries is on this forum.

      Then sit back and hope that no-one ever has a day off sick because all that coding effort will be wasted unless you amend the code to be smart enough to work out that the code wasn’t run yesterday so it needs to display not yesterday but the day before yesterday.

      This idea actually makes me think there is another way of doing this – what if you stored today’s date in the document metadata and simply inject that date into the content control next time the document is opened? If the file was opened once every work day and never at any other time this would be a flawless solution and require no date manipulation at all.

    • #1400435

      OK, so it sounds like a good strategy and I will look on this forum and probably google arrays and dictionaries as well. Here is an article I found that I don’t know if you are talking about doing something like this: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

      Don’t worry about sick days because if I don’t do it because I am sick or something, someone else will have to do it for that particular day (regardless, it will get done every day).

      I hope I am not understanding you correctly about storing today’s date in the document metadata to populate the content control every time it is opened with the current date…..This is not the objective at all, remember the mail room receives the mail 1 day before I get it on my desk and 3 days before from Friday to Monday. If it was the current day that the mail room received it and I got it to date stamp it the same day, I wouldn’t have to use any VBA macro at all, it would be super easy just to put a form control for Updating the current day. But unfortunately, it is as the way I just mentioned in this paragraph…..

      I don’t know VBA and you are probably going to think ‘What in the world’ or something but I am taking snippets of code to make this:

      Sub HolidayExclusions(ByVal Target As Range)
      Dim Arr As Variant
      Dim Dic As Scripting.Dictionary
      Set Dic = New Scripting.Dictionary

      Holidays = Array(“01/01”, “01/21”, “02/18”, “05/27”, “07/04”, “09/02”, “11/28”, “11/29”, “12/25”)

      For Each x In Dic
      MsgBox Dic.Item(x)
      Next
      End Sub

      ??? Is this what you are saying for steps #1 and #2??? (see what I mean, I am a VBA newbie!! Thank you for putting up with my ignorance….)

    • #1400446

      What about doing something like this Andrew:

      Code:
      Sub AutoOpen()
        [COLOR="#0000FF"]Call SkipHolidays[/COLOR]   [COLOR="#006400"] 'All the rest of the code is the same except this line to Call Private Function SkipHolidays (see below this code)[/COLOR]  
        Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
        Dim oCC As ContentControl
        If Format(Now(), "ddd") = "Mon" Then
          iDelay = 3
        Else
          iDelay = 1
        End If
        sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
        
        Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
        oCC.Range.Text = sDate
      End Sub
      

      SkipHolidays function:

      Code:
      Private Function SkipHolidays(rst As Recordset, _
       strField As String, dtmTemp As Date, intIncrement As Integer) _
       As Date
          ' Skip weekend days, and holidays in the
          ' recordset referred to by rst.
          Dim strCriteria As String
          On Error GoTo HandleErr
          ' Move up to the first Monday/last Friday if the first/last
          ' of the month was a weekend date. Then skip holidays.
          ' Repeat this entire process until you get to a weekday.
          ' Unless rst contains a row for every day in the year (!)
          ' this should finally converge on a weekday.
          Do
              Do While IsWeekend(dtmTemp)
                  dtmTemp = dtmTemp + intIncrement
              Loop
              If Not rst Is Nothing Then
                  If Len(strField) > 0 Then
                      If Left(strField, 1)  "[" Then
                          strField = "[" & strField & "]"
                      End If
                      Do
                          strCriteria = strField & _
                           " = #" & Format(dtmTemp, "mm/dd/yy") & "#"
                          rst.FindFirst strCriteria
                          If Not rst.NoMatch Then
                              dtmTemp = dtmTemp + intIncrement
                          End If
                      Loop Until rst.NoMatch
                  End If
              End If
          Loop Until Not IsWeekend(dtmTemp)
      ExitHere:
          SkipHolidays = dtmTemp
          Exit Function
      
      HandleErr:
          ' No matter what the error, just
          ' return without complaining.
          ' The worst that could happen is that the code
          ' includes a holiday as a real day, even if
          ' it's in the table.
          Resume ExitHere
      End Function
      

      When I tried this, it gave me a Compile error: User-defined type not defined ??? I have been trying to read about UDFs but can’t seem to figure out why I can’t just Call a Function??

      I read on http://support.microsoft.com/kb/858611 to click on the latest version of Microsoft ActiveX Data Object Library (for me, the latest version was 6.1). After clicking on that one, and reopening the file, the error message changed to: Compile error: Argument not optional (with Sub AutoOpen() highlighted in yellow and yellow arrow pointing at it (and Call SkipHolidays highlighted in the normal blue highlight color).

    • #1400452

      I was trying to be cheesy and came up with this really simple version (but of course, would have to updated continually or just be a super long series of If Statements): Option Explicit
      Sub AutoOpen()
      ‘Series of If Statements must be updated after the holiday in a given year passes
      Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
      Dim oCC As ContentControl
      If Now = 1 / 2 / 14 Then
      iDelay = 2 ‘ post New Years Day
      ElseIf Now = 1 / 21 / 14 Then
      iDelay = 4 ‘ post Martin Luther King’s Day
      ElseIf Now = 2 / 18 / 14 Then
      iDelay = 4 ‘ post President’s Day
      ElseIf Now = 5 / 27 / 14 Then
      iDelay = 4 ‘ post Memorial Day
      ElseIf Now = 7 / 7 / 14 Then
      iDelay = 4 ‘ post Independence Day
      ElseIf Now = 9 / 3 / 13 Then
      iDelay = 4 ‘ post Labor Day
      ElseIf Now = 12 / 2 / 13 Then
      iDelay = 5 ‘ post Thanksgiving Day Weekend
      ElseIf Now = 12 / 26 / 13 Then
      iDelay = 2 ‘ post Christmas Day
      ElseIf Format(Now(), “ddd”) = “Mon” Then
      iDelay = 3
      Else
      iDelay = 1
      End If
      sDate = Format(Now() – iDelay, “dddd, MMMM dd, yyyy”)

      Set oCC = ActiveDocument.SelectContentControlsByTitle(“MailRoom Received”).Item(1)
      oCC.Range.Text = sDate
      End Sub
      I don’t understand why this doesn’t work?? It always does 2 days ago whenever I set my system clock on one of the days specified in the If Statement (first business day post Holiday)?? If you can help me with this one, I will get out of your hair…..

    • #1400458

      That is the wrong syntax for a date and Now has a time component in it too. Try something along the lines of…

      If Date = #01/02/2014# Then

      • #1400540

        Yes!! Here is the syntax making that magic happen!!

        Code:
        Option Explicit
        
        Sub AutoOpen()
        [COLOR="#006400"]'Series of If Statements must be updated after the holiday in a given year passes[/COLOR]
          Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
          Dim oCC As ContentControl
          If Date = #1/2/2014# Then
            iDelay = 2     [COLOR="#006400"]' post New Years Day[/COLOR]
          ElseIf Date = #1/21/2014# Then
            iDelay = 4     [COLOR="#006400"]' post Martin Luther King's Day[/COLOR]
          ElseIf Date = #2/18/2014# Then
            iDelay = 4     [COLOR="#006400"]' post President's Day[/COLOR]
          ElseIf Date = #5/27/2014# Then
            iDelay = 4     [COLOR="#006400"]' post Memorial Day[/COLOR]
          ElseIf Date = #7/7/2014# Then
            iDelay = 4     [COLOR="#006400"]' post Independence Day[/COLOR]
          ElseIf Date = #9/3/2013# Then
            iDelay = 4     [COLOR="#006400"]' post Labor Day[/COLOR]
          ElseIf Date = #12/2/2013# Then
            iDelay = 5    [COLOR="#006400"] ' post Thanksgiving Day Weekend[/COLOR]
          ElseIf Date = #12/26/2013# Then
            iDelay = 2     [COLOR="#006400"]' post Christmas Day[/COLOR]
          ElseIf Format(Now(), "ddd") = "Mon" Then
            iDelay = 3
          Else
            iDelay = 1
          End If
          sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
          
          Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
          oCC.Range.Text = sDate
        End Sub
        

        And here is some code that would be dynamic. I have commented in Red to point out my questionsconcerns.
        Please let me know what you think?

        Code:
        Option Explicit
         
        Sub AutoOpen()
        Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
        Dim oCC As ContentControl
        If Date = DateSerial(Year(Date), 1, 2) Then    
            iDelay = 2 [COLOR="#006400"]' post New Years Day[/COLOR]
        ElseIf Date = NDow(Year(Date), 1, 3, vbMonday) Then   [COLOR="#FF0000"](I can see that vb[day of week] tells the 1, 3 to pick the 3rd week in the month of January since it is 1, 3 or 1(January), 3(3rd week of January)  
                                                                                           But it would have to be that line + 1 right? Because we don't work on that day so we would work on Tuesday, the day after the 3rd Monday in January (not sure the correct syntax for + 1 day???[/COLOR]    
        iDelay = 4 [COLOR="#006400"]' post Martin Luther King's Day[/COLOR]
        ElseIf Date = NDow(Year(Date), 2, 3, vbMonday) Then    [COLOR="#FF0000"](same + 1 syntax needed here)[/COLOR]
            iDelay = 4 [COLOR="#006400"]' post President's Day[/COLOR]
        ElseIf Date = NDow(Year(Date), 5, 4, vbMonday) Then     [COLOR="#FF0000"](same + 1 syntax needed here)[/COLOR]
            iDelay = 4 [COLOR="#006400"]' post Memorial Day[/COLOR]
        ElseIf Date = DateSerial(Year(Date) ,4,7) Then      [COLOR="#FF0000"] (Not sure if this one is correct--I think it should be (Year(Date), 7, 5) Then      
        (and what would happen if July 5th was a weekend?? Maybe after the line Can we put something like '& If Date = DateSerial(Year(Date), 7, 5, vbMonday) Then iDelay = 3 but If Date = DateSerial(Year(Date), 7, 5, vbTuesday) Then iDelay = 4 but If Date = DateSerial(Year(Date), 7, 5, vbWednesday) Then iDelay = 2, etc., etc., etc....... [/COLOR]
        
        or maybe I have a function or you have a function that accounts for whether or not the holiday is a weekend or not???? 
        
        [COLOR="#FF0000"]I think instead of repeating for every day of weekday, maybe we can make it say something like 'ElseIfDate = DateSerial(Year(Date), 7, 5) & WeekDay(wDate) = 1, Go to WeekDay(wDate) = 1 + 1, Then iDelay = 3 (since July 4th would fall on a Sunday and would be treated just like a normal Monday) Or if it were WeekDay(wDate) = 7, Go to WeekDay(wDate) = 7 + 2, Then iDelay = 3 (since July 4th would fall on a Saturday and need 2 days added to be treated like a normal Monday)[/COLOR]
        
        This is a function that might help:  
        ' Days of the Week: 1 = Sunday, 2 = Monday, 3 = Tuesday, etc., etc., etc.......
        Public Function IsWeekend(wDate As Date) As Integer
                If WeekDay(wDate) = 7 Or WeekDay(wDate) = 1 Then Is Weekend = 1
        End Function
        
        
            iDelay = 4 [COLOR="#006400"]' post Independence Day[/COLOR]
        ElseIf Date = NDow(Year(Date), 9, 1, vbMonday) Then   [COLOR="#FF0000"](Would this work for let's say September of 2015 where the first Monday of September is on the 7th which is actually the second week of September???)[/COLOR]
            iDelay = 4 [COLOR="#006400"]' post Labor Day[/COLOR]
        ElseIf Date = NDow(Year(Date), 11, 4, vbThursday) Then   [COLOR="#FF0000"](this would need a + 4 since we would come back to work the following Monday right??)[/COLOR]    
        iDelay = 5 [COLOR="#006400"]' post Thanksgiving Day Weekend[/COLOR]
        ElseIf Date = DateSerial(Year(Date),12,26) Then
            iDelay = 2 [COLOR="#006400"]' post Christmas Day[/COLOR]
        ElseIf WeekDay(Date) = vbMonday Then
            iDelay = 3
        Else
            iDelay = 1
        End If
        sDate = Format(Date - iDelay, "dddd, MMMM dd, yyyy")
         
        Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
        oCC.Range.Text = sDate
        End Sub
         
        ' We can generalize this to holidays that are defined as the Nth Day of some month,
        ' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
        ' The following function will return the Nth DayOfWeek for a given month and year:
        'SYntax NDOW:
        '   y = Year
        '   M = Month
        '   N = Nth day of M month
        '   DOW = Day of the week:  1 = Sunday, 2= Monday, etc.
        Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
            NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
        End Function
        
    • #1400913

      Sigh – if it was easy I would have done it for you. The first set of code needs to be much more complicated to ensure you haven’t fallen back onto a weekend or other holiday. In Australia if a holiday such as Christmas falls on a Sunday then the public holiday is held on the 26th of December. If that happens for you then the code needs to deal with that too.

      I’m not even going to start with the second code.

      I still think this problem should be solved outside the box rather than inside the box.

      Alternative Option #1 – The people who print the original output turn on the Printer Overlay function using the printer driver so the date gets added to the original output AS it is printed so when it arrives on your desk it is already date stamped with THE DAY it was printed. If your printer doesn’t support overlay files then the files could be output to Acrobat and a date overlay could be added there before output.

      Alternative Option #2 – Document has code which tracks the day the document was last opened (which is always a work day since you won’t open it on the weekend or on a holiday). Then when you open the document the code reads the previous opening date and inserts that onto the page. This method is actually dead simple to code and is far more fault tolerant than your best holiday manipulations.

      Code:
      Sub AutoOpen()
        Dim dOpenedLast As Date, oCC As ContentControl
        'read the previous date opened from the stored property
        dOpenedLast = CDate(ActiveDocument.BuiltInDocumentProperties("Subject"))
        'If the file wasn't opened earlier today then change the date shown to that date
        If dOpenedLast  Date Then
          Set oCC = ActiveDocument.SelectContentControlsByTitle("Mail Room Receive Date").Item(1)
          oCC.Range.Text = dOpenedLast
        End If
        'reset the last opened date to today
        ActiveDocument.BuiltInDocumentProperties("Subject") = Format(Date, "d MMMM yyyy")
      End Sub
      Sub AutoClose()
        ActiveDocument.Save
      End Sub
    • #1401589

      Yawn-Let me show you some VBA code to solve this schedule set up inside the box (getting confused with you “Alternative Options”??)

      #1: The people who print the original output are hospitals and doctor’s offices throughout the region (not at our company address) who mail them through the mail….These are physical papers when I get them (no software files involved here) which I remove all staples, place in printer tray, run my macro document by opening it and print 1,000 copies, for example (as long as I put a number higher than how many I put in the tray so that when they are finished, I can cancel the job with all pages having the date stamp).

      #2: Like I said in #1, no software files involved here (wish they were, hopefully someday not too far when they make an e-portal to handle all claims, but not sure if that will happen 100% since many doctors prefer to mail in paper claims, other reasons I am not aware of right now, etc., etc.)

      Code:
      Option Explicit
      Private Type typHoliday
          bIsHoliday As Boolean
          strDay As String
      End Type
      Sub AutoOpen()
          StampPreviousBusinessDay
      End Sub
      Sub StampPreviousBusinessDay()
          Dim oDate As Date
          Dim oDateStamp As Date
          Dim typDay As typHoliday
           'oDate = "12/2/2013" 'Test for Thanksgiving Day
           'oDate = "12/27/2011" 'Test for Chistmas Day Observed
           'oDate = "1/3/2011" 'Test for New Years Day Observed on Friday
           'oDate = "1/3/2017" 'Test for New Years Day Observed on Monday.
           'oDate = "5/27/14" 'Test for Memorial Day
          oDate = "5/27/14" 'Test for Independence Day Observed on Friday.
           'Unset and use this when done testing.
           oDate = Now
          oDateStamp = DateAdd("d", -1, oDate)
          Do
              typDay = fcnIsHolidayOrWeekend(oDateStamp)
              If typDay.bIsHoliday Then
                  oDateStamp = DateAdd("d", -1, oDateStamp)
              End If
          Loop While typDay.bIsHoliday
          ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1).Range.Text = Format(oDateStamp, "dddd, MMMM dd, yyyy")
      lbl_Exit:
          Exit Sub
      End Sub
      Public Function fcnIsHolidayOrWeekend(oDate As Date) As typHoliday
          Select Case True
          Case Weekday(oDate) = 1 Or Weekday(oDate) = 7
               'Plain weekend day.
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Weekend day"
          Case oDate = DateSerial(Year(oDate), 1, 1)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "New Year's Day"
          Case oDate = DateSerial(Year(oDate), 12, 25)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Christmas Day"
          Case oDate = DateSerial(Year(oDate), 7, 4)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Independence Day"
          Case oDate = DateSerial(Year(oDate), 11, 11)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Veterans Day"
          Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 11, 4, 5)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Thanksgiving Day"
          Case oDate = DateAdd("d", 1, fcnNumbered_DayOfWeek(Year(oDate), 11, 4, 5))
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Thanksgiving Recovery Day"
          Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 1, 3, 2)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Martin Luther King's Birthday"
          Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 2, 3, 2)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Presidents Day"
          Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 9, 1, 2)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Labor Day"
          Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 5, fncNumberOfNamedDaysInMonth(Year(oDate), 5, 2), 2)
              fcnIsHolidayOrWeekend.bIsHoliday = True
              fcnIsHolidayOrWeekend.strDay = "Memorial Day"
          Case Else
               'Is it an observed holiday
              If Weekday(oDate) = 2 Then
                  Select Case True
                  Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 1, 1)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "New Year's Day Observed"
                  Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 12, 25)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Christmas Day Observed"
                  Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 7, 4)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Indenpence Day Observed"
                  Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 11, 11)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Veteren's Day Observed"
                  Case Else
                      fcnIsHolidayOrWeekend.bIsHoliday = False
                      fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
                  End Select
              ElseIf Weekday(oDate) = 6 Then
                  Select Case True
                  Case oDate = "12/31/" & Year(oDate)
                      If DateAdd("d", 1, oDate) = DateSerial(Year(oDate) + 1, 1, 1) Then
                          fcnIsHolidayOrWeekend.bIsHoliday = True
                          fcnIsHolidayOrWeekend.strDay = "New Year's Day Observed"
                      End If
                  Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 12, 25)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Christmas Day Observed"
                  Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 7, 4)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Indenpence Day Observed"
                  Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 11, 11)
                      fcnIsHolidayOrWeekend.bIsHoliday = True
                      fcnIsHolidayOrWeekend.strDay = "Veteren's Day Observed"
                  Case Else
                      fcnIsHolidayOrWeekend.bIsHoliday = False
                      fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
                  End Select
              Else
                  fcnIsHolidayOrWeekend.bIsHoliday = False
                  fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
              End If
          End Select
      lbl_Exit:
          Exit Function
      End Function
      Public Function fcnNumbered_DayOfWeek(lngYear As Long, lngMonth As Long, lngNumber As Long, lngDayOfWeek As Long) As Date
          fcnNumbered_DayOfWeek = DateSerial(lngYear, lngMonth, (8 - Weekday(DateSerial(lngYear, lngMonth, 1), (lngDayOfWeek + 1) Mod 8)) + ((lngNumber - 1) * 7))
      lbl_Exit:
          Exit Function
      End Function
      Public Function fncNumberOfNamedDaysInMonth(lngYear As Long, lngMonth As Long, lngDayOfWeek As Long) As Long
          Dim lngIndex As Long, lngRange As Long
          On Error GoTo lbl_Err
          lngRange = Day(DateSerial(lngYear, lngMonth + 1, 0))
          fncNumberOfNamedDaysInMonth = 0
          For lngIndex = 1 To lngRange
              If Weekday(DateSerial(lngYear, lngMonth, lngIndex)) = lngDayOfWeek Then
                  fncNumberOfNamedDaysInMonth = fncNumberOfNamedDaysInMonth + 1
              End If
          Next lngIndex
      lbl_Exit:
          Exit Function
      lbl_Err:
          fncNumberOfNamedDaysInMonth = 0
          Resume lbl_Exit
      End Function
      
    • #1402525

      Andrew (or recent poster), did you send a reply or modify one of the messages because I got an email 7/21/13 at 2:50AM from this forum, but when I looked, my latest post still shows as the last one posted and I don’t see any post that looks differentnew”catches my eye”??

    • #1402593

      Luke

      I didn’t post a reply but there was recently some spamming messages posted on this board and the moderators such as myself try to get onto these quickly and delete the posts before many people see them.

      I haven’t replied because I don’t have the time to attempt to resolve the code you have posted. I still believe the Alternative #2 code I posted earlier will do what you require without the need to plot the working days either side of a public holiday for the next millennium. I wish you the best of luck on your quest though – it is an interesting project but not one I want to solve for free. Perhaps someone else will step in to help you out.

    • #1402596

      That’s cool….

      Alternative #2: Once again Andrew, these are physical paper claims that I can touch and feel (no software files even though I wish they could go electronic and make my life that much easier with a fully automated business process). But anyway, the code I posted does do all of that, it doesn’t matter what year, millennium, etc.

      I can’t be the only chimp who has ventured out to solve something like this……..

    • #1402614

      Alternative 2 doesn’t need the hard copies to be in electronic form. It is the code I posted to use in a docm version of the file you already had. It works by always knowing the previous work day because that was the day the document was last opened.

      The code basically doesn’t get called on weekends or holidays (because you aren’t working then) so when you open the template it recalls the date it was last opened and displays that (while storing today’s date for the next day you open the doc). This would work every working day of the year – always pointing back to the previous work day.

      It’s true that you can’t be the only person who has tried to solve this question but most others would consider the flaws in your labelling logic. If the paper arrives on your desk on a Monday, how can you be POSITIVE that it was actually received by your ‘mail box’ last Friday and not dropped in by hand sometime over the weekend or even earlier today. Giving it a stamp that could conceivably pre-date when it really arrived is IMO dangerous. Doctors work 24 hours a day and on public holidays too don’t they? Backdating to another date is an educated guess and hackable by anyone who knows your system of document handling.

      Most people would simply stamp the received paperwork on the date that you start processing it (ie today). Whilst this may not be the ACTUAL date that the mail arrived it is perfectly accurate, straightforward and how most people would do it. If you changed the label to ‘Processing commenced: ddmmyy’ then there would be no ambiguity in the labelling and your accuracy problem would be solved. If you have key performance indicators that dictate you must label the paper with the date it was received by post then it really should be processed on that day (or held in a locked repository until it is).

    • #1402685

      I see what you’re saying there and that is another way of doing it and makes sense.

      As far as the business process rules, it is what it is (that’s what my manager needs it to be). When my company receives it in the mail, that is the date that it needs the date stamp. It is held in a locked repository. I totally agree with you on the timing of when the mail could possibly be dropped off. For all we know, I could put whatever date I wanted on there and say “yeah, it was received that day”. I believe a more perfect system would be something like bar scanning all mail items when they are received (but even then if someone drops it into a drop box or drops it off on Saturday when mail room employees are enjoying there weekend off or if, like you say, they receive it earlier in the day, let’s say someone drops it off at 2am in the morning for whatever reason, then the date stamp could possibly be inaccurate). It is a flawed system, but nothing is perfect and as long as the Claims Processors process the claims early enough to avoid just meeting the deadlines, there should be no penalty assessed. Doctors could work 24 hours a day and on public holidays and when we get the claim in the mail, we will date stamp it according to when we receive those claims for procedures given to patients on holidays, weekends, 24 hours a day, whatever (you get the point, right?)…. and yes, those claims would be subjected to an imperfect systembusiness process that we have in place as of now just like all other claims, whether the procedure(s) given took place on regular business days, weekends, holidays, whatever (you know what I am saying?)…..

      Anyway, I am not sure if I have that code you are referring to…..can you post it up again or tell me what number post it is so I can check?

    • #1405165

      Thanks Andrew for all your help with this (it is really great),

      Can you please check out this post: “IIf statement for Member Eligibility” under the Access forum. I can’t seem to come up with any solution for the problem I am asking about in that, my newest post….

    Viewing 25 reply threads
    Reply To: Word 2010 macro fails when inserting text box

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: