I am currently in a pickle. I have a file that is recommended to have
12 other files to be open at the same time. I have created a UserForm1
that allows a user to click on either open "All files" or allows
them to click each checkbox individually. That is not the issue. The
issue is, these files are spread out among many different directories
and every quarter those file directories change. They are minor
changes like changing from a 0305 folder to a 0605 folder but the
changes do occur. I need to find a way, on the same UserForm1, to have
a user be able to enter one of 4 dates in a year (3/31/0?, 6/30/0?,
9/30/0?, 12/31/0?). After entering the date, I am looking for it to
automatically save the date so a user would not have to enter the date
in every time they open the file, and it would automatically change the
file directory these checkboxes are linked to.

For example

Private Sub CheckBox1_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 1"
End Sub

Private Sub CheckBox2_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 2"
End Sub

Private Sub CheckBox3_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 3"
End Sub

If user enters date 6/30/05 - - - Files changes to L:\ACCT\SA\0605\SR
(Stays the same)

If user enters date 9/30/05 - - - Files change to L:\ACCT\SA\0905\SR

If user enters date 12/31/05 - - - Files change to L:\ACCT\SA\1205\SR

If user enters date 3/31/06 - - - Files change to L:\ACCT\SA\0306\SR


Any help would be greatly appreciated since I am lost.

Hi,
Code something like this:

Dim v As Variant
Dim MMYY As String
Dim MyDate As String

MyDate = "6/30/05"

v = Split(MyDate, "/")
MMYY = Format(v(0) & v(2), "0000")

Workbooks.Open Filename:="L:\ACCT\SA\" & MMYY & "\SR\File 2"


HTH

"jdawg" wrote:

I am currently in a pickle. I have a file that is recommended to have
12 other files to be open at the same time. I have created a UserForm1
that allows a user to click on either open "All files" or allows
them to click each checkbox individually. That is not the issue. The
issue is, these files are spread out among many different directories
and every quarter those file directories change. They are minor
changes like changing from a 0305 folder to a 0605 folder but the
changes do occur. I need to find a way, on the same UserForm1, to have
a user be able to enter one of 4 dates in a year (3/31/0?, 6/30/0?,
9/30/0?, 12/31/0?). After entering the date, I am looking for it to
automatically save the date so a user would not have to enter the date
in every time they open the file, and it would automatically change the
file directory these checkboxes are linked to.

For example

Private Sub CheckBox1_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 1"
End Sub

Private Sub CheckBox2_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 2"
End Sub

Private Sub CheckBox3_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 3"
End Sub

If user enters date 6/30/05 - - - Files changes to L:\ACCT\SA\0605\SR
(Stays the same)

If user enters date 9/30/05 - - - Files change to L:\ACCT\SA\0905\SR

If user enters date 12/31/05 - - - Files change to L:\ACCT\SA\1205\SR

If user enters date 3/31/06 - - - Files change to L:\ACCT\SA\0306\SR


Any help would be greatly appreciated since I am lost.

Using Split eliminates use in xl97 and earlier. Plus, it is an unnecessary
step:

MyDate = "6/30/05"
? format(MyDate,"MMYY")
0605


--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Hi,
Code something like this:

Dim v As Variant
Dim MMYY As String
Dim MyDate As String

MyDate = "6/30/05"

v = Split(MyDate, "/")
MMYY = Format(v(0) & v(2), "0000")

Workbooks.Open Filename:="L:\ACCT\SA\" & MMYY & "\SR\File 2"


HTH

"jdawg" wrote:

I am currently in a pickle. I have a file that is recommended to have
12 other files to be open at the same time. I have created a UserForm1
that allows a user to click on either open "All files" or allows
them to click each checkbox individually. That is not the issue. The
issue is, these files are spread out among many different directories
and every quarter those file directories change. They are minor
changes like changing from a 0305 folder to a 0605 folder but the
changes do occur. I need to find a way, on the same UserForm1, to have
a user be able to enter one of 4 dates in a year (3/31/0?, 6/30/0?,
9/30/0?, 12/31/0?). After entering the date, I am looking for it to
automatically save the date so a user would not have to enter the date
in every time they open the file, and it would automatically change the
file directory these checkboxes are linked to.

For example

Private Sub CheckBox1_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 1"
End Sub

Private Sub CheckBox2_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 2"
End Sub

Private Sub CheckBox3_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 3"
End Sub

If user enters date 6/30/05 - - - Files changes to L:\ACCT\SA\0605\SR
(Stays the same)

If user enters date 9/30/05 - - - Files change to L:\ACCT\SA\0905\SR

If user enters date 12/31/05 - - - Files change to L:\ACCT\SA\1205\SR

If user enters date 3/31/06 - - - Files change to L:\ACCT\SA\0306\SR


Any help would be greatly appreciated since I am lost.

Thanks Tom. Why Do I make life complicated!

"Tom Ogilvy" wrote:

Using Split eliminates use in xl97 and earlier. Plus, it is an unnecessary
step:

MyDate = "6/30/05"
? format(MyDate,"MMYY")
0605


--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Hi,
Code something like this:

Dim v As Variant
Dim MMYY As String
Dim MyDate As String

MyDate = "6/30/05"

v = Split(MyDate, "/")
MMYY = Format(v(0) & v(2), "0000")

Workbooks.Open Filename:="L:\ACCT\SA\" & MMYY & "\SR\File 2"


HTH

"jdawg" wrote:

I am currently in a pickle. I have a file that is recommended to have
12 other files to be open at the same time. I have created a UserForm1
that allows a user to click on either open "All files" or allows
them to click each checkbox individually. That is not the issue. The
issue is, these files are spread out among many different directories
and every quarter those file directories change. They are minor
changes like changing from a 0305 folder to a 0605 folder but the
changes do occur. I need to find a way, on the same UserForm1, to have
a user be able to enter one of 4 dates in a year (3/31/0?, 6/30/0?,
9/30/0?, 12/31/0?). After entering the date, I am looking for it to
automatically save the date so a user would not have to enter the date
in every time they open the file, and it would automatically change the
file directory these checkboxes are linked to.

For example

Private Sub CheckBox1_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 1"
End Sub

Private Sub CheckBox2_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 2"
End Sub

Private Sub CheckBox3_Click()
Workbooks.Open Filename:="L:\ACCT\SA\0605\SR\File 3"
End Sub

If user enters date 6/30/05 - - - Files changes to L:\ACCT\SA\0605\SR
(Stays the same)

If user enters date 9/30/05 - - - Files change to L:\ACCT\SA\0905\SR

If user enters date 12/31/05 - - - Files change to L:\ACCT\SA\1205\SR

If user enters date 3/31/06 - - - Files change to L:\ACCT\SA\0306\SR


Any help would be greatly appreciated since I am lost.