How to do a If Then macro for variables setting dates.

[jonathanwang003]

Hi Guys,

I feel as if I have a simple issue but a sophisticated answer. So I have excel files I need to open that are named by month and year. The goal of this macro is that when I press a button, it automatically opens the file. There are 2 combo boxes, one for "Month" and one for "Year." That way, the user selects the date they want, press a button, and there we have it.

So here's a slight issue, I need to open the month prior to the set date in the combo boxes. So if I select February 2014, I need to open last months file, January 2014. So now, when I select January 2014, it opens December 2014! I need it to open December 2013, the year before, since January is the beginning of the year.

So here's the variables I set and defined, but I can't seem to get January to work properly. I believe I need some sort of IF/Then to set the year back one year if the month of January is chosen. So if January is not chosen, the following code works.

Mo1 is the prior month.
Mo2 is the current month.

This code doesn't work right because later on I have to save the file as the current month. Example: when I open up December 2013 file, I need to save it as January 2014, but the variables will save it as January 2013... so the same problem in a different way.

Public Box1 As Object
Public Box2 As Object
Public Box3 As Object
Public Mo1 As String
Public Mo2 As String
Public Yr1 As String
Public Yr2 As String

Set Box1 = [Sheet1].ComboBox1
Set Box2 = [Sheet1].ComboBox2

'Converts the date entries to the date _
abbreviations for the file name.
If Box1 = "January" Then Yr1 = Yr2 - 1
If Box1 = "January" Then Mo1 = "Dec"
If Box1 = "February" Then Mo1 = "Jan"
If Box1 = "March" Then Mo1 = "Feb"
If Box1 = "April" Then Mo1 = "Mar"
If Box1 = "May" Then Mo1 = "Apr"
If Box1 = "June" Then Mo1 = "May"
If Box1 = "July" Then Mo1 = "Jun"
If Box1 = "August" Then Mo1 = "Jul"
If Box1 = "September" Then Mo1 = "Aug"
If Box1 = "October" Then Mo1 = "Sep"
If Box1 = "November" Then Mo1 = "Oct"
If Box1 = "December" Then Mo1 = "Nov"
    
If Box1 = "January" Then Mo2 = "Jan"
If Box1 = "February" Then Mo2 = "Feb"
If Box1 = "March" Then Mo2 = "Mar"
If Box1 = "April" Then Mo2 = "Apr"
If Box1 = "May" Then Mo2 = "May"
If Box1 = "June" Then Mo2 = "Jun"
If Box1 = "July" Then Mo2 = "Jul"
If Box1 = "August" Then Mo2 = "Aug"
If Box1 = "September" Then Mo2 = "Sep"
If Box1 = "October" Then Mo2 = "Oct"
If Box1 = "November" Then Mo2 = "Nov"
If Box1 = "December" Then Mo2 = "Dec"

 

[dmt32]

Hi,
no sophisticated answer required you just need to work with dates. A simple function should do what you want.

Have play with following & see if helps. I have assumed that your comboboxes have their default names & Combobox1 is for the months & ComboBox2 is for the Years - You will need to amend code as required.

Dim YearMonth As Variant


Private Sub CommandButton1_Click()
'Open Button
YearMonth = GetDate(Me.ComboBox2.Value, Me.ComboBox1.ListIndex + 1, xlPrevious)
MsgBox Format(YearMonth, "mmm yyyy")
End Sub


Private Sub CommandButton2_Click()
'Save Button
YearMonth = GetDate(Me.ComboBox2.Value, Me.ComboBox1.ListIndex + 1, xlNext)
MsgBox Format(YearMonth, "mmm yyyy")
End Sub


Function GetDate(sYear As Long, ByVal sMonth As Integer, adj As Integer) As Variant
    sMonth = sMonth + IIf(adj = xlPrevious, -1, 0)
    GetDate = DateSerial(sYear, sMonth, 1)
End Function

I have used a msgbox to display the result but you would use the YearMonth variable in your Open & Save Code formatted as required i.e "Jan 2014" would be as this example Format(YearMonth, "mmm yyyy")

Hope Helpful

Dave

 

[jonathanwang003]

Hi Dave,

Thanks for responding. I'm new to macros so my knowledge is very limited. Forgive me. Let me paste what I have to see if you could help me bridge the two.

Public Box1 As Object
Public Box2 As Object
Public Mo1 As String
Public Mo2 As String
Public Yr1 As String
Public Yr2 As String
Public filename1 As String
Public filename2 As String

Sub FileNames()

    Yr2 = [Sheet1].ComboBox2.Value
    Yr1 = Yr2 - 1

'Converts the date entries to the date _
abbreviations for the file name.
If Box1 = "January" Then Yr1 = Yr2 - 1
If Box1 = "January" Then Mo1 = "Dec"
If Box1 = "February" Then Mo1 = "Jan"
If Box1 = "March" Then Mo1 = "Feb"
If Box1 = "April" Then Mo1 = "Mar"
If Box1 = "May" Then Mo1 = "Apr"
If Box1 = "June" Then Mo1 = "May"
If Box1 = "July" Then Mo1 = "Jun"
If Box1 = "August" Then Mo1 = "Jul"
If Box1 = "September" Then Mo1 = "Aug"
If Box1 = "October" Then Mo1 = "Sep"
If Box1 = "November" Then Mo1 = "Oct"
If Box1 = "December" Then Mo1 = "Nov"
    
If Box1 = "January" Then Mo2 = "Jan"
If Box1 = "February" Then Mo2 = "Feb"
If Box1 = "March" Then Mo2 = "Mar"
If Box1 = "April" Then Mo2 = "Apr"
If Box1 = "May" Then Mo2 = "May"
If Box1 = "June" Then Mo2 = "Jun"
If Box1 = "July" Then Mo2 = "Jul"
If Box1 = "August" Then Mo2 = "Aug"
If Box1 = "September" Then Mo2 = "Sep"
If Box1 = "October" Then Mo2 = "Oct"
If Box1 = "November" Then Mo2 = "Nov"
If Box1 = "December" Then Mo2 = "Dec"
        
    'Construct file name.
If Box1 = "January" Then filename1 = Mo1 & " " & Yr1
    filename1 = Mo1 & " " & Yr2
    filename2 = Mo2 & " " & Yr2

I thought adding the If/Then would work, but the code runs as if it isn't even there. Filename1 is the old document name I need to open, Filename2 is the new document name i wish to save as.
So I select January 2014 which needs to open December 2013 and save as January 2014.

But the problem is, I selected January 2014, but it opens December 2014! When I run the macro line by line (F8), the IF/Then works, but once it gets to the filename1 variable definition, it reverts back as if the If/Then never happened. How do I write it so it does, "If January, then Filename1 uses Yr1, otherwise use Yr2?"

 

[dmt32]

Did you try my suggested code?

Dave

 

[jonathanwang003]

Hi Dave,

Sorry for the late response. I just did and it worked perfectly Thanks so much!

 

[dmt32]

Hi Dave,

Sorry for the late response. I just did and it worked perfectly Thanks so much!

welcome thanks for feedback

Dave