You are here:

Excel/Need help with VBA code for Excel 2000

Advertisement


Question
QUESTION: I've created a timesheet that I'm trying to get to work properly across several versions of Excel.  I've saved it as a .xls file and when it is opened in Excel 2003 and above, it appears to work fine.  When it is opened in Excel 2000 and I attempt to enter any data, I get a Visual Basic compile error:  Named argument not found.  The part highlighted within the code is AllowFormattingCells:=True.  Once I exit from the error message and the code screen, what I entered will be there correctly but it is such a nuisance to get this error after each piece of data is entered.  The computer running Excel 2000 is also running Win XP SP3.  I assume the only way around this issue is for me to create the timesheet with code written for 2000 and use the existing timesheet for version 2003 and greater.  I've pasted a copy of the existing code below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double, icolor As Integer

Sheet2.Unprotect Password:="Timesheets101"
Application.EnableEvents = False
On Error GoTo ExitPoint

If Not Intersect(Target, Range("D11:R18,C31:D50")) Is Nothing Then
   For Each rngCell In Intersect(Target, Range("D11:R18,C31:D50")).Cells
       If rngCell.Value <> "" Then
         With rngCell
         dblTime = TimeValue(Format(Val(.Value), "00\:00"))
         dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
         dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
         .NumberFormat = "h:mm AM/PM"
         .Value = dblTime
         End With
       End If
   Next rngCell
End If

ExitPoint:
If [Q45] > 0 Then
   Range("M46:P46").Interior.ColorIndex = 6
Else
   Range("M46:P46").Interior.ColorIndex = xlNone
End If
If [Q49] > 0 Then
   Range("M50:P50").Interior.ColorIndex = 6
Else
   Range("M50:P50").Interior.ColorIndex = xlNone
End If

Application.EnableEvents = True
Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

End Sub

Can you please convert or fix the code above so that it works with Excel 2000?  I have limited knowledge of Visual Basic code and I've had help along the way getting this far.  Any assistance you can offer is greatly appreciated!

ANSWER: Hi Vickie,

I don't believe Excel 2000 supported the AllowFormattingCells argument for the worksheet Protect method.  I suggest you do a test in your code for the Excel version like this:

Dim myExcelVersion As Integer
myExcelVersion = Val(Application.Version)
If myExcelVersion > 9 Then
  Worksheets("mySheet").Protect  Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
Else
  Worksheets("mySheet").Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

Note that Application.Version = 9 for Excel 2000.  Later versions are higher numbers.

I hope you find this helpful.

Damon

---------- FOLLOW-UP ----------

QUESTION: Wow...such a fast response.  Please forgive me for my ignorance but WHERE within the current code do I paste your answer?  Also, if this additional code works, does that mean I DON'T need to create 2 versions of the timesheet?  This will be awesome if true.  Thanks again!

ANSWER: Hi again Vickie,

Yes, the idea is to make the same code handle both versions so you only need one version of the timesheet.

Sorry that my explanation wasn't quite sufficient.  Simply replace your code

Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

with

Dim myExcelVersion As Integer
myExcelVersion = Val(Application.Version)
If myExcelVersion > 9 Then
 Sheet2.Protect  Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
Else
 Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If

Again, let me know if any problems.

Damon

---------- FOLLOW-UP ----------

QUESTION: Okay, so I did as instructed and replaced my code with your code and tested it.  Excel 2000 still stops on AllowFormattingCells:=True and gives an error.  Is there any other tweaking to the code that can be done?  Thanks.

Answer
Hi again Vickie,

Okay, here's a likely fix.  I can't be sure because I no longer have Excel 2000, but please give this a try.  Change Sheet2.Protect ... to Worksheets("Sheet2").Protect, both instances.  This should keep Excel from being able to catch the bad parameter during the source code scan so that it doesn't produce an error on the code line that it hasn't executed due to the IF statement.

Again, let me know.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.