Hi folks

Office 97 & NT4

I've been handed a bit of a poisened chalice & like a fool I've taken a
sip...so here's the problem:

Esentially I need to prohibit printing a file unless a small number of
fields are completed. This was originally done in MS Word, but nothing stops
the users from choosing 'Disable Macros' and printing out as many copies as
they like.

I've come across XML macros that cannot be disabled, so I think Excel is my
only choice (yes?). What I'd like to do then is create a spreadsheet that
contains some text fields, which are validated and upon completion, prompt
the user to print. I can handle the validation & the final msgbox "print
now?" etc, but am completely stumped re. creating code from previous
versions of Excel.

Can anyone help me or suggest an alternative way to solve this problem? I
don't mind how it's done as long as users can't print a (partially or
totally) incomplete form - that's the nub of the issue

Ideally I'd like to use Word, and not use UserForms, but beggars can't be
choosers.

Thanks,
Paul

A possible solution might be to set a flag on a sheet which is very hidden.
The user can not get to the very hidden sheet (providing the project is
password protected) so you can control how often a sheet can be printed. You
will probably want to use the before print event to check the very hidden
sheet to determine if the sheet can be printed...

HTH

"Paul Wagstaff" wrote:

> Hi folks
>
> Office 97 & NT4
>
> I've been handed a bit of a poisened chalice & like a fool I've taken a
> sip...so here's the problem:
>
> Esentially I need to prohibit printing a file unless a small number of
> fields are completed. This was originally done in MS Word, but nothing stops
> the users from choosing 'Disable Macros' and printing out as many copies as
> they like.
>
> I've come across XML macros that cannot be disabled, so I think Excel is my
> only choice (yes?). What I'd like to do then is create a spreadsheet that
> contains some text fields, which are validated and upon completion, prompt
> the user to print. I can handle the validation & the final msgbox "print
> now?" etc, but am completely stumped re. creating code from previous
> versions of Excel.
>
> Can anyone help me or suggest an alternative way to solve this problem? I
> don't mind how it's done as long as users can't print a (partially or
> totally) incomplete form - that's the nub of the issue
>
> Ideally I'd like to use Word, and not use UserForms, but beggars can't be
> choosers.
>
> Thanks,
> Paul
>
>
>
>

Hi jim, you remember me (your student¡¡¡¡)

Also could be a good idea Hide all the toolbars and just leave a button with
the print caption in it (an its respective macro of print)

Application.CommandBars("").Enabled = false
Application.CommandBars("format").Enabled = false
Application.CommandBars("form").Enabled = false
Application.CommandBars("chart").Enabled = false
Application.commandbars("Worksheet Menu Bar").enabled=false

remember to put this code in your workbook module

and be shure to create a macro to enable all your menus with a shortcut
application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc.

also I want your opinion jim
"Jim Thomlinson" wrote:

> A possible solution might be to set a flag on a sheet which is very hidden.
> The user can not get to the very hidden sheet (providing the project is
> password protected) so you can control how often a sheet can be printed. You
> will probably want to use the before print event to check the very hidden
> sheet to determine if the sheet can be printed...
>
> HTH
>
> "Paul Wagstaff" wrote:
>
> > Hi folks
> >
> > Office 97 & NT4
> >
> > I've been handed a bit of a poisened chalice & like a fool I've taken a
> > sip...so here's the problem:
> >
> > Esentially I need to prohibit printing a file unless a small number of
> > fields are completed. This was originally done in MS Word, but nothing stops
> > the users from choosing 'Disable Macros' and printing out as many copies as
> > they like.
> >
> > I've come across XML macros that cannot be disabled, so I think Excel is my
> > only choice (yes?). What I'd like to do then is create a spreadsheet that
> > contains some text fields, which are validated and upon completion, prompt
> > the user to print. I can handle the validation & the final msgbox "print
> > now?" etc, but am completely stumped re. creating code from previous
> > versions of Excel.
> >
> > Can anyone help me or suggest an alternative way to solve this problem? I
> > don't mind how it's done as long as users can't print a (partially or
> > totally) incomplete form - that's the nub of the issue
> >
> > Ideally I'd like to use Word, and not use UserForms, but beggars can't be
> > choosers.
> >
> > Thanks,
> > Paul
> >
> >
> >
> >

I was under the impression that the beforeprint event doesn't run when
macros are enabled. Since the OP stated that disabling macros was the root
of the whole problem, how would you see this being a solution?

--
Regards,
Tom Ogilvy

"Jim Thomlinson" <(E-Mail Removed)> wrote in message
news:44E168C8-07F1-43BD-A55F-(E-Mail Removed)...
> A possible solution might be to set a flag on a sheet which is very
hidden.
> The user can not get to the very hidden sheet (providing the project is
> password protected) so you can control how often a sheet can be printed.
You
> will probably want to use the before print event to check the very hidden
> sheet to determine if the sheet can be printed...
>
> HTH
>
> "Paul Wagstaff" wrote:
>
> > Hi folks
> >
> > Office 97 & NT4
> >
> > I've been handed a bit of a poisened chalice & like a fool I've taken a
> > sip...so here's the problem:
> >
> > Esentially I need to prohibit printing a file unless a small number of
> > fields are completed. This was originally done in MS Word, but nothing
stops
> > the users from choosing 'Disable Macros' and printing out as many copies
as
> > they like.
> >
> > I've come across XML macros that cannot be disabled, so I think Excel is
my
> > only choice (yes?). What I'd like to do then is create a spreadsheet
that
> > contains some text fields, which are validated and upon completion,
prompt
> > the user to print. I can handle the validation & the final msgbox "print
> > now?" etc, but am completely stumped re. creating code from previous
> > versions of Excel.
> >
> > Can anyone help me or suggest an alternative way to solve this problem?
I
> > don't mind how it's done as long as users can't print a (partially or
> > totally) incomplete form - that's the nub of the issue
> >
> > Ideally I'd like to use Word, and not use UserForms, but beggars can't
be
> > choosers.
> >
> > Thanks,
> > Paul
> >
> >
> >
> >

Looks like you fell into the same trap as your instructor: You have
presented VBA code as a solution, but . . .

I was under the impression that vba macros don't run when macros are
disabled. Since the OP stated that disabling macros was the root of the
whole problem, how would you see this being a solution?

--
Regards,
Tom Ogilvy


"filo666" <(E-Mail Removed)> wrote in message
news:22582724-2E55-4112-A4C2-(E-Mail Removed)...
> Hi jim, you remember me (your student¡¡¡¡)
>
> Also could be a good idea Hide all the toolbars and just leave a button
with
> the print caption in it (an its respective macro of print)
>
> Application.CommandBars("").Enabled = false
> Application.CommandBars("format").Enabled = false
> Application.CommandBars("form").Enabled = false
> Application.CommandBars("chart").Enabled = false
> Application.commandbars("Worksheet Menu Bar").enabled=false
>
> remember to put this code in your workbook module
>
> and be shure to create a macro to enable all your menus with a shortcut
> application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc.
>
> also I want your opinion jim
> "Jim Thomlinson" wrote:
>
> > A possible solution might be to set a flag on a sheet which is very
hidden.
> > The user can not get to the very hidden sheet (providing the project is
> > password protected) so you can control how often a sheet can be printed.
You
> > will probably want to use the before print event to check the very
hidden
> > sheet to determine if the sheet can be printed...
> >
> > HTH
> >
> > "Paul Wagstaff" wrote:
> >
> > > Hi folks
> > >
> > > Office 97 & NT4
> > >
> > > I've been handed a bit of a poisened chalice & like a fool I've taken
a
> > > sip...so here's the problem:
> > >
> > > Esentially I need to prohibit printing a file unless a small number of
> > > fields are completed. This was originally done in MS Word, but nothing
stops
> > > the users from choosing 'Disable Macros' and printing out as many
copies as
> > > they like.
> > >
> > > I've come across XML macros that cannot be disabled, so I think Excel
is my
> > > only choice (yes?). What I'd like to do then is create a spreadsheet
that
> > > contains some text fields, which are validated and upon completion,
prompt
> > > the user to print. I can handle the validation & the final msgbox
"print
> > > now?" etc, but am completely stumped re. creating code from previous
> > > versions of Excel.
> > >
> > > Can anyone help me or suggest an alternative way to solve this
problem? I
> > > don't mind how it's done as long as users can't print a (partially or
> > > totally) incomplete form - that's the nub of the issue
> > >
> > > Ideally I'd like to use Word, and not use UserForms, but beggars can't
be
> > > choosers.
> > >
> > > Thanks,
> > > Paul
> > >
> > >
> > >
> > >

"Enabled" should of course be "Disabled". My Typo.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I was under the impression that the beforeprint event doesn't run when
> macros are enabled. Since the OP stated that disabling macros was the
root
> of the whole problem, how would you see this being a solution?
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <(E-Mail Removed)> wrote in
message
> news:44E168C8-07F1-43BD-A55F-(E-Mail Removed)...
> > A possible solution might be to set a flag on a sheet which is very
> hidden.
> > The user can not get to the very hidden sheet (providing the project is
> > password protected) so you can control how often a sheet can be printed.
> You
> > will probably want to use the before print event to check the very
hidden
> > sheet to determine if the sheet can be printed...
> >
> > HTH
> >
> > "Paul Wagstaff" wrote:
> >
> > > Hi folks
> > >
> > > Office 97 & NT4
> > >
> > > I've been handed a bit of a poisened chalice & like a fool I've taken
a
> > > sip...so here's the problem:
> > >
> > > Esentially I need to prohibit printing a file unless a small number of
> > > fields are completed. This was originally done in MS Word, but nothing
> stops
> > > the users from choosing 'Disable Macros' and printing out as many
copies
> as
> > > they like.
> > >
> > > I've come across XML macros that cannot be disabled, so I think Excel
is
> my
> > > only choice (yes?). What I'd like to do then is create a spreadsheet
> that
> > > contains some text fields, which are validated and upon completion,
> prompt
> > > the user to print. I can handle the validation & the final msgbox
"print
> > > now?" etc, but am completely stumped re. creating code from previous
> > > versions of Excel.
> > >
> > > Can anyone help me or suggest an alternative way to solve this
problem?
> I
> > > don't mind how it's done as long as users can't print a (partially or
> > > totally) incomplete form - that's the nub of the issue
> > >
> > > Ideally I'd like to use Word, and not use UserForms, but beggars can't
> be
> > > choosers.
> > >
> > > Thanks,
> > > Paul
> > >
> > >
> > >
> > >
>
>

You need to be able to handle the eventuallity of opening the file with
macro's disabled. The on close event of the spreadsheet should hide all but a
single sheet which lets the user know that macro's must be enabled. The on
open hides this sheet and unhides the data sheets. Other than that I can not
see how to get around this whole issue. Good point though...

HTH

"Tom Ogilvy" wrote:

> I was under the impression that the beforeprint event doesn't run when
> macros are enabled. Since the OP stated that disabling macros was the root
> of the whole problem, how would you see this being a solution?
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Thomlinson" <(E-Mail Removed)> wrote in message
> news:44E168C8-07F1-43BD-A55F-(E-Mail Removed)...
> > A possible solution might be to set a flag on a sheet which is very
> hidden.
> > The user can not get to the very hidden sheet (providing the project is
> > password protected) so you can control how often a sheet can be printed.
> You
> > will probably want to use the before print event to check the very hidden
> > sheet to determine if the sheet can be printed...
> >
> > HTH
> >
> > "Paul Wagstaff" wrote:
> >
> > > Hi folks
> > >
> > > Office 97 & NT4
> > >
> > > I've been handed a bit of a poisened chalice & like a fool I've taken a
> > > sip...so here's the problem:
> > >
> > > Esentially I need to prohibit printing a file unless a small number of
> > > fields are completed. This was originally done in MS Word, but nothing
> stops
> > > the users from choosing 'Disable Macros' and printing out as many copies
> as
> > > they like.
> > >
> > > I've come across XML macros that cannot be disabled, so I think Excel is
> my
> > > only choice (yes?). What I'd like to do then is create a spreadsheet
> that
> > > contains some text fields, which are validated and upon completion,
> prompt
> > > the user to print. I can handle the validation & the final msgbox "print
> > > now?" etc, but am completely stumped re. creating code from previous
> > > versions of Excel.
> > >
> > > Can anyone help me or suggest an alternative way to solve this problem?
> I
> > > don't mind how it's done as long as users can't print a (partially or
> > > totally) incomplete form - that's the nub of the issue
> > >
> > > Ideally I'd like to use Word, and not use UserForms, but beggars can't
> be
> > > choosers.
> > >
> > > Thanks,
> > > Paul
> > >
> > >
> > >
> > >
>
>
>

A typo. How many mistakes does that make for you in your life. I am
guessing... 1. I should we mark this day on the callendar for posterity...
;-)

"Tom Ogilvy" wrote:

> "Enabled" should of course be "Disabled". My Typo.
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I was under the impression that the beforeprint event doesn't run when
> > macros are enabled. Since the OP stated that disabling macros was the
> root
> > of the whole problem, how would you see this being a solution?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <(E-Mail Removed)> wrote in
> message
> > news:44E168C8-07F1-43BD-A55F-(E-Mail Removed)...
> > > A possible solution might be to set a flag on a sheet which is very
> > hidden.
> > > The user can not get to the very hidden sheet (providing the project is
> > > password protected) so you can control how often a sheet can be printed.
> > You
> > > will probably want to use the before print event to check the very
> hidden
> > > sheet to determine if the sheet can be printed...
> > >
> > > HTH
> > >
> > > "Paul Wagstaff" wrote:
> > >
> > > > Hi folks
> > > >
> > > > Office 97 & NT4
> > > >
> > > > I've been handed a bit of a poisened chalice & like a fool I've taken
> a
> > > > sip...so here's the problem:
> > > >
> > > > Esentially I need to prohibit printing a file unless a small number of
> > > > fields are completed. This was originally done in MS Word, but nothing
> > stops
> > > > the users from choosing 'Disable Macros' and printing out as many
> copies
> > as
> > > > they like.
> > > >
> > > > I've come across XML macros that cannot be disabled, so I think Excel
> is
> > my
> > > > only choice (yes?). What I'd like to do then is create a spreadsheet
> > that
> > > > contains some text fields, which are validated and upon completion,
> > prompt
> > > > the user to print. I can handle the validation & the final msgbox
> "print
> > > > now?" etc, but am completely stumped re. creating code from previous
> > > > versions of Excel.
> > > >
> > > > Can anyone help me or suggest an alternative way to solve this
> problem?
> > I
> > > > don't mind how it's done as long as users can't print a (partially or
> > > > totally) incomplete form - that's the nub of the issue
> > > >
> > > > Ideally I'd like to use Word, and not use UserForms, but beggars can't
> > be
> > > > choosers.
> > > >
> > > > Thanks,
> > > > Paul
> > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

you're right tom, but think about this, if the programmer leaves the
Application.commandbars("Worksheet Menu Bar").enabled=true (and I mean he
leave excel with no bars in it) how does the user could disabled the
macros???????
try it.

"Tom Ogilvy" wrote:

> "Enabled" should of course be "Disabled". My Typo.
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I was under the impression that the beforeprint event doesn't run when
> > macros are enabled. Since the OP stated that disabling macros was the
> root
> > of the whole problem, how would you see this being a solution?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Thomlinson" <(E-Mail Removed)> wrote in
> message
> > news:44E168C8-07F1-43BD-A55F-(E-Mail Removed)...
> > > A possible solution might be to set a flag on a sheet which is very
> > hidden.
> > > The user can not get to the very hidden sheet (providing the project is
> > > password protected) so you can control how often a sheet can be printed.
> > You
> > > will probably want to use the before print event to check the very
> hidden
> > > sheet to determine if the sheet can be printed...
> > >
> > > HTH
> > >
> > > "Paul Wagstaff" wrote:
> > >
> > > > Hi folks
> > > >
> > > > Office 97 & NT4
> > > >
> > > > I've been handed a bit of a poisened chalice & like a fool I've taken
> a
> > > > sip...so here's the problem:
> > > >
> > > > Esentially I need to prohibit printing a file unless a small number of
> > > > fields are completed. This was originally done in MS Word, but nothing
> > stops
> > > > the users from choosing 'Disable Macros' and printing out as many
> copies
> > as
> > > > they like.
> > > >
> > > > I've come across XML macros that cannot be disabled, so I think Excel
> is
> > my
> > > > only choice (yes?). What I'd like to do then is create a spreadsheet
> > that
> > > > contains some text fields, which are validated and upon completion,
> > prompt
> > > > the user to print. I can handle the validation & the final msgbox
> "print
> > > > now?" etc, but am completely stumped re. creating code from previous
> > > > versions of Excel.
> > > >
> > > > Can anyone help me or suggest an alternative way to solve this
> problem?
> > I
> > > > don't mind how it's done as long as users can't print a (partially or
> > > > totally) incomplete form - that's the nub of the issue
> > > >
> > > > Ideally I'd like to use Word, and not use UserForms, but beggars can't
> > be
> > > > choosers.
> > > >
> > > > Thanks,
> > > > Paul
> > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

That is a nice touch but it can lead to a lot of work. Those are application
settings and when you paly with those you really want to have an error
handler to clean those settings up in case of an error. The other issue is if
the user has multiple books open then the toolbars are gone for all books,
not just the current book. I usually avoid that wherever possible. Too many
headaches...

"filo666" wrote:

> Hi jim, you remember me (your student¡¡¡¡)
>
> Also could be a good idea Hide all the toolbars and just leave a button with
> the print caption in it (an its respective macro of print)
>
> Application.CommandBars("").Enabled = false
> Application.CommandBars("format").Enabled = false
> Application.CommandBars("form").Enabled = false
> Application.CommandBars("chart").Enabled = false
> Application.commandbars("Worksheet Menu Bar").enabled=false
>
> remember to put this code in your workbook module
>
> and be shure to create a macro to enable all your menus with a shortcut
> application.commandbars("Worksheet Menu Bar").enabled=true 'etc etc.
>
> also I want your opinion jim
> "Jim Thomlinson" wrote:
>
> > A possible solution might be to set a flag on a sheet which is very hidden.
> > The user can not get to the very hidden sheet (providing the project is
> > password protected) so you can control how often a sheet can be printed. You
> > will probably want to use the before print event to check the very hidden
> > sheet to determine if the sheet can be printed...
> >
> > HTH
> >
> > "Paul Wagstaff" wrote:
> >
> > > Hi folks
> > >
> > > Office 97 & NT4
> > >
> > > I've been handed a bit of a poisened chalice & like a fool I've taken a
> > > sip...so here's the problem:
> > >
> > > Esentially I need to prohibit printing a file unless a small number of
> > > fields are completed. This was originally done in MS Word, but nothing stops
> > > the users from choosing 'Disable Macros' and printing out as many copies as
> > > they like.
> > >
> > > I've come across XML macros that cannot be disabled, so I think Excel is my
> > > only choice (yes?). What I'd like to do then is create a spreadsheet that
> > > contains some text fields, which are validated and upon completion, prompt
> > > the user to print. I can handle the validation & the final msgbox "print
> > > now?" etc, but am completely stumped re. creating code from previous
> > > versions of Excel.
> > >
> > > Can anyone help me or suggest an alternative way to solve this problem? I
> > > don't mind how it's done as long as users can't print a (partially or
> > > totally) incomplete form - that's the nub of the issue
> > >
> > > Ideally I'd like to use Word, and not use UserForms, but beggars can't be
> > > choosers.
> > >
> > > Thanks,
> > > Paul
> > >
> > >
> > >
> > >