Results 1 to 7 of 7

Thread: Automate months

  1. #1
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I have a template which has various bookmarks, labels and text boxes.

    I want a user to be able to insert a month into a text box (which is contained within a userform) - for example January and then for the following two months to automatically populate within the form - February and March. Can anyone help?

    Thanks

    Angie

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Angie,

    To get the current month, you can use:

    MonthName(Month(Now))

    and then increment that up to get subsequent months - like:

    MonthName(Month(Now)+1)
    and
    MonthName(Month(Now)+2)

    Gary

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Actually, you can't simply rely on MonthName as described in my previous post, because incrementing the month by one or two will cause an error in November and/or December. So you need to use code like this:

    Code:
       Dim MonthNum As Long
       Dim MonthPlus1 As String
       Dim MonthPlus2 As String
       MonthNum = Month(Now)
       Select Case MonthNum
          Case Is < 11
             MonthPlus1 = MonthName(Month(Now) + 1)
             MonthPlus2 = MonthName(Month(Now) + 2)
          Case 11
             MonthPlus1 = MonthName(Month(Now) + 1)
             MonthPlus2 = MonthName(Month(1))
          Case 12
             MonthPlus1 = MonthName(Month(1))
             MonthPlus2 = MonthName(Month(2))
       End Select
    and then assign the values for MonthPlus1 and MonthPlus2 to the appropriate labels or textboxes within the form.

    Gary

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Gary's method will work but will need to be modified if the user is to input the starting month rather than accept today's date. This then brings in the possibility that they won't type a month name where you want them to.

    Since you are using a userform I would do this using a combo box to allow the user to select the month from a list you provide. That way you can restrict the user to only entering a valid month name and you can also harvest their choice and reuse the same list for the other text boxes.
    The following code assumes that your user form has:
    • a month combo box called cb1
    • the following month text box called tb1
    • the subsequent month text box called tb2

    Code:
    Private Sub cb1_Change()
      Dim x As Integer
      x = cb1.ListIndex
      Me.tb1 = Me.cb1.List((x + 1) Mod 12)
      Me.tb2 = Me.cb1.List((x + 2) Mod 12)
    End Sub
    
    Private Sub UserForm_Initialize()
      With Me.cb1
        .AddItem "Jan"
        .AddItem "Feb"
        .AddItem "Mar"
        .AddItem "Apr"
        .AddItem "May"
        .AddItem "Jun"
        .AddItem "Jul"
        .AddItem "Aug"
        .AddItem "Sep"
        .AddItem "Oct"
        .AddItem "Nov"
        .AddItem "Dec"
      End With
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    One could, of course, use two copies of the 'Calculate a day, date, month and year, using n months delay' example (one for each month, with delay values of 1 & 2, respectively) from my Microsoft Word Date Calculation Tutorial in post 249902

    With that approach, no macros are required. All you need to do is to ensure the formfield has an appropriate bookmark name (with which you replace the 'DATE' reference in the date calculation field code) and to set the formfield's properties to 'calculate on exit'.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    If the template has the month input in a userform textbox I don't think the field suggestion will work directly. However you might be able to use a single date input field in the userform and then make use of that information with fields in the actual document assuming the userform doesn't need to show the two subsequent dates.

    If the OP is using formfields rather than a userform then Paul's (macropod's) solution will certainly be the most robust way to do it.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I used Andrew's method and it worked just great. Thanks everyone for all your help.

    Angie

Posting Permissions

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