ADO 2.7 & ADO 2.8 beforeprint

I have an excell workbook that I transfer information to access with. From
other threads, I have been told to check the Microsoft ActiveX data object
references to allow for the connection. I did this (using version 2.8). My
customer was getting errors from a hidden module so I had them search for the
latest version on their machine-2.7. I changed the references in my workbook
to 2.7 and now the macros run perfectly on there machine.

Now the problem. Since changing the version to 2.7, a macro that I use in
"This Workbook" to check for information from the user before printing does
not work. When printing, it is like the code in This Workbook is not even
there. Here's the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Header As String, Response As Integer, I As Integer
Header = Range("D2").Value
With ActiveSheet.PageSetup
.CenterHeader = "&12SI Body & Options" & Chr(10) & "APS Vehicle# " &
Header
End With
Response = MsgBox("Is this a Shop Copy or Office Copy? Click Yes for Shop
Copy, No for Office Copy, or Cancel to cancel printng", vbYesNoCancel)
If Response = vbYes Then
ActiveSheet.Unprotect ("123")
For I = 9 To 56
Range("E" & I).Select
Selection.ClearContents
Next I
Range("A1").Select
ActiveSheet.Protect ("123"), DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFormattingCells:=True
End If

Any ideas why this is happening or what the simplest way to have this
workbook do all of the things I need?

Have you put a break in the code to see that it gets entered?

Check the VBE Tools>References, and check there are no missing references
(like ADO 2.8?), and if so, get rid of them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JCanyoneer" <[email protected]> wrote in message
news:[email protected]...
> I have an excell workbook that I transfer information to access with. From
> other threads, I have been told to check the Microsoft ActiveX data object
> references to allow for the connection. I did this (using version 2.8). My
> customer was getting errors from a hidden module so I had them search for
the
> latest version on their machine-2.7. I changed the references in my
workbook
> to 2.7 and now the macros run perfectly on there machine.
>
> Now the problem. Since changing the version to 2.7, a macro that I use in
> "This Workbook" to check for information from the user before printing
does
> not work. When printing, it is like the code in This Workbook is not even
> there. Here's the code:
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim Header As String, Response As Integer, I As Integer
> Header = Range("D2").Value
> With ActiveSheet.PageSetup
> .CenterHeader = "&12SI Body & Options" & Chr(10) & "APS Vehicle# "
&
> Header
> End With
> Response = MsgBox("Is this a Shop Copy or Office Copy? Click Yes for Shop
> Copy, No for Office Copy, or Cancel to cancel printng", vbYesNoCancel)
> If Response = vbYes Then
> ActiveSheet.Unprotect ("123")
> For I = 9 To 56
> Range("E" & I).Select
> Selection.ClearContents
> Next I
> Range("A1").Select
> ActiveSheet.Protect ("123"), DrawingObjects:=True, Contents:=True,
> Scenarios:=True, AllowFormattingCells:=True
> End If
>
> Any ideas why this is happening or what the simplest way to have this
> workbook do all of the things I need?

I'm not sure what you mean on the "break in code". Is this to see if This
Workbook get entered? How do I break the code? End?

If any of the ADO 2.8 is checked, the errors occur on my customer's machine.
Here is what is checked in my references:

VB for A
ME 10.0 Object Library
OLE Auto
M Forms 2.0 Object Library
M Office 10.0 Object Library
M Activex Data Objects 2.7 Library

When I check the 2.7 Recordset Library, The next time I check, it has become
2.8 and error on my customer's machine.

I have tried changinge these all back to 2.8 but the before print is being
skipped then too.


"Bob Phillips" wrote:

> Have you put a break in the code to see that it gets entered?
>
> Check the VBE Tools>References, and check there are no missing references
> (like ADO 2.8?), and if so, get rid of them.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JCanyoneer" <[email protected]> wrote in message
> news:[email protected]...
> > I have an excell workbook that I transfer information to access with. From
> > other threads, I have been told to check the Microsoft ActiveX data object
> > references to allow for the connection. I did this (using version 2.8). My
> > customer was getting errors from a hidden module so I had them search for
> the
> > latest version on their machine-2.7. I changed the references in my
> workbook
> > to 2.7 and now the macros run perfectly on there machine.
> >
> > Now the problem. Since changing the version to 2.7, a macro that I use in
> > "This Workbook" to check for information from the user before printing
> does
> > not work. When printing, it is like the code in This Workbook is not even
> > there. Here's the code:
> >
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > Dim Header As String, Response As Integer, I As Integer
> > Header = Range("D2").Value
> > With ActiveSheet.PageSetup
> > .CenterHeader = "&12SI Body & Options" & Chr(10) & "APS Vehicle# "
> &
> > Header
> > End With
> > Response = MsgBox("Is this a Shop Copy or Office Copy? Click Yes for Shop
> > Copy, No for Office Copy, or Cancel to cancel printng", vbYesNoCancel)
> > If Response = vbYes Then
> > ActiveSheet.Unprotect ("123")
> > For I = 9 To 56
> > Range("E" & I).Select
> > Selection.ClearContents
> > Next I
> > Range("A1").Select
> > ActiveSheet.Protect ("123"), DrawingObjects:=True, Contents:=True,
> > Scenarios:=True, AllowFormattingCells:=True
> > End If
> >
> > Any ideas why this is happening or what the simplest way to have this
> > workbook do all of the things I need?
>
>
>

In the VBE, open the ThisWorkbook module, then select a code line in the
BeforePrint and hit the F9 key. It should change that line to a brown
background. Then when you print something (or print preview), the code
should stop at that line.

As to references, is there anything saying MISSING in the list?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JCanyoneer" <[email protected]> wrote in message
news:[email protected]...
> I'm not sure what you mean on the "break in code". Is this to see if This
> Workbook get entered? How do I break the code? End?
>
> If any of the ADO 2.8 is checked, the errors occur on my customer's
machine.
> Here is what is checked in my references:
>
> VB for A
> ME 10.0 Object Library
> OLE Auto
> M Forms 2.0 Object Library
> M Office 10.0 Object Library
> M Activex Data Objects 2.7 Library
>
> When I check the 2.7 Recordset Library, The next time I check, it has
become
> 2.8 and error on my customer's machine.
>
> I have tried changinge these all back to 2.8 but the before print is being
> skipped then too.
>
>
> "Bob Phillips" wrote:
>
> > Have you put a break in the code to see that it gets entered?
> >
> > Check the VBE Tools>References, and check there are no missing
references
> > (like ADO 2.8?), and if so, get rid of them.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "JCanyoneer" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have an excell workbook that I transfer information to access with.
From
> > > other threads, I have been told to check the Microsoft ActiveX data
object
> > > references to allow for the connection. I did this (using version
2.8). My
> > > customer was getting errors from a hidden module so I had them search
for
> > the
> > > latest version on their machine-2.7. I changed the references in my
> > workbook
> > > to 2.7 and now the macros run perfectly on there machine.
> > >
> > > Now the problem. Since changing the version to 2.7, a macro that I use
in
> > > "This Workbook" to check for information from the user before printing
> > does
> > > not work. When printing, it is like the code in This Workbook is not
even
> > > there. Here's the code:
> > >
> > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > Dim Header As String, Response As Integer, I As Integer
> > > Header = Range("D2").Value
> > > With ActiveSheet.PageSetup
> > > .CenterHeader = "&12SI Body & Options" & Chr(10) & "APS
Vehicle# "
> > &
> > > Header
> > > End With
> > > Response = MsgBox("Is this a Shop Copy or Office Copy? Click Yes for
Shop
> > > Copy, No for Office Copy, or Cancel to cancel printng", vbYesNoCancel)
> > > If Response = vbYes Then
> > > ActiveSheet.Unprotect ("123")
> > > For I = 9 To 56
> > > Range("E" & I).Select
> > > Selection.ClearContents
> > > Next I
> > > Range("A1").Select
> > > ActiveSheet.Protect ("123"), DrawingObjects:=True, Contents:=True,
> > > Scenarios:=True, AllowFormattingCells:=True
> > > End If
> > >
> > > Any ideas why this is happening or what the simplest way to have this
> > > workbook do all of the things I need?
> >
> >
> >

The code stopped at that line. I clicked the stop button at the top of the
page and the worksheet printed without using the code. I then printed the
worksheet again and the code worked fine. Any ideas?

I did not see anything with the word MISSING in it (There's a tonof them to
look through)

"Bob Phillips" wrote:

> In the VBE, open the ThisWorkbook module, then select a code line in the
> BeforePrint and hit the F9 key. It should change that line to a brown
> background. Then when you print something (or print preview), the code
> should stop at that line.
>
> As to references, is there anything saying MISSING in the list?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JCanyoneer" <[email protected]> wrote in message
> news:[email protected]...
> > I'm not sure what you mean on the "break in code". Is this to see if This
> > Workbook get entered? How do I break the code? End?
> >
> > If any of the ADO 2.8 is checked, the errors occur on my customer's
> machine.
> > Here is what is checked in my references:
> >
> > VB for A
> > ME 10.0 Object Library
> > OLE Auto
> > M Forms 2.0 Object Library
> > M Office 10.0 Object Library
> > M Activex Data Objects 2.7 Library
> >
> > When I check the 2.7 Recordset Library, The next time I check, it has
> become
> > 2.8 and error on my customer's machine.
> >
> > I have tried changinge these all back to 2.8 but the before print is being
> > skipped then too.
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Have you put a break in the code to see that it gets entered?
> > >
> > > Check the VBE Tools>References, and check there are no missing
> references
> > > (like ADO 2.8?), and if so, get rid of them.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "JCanyoneer" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have an excell workbook that I transfer information to access with.
> From
> > > > other threads, I have been told to check the Microsoft ActiveX data
> object
> > > > references to allow for the connection. I did this (using version
> 2.8). My
> > > > customer was getting errors from a hidden module so I had them search
> for
> > > the
> > > > latest version on their machine-2.7. I changed the references in my
> > > workbook
> > > > to 2.7 and now the macros run perfectly on there machine.
> > > >
> > > > Now the problem. Since changing the version to 2.7, a macro that I use
> in
> > > > "This Workbook" to check for information from the user before printing
> > > does
> > > > not work. When printing, it is like the code in This Workbook is not
> even
> > > > there. Here's the code:
> > > >
> > > > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> > > > Dim Header As String, Response As Integer, I As Integer
> > > > Header = Range("D2").Value
> > > > With ActiveSheet.PageSetup
> > > > .CenterHeader = "&12SI Body & Options" & Chr(10) & "APS
> Vehicle# "
> > > &
> > > > Header
> > > > End With
> > > > Response = MsgBox("Is this a Shop Copy or Office Copy? Click Yes for
> Shop
> > > > Copy, No for Office Copy, or Cancel to cancel printng", vbYesNoCancel)
> > > > If Response = vbYes Then
> > > > ActiveSheet.Unprotect ("123")
> > > > For I = 9 To 56
> > > > Range("E" & I).Select
> > > > Selection.ClearContents
> > > > Next I
> > > > Range("A1").Select
> > > > ActiveSheet.Protect ("123"), DrawingObjects:=True, Contents:=True,
> > > > Scenarios:=True, AllowFormattingCells:=True
> > > > End If
> > > >
> > > > Any ideas why this is happening or what the simplest way to have this
> > > > workbook do all of the things I need?
> > >
> > >
> > >
>
>
>

JCanyoneer wrote:
> I have been told to check the Microsoft ActiveX data object
> references to allow for the connection.

Perhaps you didn't take the best advice <g>. Maybe time to reconsider
using late binding?

Jamie.

--

What is late binding and where can I get more info on it?

"Jamie Collins" wrote:

>
> JCanyoneer wrote:
> > I have been told to check the Microsoft ActiveX data object
> > references to allow for the connection.
>
> Perhaps you didn't take the best advice <g>. Maybe time to reconsider
> using late binding?
>
> Jamie.
>
> --
>
>

This shouldn't be an issue if all machines are using 2.7.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JCanyoneer" <[email protected]> wrote in message
news:[email protected]...
> What is late binding and where can I get more info on it?
>
> "Jamie Collins" wrote:
>
> >
> > JCanyoneer wrote:
> > > I have been told to check the Microsoft ActiveX data object
> > > references to allow for the connection.
> >
> > Perhaps you didn't take the best advice <g>. Maybe time to reconsider
> > using late binding?
> >
> > Jamie.
> >
> > --
> >
> >

Seems to all work fine right now. I'll let you know if I run into problems
with this in the future. Thanks for your help again Bob.

"Bob Phillips" wrote:

> This shouldn't be an issue if all machines are using 2.7.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JCanyoneer" <[email protected]> wrote in message
> news:[email protected]...
> > What is late binding and where can I get more info on it?
> >
> > "Jamie Collins" wrote:
> >
> > >
> > > JCanyoneer wrote:
> > > > I have been told to check the Microsoft ActiveX data object
> > > > references to allow for the connection.
> > >
> > > Perhaps you didn't take the best advice <g>. Maybe time to reconsider
> > > using late binding?
> > >
> > > Jamie.
> > >
> > > --
> > >
> > >
>
>
>