VBA trouble when using Excel 2003 vs Excel 2002
 

I cannot use a template that has worked for excel 2000 and excel 2002 when i
switch to excel 2003. The template is used to format a worksheet with
proper headers and footers and tab names based on the input received through
a text box.

The first problem: The textbox is not working. Meaning - when i type
something in the textbox on a form and hit OK, the vba error checking comes
up and says that there is a "COMPILE ERROR: Can't find project or library".
This happens in the SecNum = TextBox1.Value part

The second problem: I have a BeforePrint routine that will place headers
and footers in the document. When I try to do a print preview, I get the
same message at the point where I try to specify Chr(10) as a carriage
return.

What can be happening here? I have included the VBA code...

Problem 1 code
'---------------------------------------------------------------------------
---
Private Sub CommandButton1_Click()
SecNum = TextBox1.Value
NumShts = 5

NumShts = Worksheets.Count
J = 65
For i = 1 To NumShts
origtext = Sheets(i).Range("A1").Value
If origtext = "" Then
GoTo endline
Else
NewText = "Exhibit " & SecNum & "-" & Chr(J)
Sheets(i).Range("a1").Value = NewText
Sheets(i).Name = SecNum & "-" & Chr(J)
J = J + 1
End If
endline: Next i

UserForm1.Hide
Sheets(1).Select
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub Label3_Click()

End Sub
'---------------------------------------------------------------------------
---


Problem 2 code
'---------------------------------------------------------------------------
--------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & ActiveWorkbook.FullName & Chr(10) & "&D &T"
.RightFooter = "&""ZapfHumnst Ult BT,Ultra Bold""Company Name"
.RightHeader = "&A &P"
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = True Then Sheets("<---LastPage").Range("F18").Value = 1
End Sub

Private Sub Workbook_Open()
If Sheets("<---LastPage").Range("F18").Value = 0 Then UserForm1.Show
End Sub
'---------------------------------------------------------------------------
------------

VBA trouble when using Excel 2003 vs Excel 2002
 

in the VBE, make the workbook with the code the active project (select it in
the project explorer). Go to Tools=References in the menu and see if you
have any references shown at the top as MISSING.

If so, you need to either find the referenced file on your computer, or
remove the reference if your are not using it.

--
Regards,
Tom Ogilvy

"Steven Parker" wrote in message
...
I cannot use a template that has worked for excel 2000 and excel 2002 when
i
switch to excel 2003. The template is used to format a worksheet with
proper headers and footers and tab names based on the input received
through
a text box.

The first problem: The textbox is not working. Meaning - when i type
something in the textbox on a form and hit OK, the vba error checking
comes
up and says that there is a "COMPILE ERROR: Can't find project or
library".
This happens in the SecNum = TextBox1.Value part

The second problem: I have a BeforePrint routine that will place headers
and footers in the document. When I try to do a print preview, I get the
same message at the point where I try to specify Chr(10) as a carriage
return.

What can be happening here? I have included the VBA code...

Problem 1 code

'---------------------------------------------------------------------------
---
Private Sub CommandButton1_Click()
SecNum = TextBox1.Value
NumShts = 5

NumShts = Worksheets.Count
J = 65
For i = 1 To NumShts
origtext = Sheets(i).Range("A1").Value
If origtext = "" Then
GoTo endline
Else
NewText = "Exhibit " & SecNum & "-" & Chr(J)
Sheets(i).Range("a1").Value = NewText
Sheets(i).Name = SecNum & "-" & Chr(J)
J = J + 1
End If
endline: Next i

UserForm1.Hide
Sheets(1).Select
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub Label3_Click()

End Sub

'---------------------------------------------------------------------------
---


Problem 2 code

'---------------------------------------------------------------------------
--------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & ActiveWorkbook.FullName & Chr(10) & "&D &T"
.RightFooter = "&""ZapfHumnst Ult BT,Ultra Bold""Company Name"
.RightHeader = "&A &P"
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI = True Then Sheets("<---LastPage").Range("F18").Value = 1
End Sub

Private Sub Workbook_Open()
If Sheets("<---LastPage").Range("F18").Value = 0 Then UserForm1.Show
End Sub

'---------------------------------------------------------------------------
------------

VBA trouble when using Excel 2003 vs Excel 2002
 

Tom:

Why is this happening? I notice this issue quite frequently here, and am wondering if MS
blew it when they released Excel 2003. This kind of reverse compatibility issue should not
happen in a well designed upgrade, should it?

Best

John Baker

"Tom Ogilvy" wrote:

in the VBE, make the workbook with the code the active project (select it in
the project explorer). Go to Tools=References in the menu and see if you
have any references shown at the top as MISSING.

If so, you need to either find the referenced file on your computer, or
remove the reference if your are not using it.