Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '--------------------------------------------------------------------------- ------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '--------------------------------------------------------------------------- ------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with XY Scatter graphs Excel 2003/Office 2003 | Excel Discussion (Misc queries) | |||
Trouble when trying to open a file excel 2007 in excel 2003 | Excel Discussion (Misc queries) | |||
Trouble downloading KB918419 for Excel 2003 | Excel Discussion (Misc queries) | |||
trouble with Excel 2003 and dde | Excel Discussion (Misc queries) | |||
Trouble w/ ActiveX Controls (no userform) Excel 2002. | Excel Programming |