Thanks for your insight, it has been very helpfull. I think I'll work on
changing the check boxes to options buttons in the future. I might also make
one of the variables a list box. This should help reinforce what you helped
me learn today.

Thanks again for your help.
--
Regards,

timmulla


"LabElf" wrote:

Timmulla -

Sorry the "ElseIf" construct didn't work in your Excel 2000 macro. I
checked some macro code I have, and I am able to use the If-ElseIf-Else-End
If construct without error in Excel 2000 and Excel 2003. If you can't use
ElseIf, the series of If statements you have is probably the next best thing.
Note that if you have more than one checkbox checked, the ElseIf block will
pick up the first one and not check the subsequent ones. The series of If
statements will check all the boxes and you will get the result from the last
one. You could write the full form of a nested If, e.g.,
If optYear0.Value = True Then
iYearIndex = 0
Else
If optYear1.Value = True Then
iYearIndex = 1
Else
If optYear2.Value = True Then
iYearIndex = 2
Else
iYearIndex = -1
End If
End If
End If
but you might run into limits to the nesting depth and there's more chance
of getting your "If"s and "End If"s unbalanced. (I skipped option boxes for
3, 4 and 5 in my example above.)

I don't know if this is the best book available, but when I started with my
project I got "Using Excel Visual Basic for Applications", 2nd Edition, by
Jeff Webb, published 1996 by Que Corporation. It has some errors, but then,
nearly all do. Use at your own risk, no models under 18 were used etc. ...

I do most of my work with full-on Visual Basic, so I'm not that familiar
with how certain things are done in the macro world, but that book does tend
to lean toward the macro approach. I looked in there for a way to have only
one check box at a time be true, and the answer is to use OptionButtons
instead. Put each group of buttons that you want to be mutually exclusive
into a group box (add the group box so it encloses the OptionButtons). Excel
will allow only one button in the group to be True.
Even better, you should look into using drop-down list boxes. My book says
they can be placed on worksheets, charts, and dialog sheets, so you should be
able to use them. You could load them up with your years, months, and
programs, and just use the selected values - no need for complex logic to
figure out what was selected.
Alternatively, you could attach each check box button to subroutine that
would set the value of a variable (which would have to be global to your
module).

Contact me if you get the book and want to know what errors I've found so
far. You can respond to this thread, or email me at
where first = jim and last = rodarmel.

Finally, you might mark my response as being helpful in the forum here so
others will see it as an answer (click on the post that was useful, and click
the Yes button next to the "Was this post helpful to you?" question.
--
In theory, there is no difference between theory and practice; in practice,
there is.


"timmulla" wrote:

Hi LabElf,

I had already changed the names of the checkboxes. I always seem to have
problems using the "ElseIf" statement at work. I study excel programming at
home for excel 2003 , however, at work it doesn't seem to work for me (I
believe we use excel 2000 at work). Well anyways, I eventualy got my macro
to work properly using the following code for my statements:

Dim iYearIndex As Integer
iYearIndex = -1

If optYear0.Value = True Then iYearIndex = 0

If optYear1.Value = True Then iYearIndex = 1

If optYear2.Value = True Then iYearIndex = 2

If optYear3.Value = True Then iYearIndex = 3

If optYear4.Value = True Then iYearIndex = 4

If optYear5.Value = True Then iYearIndex = 5

I know it might not be the best way to accomplish my task, but until I
figure out how to do it better, it will have to do. I still need to figure
out how to let only one check box be true at a time. Could you recommend any
good books on excel programming? I'm looking for good intermediate level
books b/c I don't have a programming background (finance/accounting). Or any
excel programming classes offered by colleges or computer schools?

I greatly appreciated your help! I learned a tremendous amount today.

Regards,

timmulla


"timmulla" wrote:

Everybody thanks for your help! LabElf, I really liked the way you wrote
your code to solve my problem, so I decided to use your method. However,
after writing my code I'm getting errors pertaining to my If, ElseIf
statements. The error says "compile error - Else without If. If you don't
mind, and have a free moment could you please look over my code. You will
also notice that I had to add another variable (program) to file path naming
convention.

My code is:

Private Sub CommandButton1_Click()

Dim strYear(0 To 5) As String
strYear(0) = "2005"
strYear(1) = "2006"
strYear(2) = "2007"
strYear(3) = "2008"
strYear(4) = "2009"
strYear(5) = "2010"

Dim strMonth(0 To 11) As String
strMonth(0) = "September"
strMonth(1) = "October"
strMonth(2) = "Novemeber"
strMonth(3) = "December"
strMonth(4) = "January"
strMonth(5) = "February"
strMonth(6) = "March"
strMonth(7) = "April"
strMonth(8) = "May"
strMonth(9) = "June"
strMonth(10) = "July"
strMonth(11) = "August"

Dim strProgram(0 To 5) As String
strProgram(0) = "54000"
strProgram(1) = "54001"
strProgram(2) = "54002"
strProgram(3) = "54010"
strProgram(4) = "54020"
strProgram(5) = "54040"

Dim IYearIndex As Integer
IYearIndex = -1

If optYear0.Value = True Then IYearIndex = 0
ElseIf optYear1.Value = True Then
IYearIndex = 1
ElseIf optYear2.Value = True Then
IYearIndex = 2
ElseIf optYear3.Value = True Then
IYearIndex = 3
ElseIf optYear4.Value = True Then
IYearIndex = 4
ElseIf optYear5.Value = True Then
IYearIndex = 5

End If

Dim IMonthIndex As Integer
IMonthIndex = -1

If chkMonth0.Value = True Then IMonthIndex = 0
ElseIf chkMonth1.Value = True Then
IMonthIndex = 1
ElseIf chkMonth2.Value = True Then
IMonthIndex = 2
ElseIf chkMonth3.Value = True Then
IMonthIndex = 3
ElseIf chkMonth4.Value = True Then
IMonthIndex = 4
ElseIf chkMonth5.Value = True Then
IMonthIndex = 5
ElseIf chkMonth6.Value = True Then
IMonthIndex = 6
ElseIf chkMonth7.Value = True Then
IMonthIndex = 7
ElseIf chkMonth8.Value = True Then
IMonthIndex = 8
ElseIf chkMonth9.Value = True Then
IMonthIndex = 9
ElseIf chkMonth10.Value = True Then
IMonthIndex = 10
ElseIf chkMonth11.Value = True Then
IMonthIndex = 11

End If

Dim IProgramIndex As Integer
IProgramIndex = -1

If chkProgram0.Value = True Then IProgramIndex = 0
ElseIf chkProgram1.Value = True Then
IProgramIndex = 1
ElseIf chkProgram2.Value = True Then
IProgramIndex = 2
ElseIf chkProgram3.Value = True Then
IProgramIndex = 3
ElseIf chkProgram4.Value = True Then
IProgramIndex = 4
ElseIf chkProgram5.Value = True Then
IProgramIndex = 5

End If


Dim strRoot As String
strRoot = "H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY" &
strYear(IYearIndex) & "uploaded files"

Dim strUpload As String
strUpload = strRoot & "\" & strMonth(IMonthIndex) & " " &
strYear(IYearIndex) & strProgram(IProgramIndex) & "GL UPLOAD.csv"

ChDir strRoot
ActiveWorkbook.SaveAs Filename:=strUpload, FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False



End Sub


I really appreciate your help!

--
Regards,

timmulla