Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

question 1 = put the code in a open_workbook event so the code auto runs the
macro when the book is opened

question 2 = you could protect the cells you which data to be entered but
allow data entry only ,

"John Calder" wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

Rich

The code is change event code which runs on a cell by cell basis as text is
entered in column 12(L).

Not a macro to be run once.


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 18:11:01 -0700, Rich wrote:

question 1 = put the code in a open_workbook event so the code auto runs the
macro when the book is opened

question 2 = you could protect the cells you which data to be entered but
allow data entry only ,

"John Calder" wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

John

This is event code.

The code does run automatically whenever you enter text in any cell in column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

Thank you for your response.

The problem that I am having is that although the code works fine, some of
the users of this spreadsheet take the option of disabling the macros when
opening the file. As the code is designed to enforce capitals in column 12
when the "disable macros" option is taken when opening the spreadsheet, the
enforced capitals no longer works. What I would like is that when the
spreadsheet opens, that the user does not get the option of enable/disable
macros but the code still runs.

If there is some code that could do this and if I knew where to put it then
maybe this would solve my problem.

Thanks

John




"Gord Dibben" wrote:

John

This is event code.

The code does run automatically whenever you enter text in any cell in column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

You cannot stop them getting that message without setting your security
setting to low (which is a very bad idea), but you can take some defensive
action.

The standard way to approach this is as follows.
- create a worksheet with a message on explaining that for this workbook to
run it needs macros enabled, maybe even a few screenshots
- hide all other worksheets]
- add some code in the Workbook_Open event that un hides the other sheets,
but hides that sheet.


What happens is that if they do not enable macros, they will only see the
warning sheet, telling them how to do it. If the enable macros, it will
startup as the workbook it should be.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"John Calder" wrote in message
...
Thank you for your response.

The problem that I am having is that although the code works fine, some of
the users of this spreadsheet take the option of disabling the macros when
opening the file. As the code is designed to enforce capitals in column 12
when the "disable macros" option is taken when opening the spreadsheet,
the
enforced capitals no longer works. What I would like is that when the
spreadsheet opens, that the user does not get the option of enable/disable
macros but the code still runs.

If there is some code that could do this and if I knew where to put it
then
maybe this would solve my problem.

Thanks

John




"Gord Dibben" wrote:

John

This is event code.

The code does run automatically whenever you enter text in any cell in
column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is
opened. (I
am assuming that this would remove the enable/disable dialog box option
when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that
allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells
that
allow data entry.

Is there any code that I can add that would still allow the data entry
but
not allow changes to the formatting?

Any help is much appreciated.

John

John

You do not have the option of not allowing users to disable macros as long as
they can see the enable/disable message when the workbook opens.

You can digitally sign the workbook so they don't get the message or you can
render the workbook useless if the users disable macros.

i.e. if users disable macros and open the workbook all they see is a blank sheet
with a message like "in order to use this workbook, macros must be enabled.
please close and re-open with macros enabled". Other worksheets will be hidden.

If they do choose to close and reopen, the workbook returns to normal view with
worksheets unhidden.

You need workbook_open and workbook_beforeclose code to achieve this.


Gord

On Sun, 20 Aug 2006 21:41:02 -0700, John Calder
wrote:

Thank you for your response.

The problem that I am having is that although the code works fine, some of
the users of this spreadsheet take the option of disabling the macros when
opening the file. As the code is designed to enforce capitals in column 12
when the "disable macros" option is taken when opening the spreadsheet, the
enforced capitals no longer works. What I would like is that when the
spreadsheet opens, that the user does not get the option of enable/disable
macros but the code still runs.

If there is some code that could do this and if I knew where to put it then
maybe this would solve my problem.

Thanks

John




"Gord Dibben" wrote:

John

This is event code.

The code does run automatically whenever you enter text in any cell in column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

Thanks Bob & Gord for your replies. As I dont have the visual basic skills to
achieve all of this I think I will just reinforce with the users that they
must enable the macros. As there are only a couple of users that consitantly
disregard the "enable macros" option I guess I was trying to make the sheet
"Idiot Proof" for these users.

The other question I asked was the worksheet I have is protected and
contains a number of cells that allow data entry.

The problem I have is that a number of people use this spreadsheet and some
of these users change the formatting in the cells that allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?





At least I know what can and cant be done in this situation in regards to
the enable/disable macro so I have at least learned something from you both.


Many Thanks

John


"Gord Dibben" wrote:

John

You do not have the option of not allowing users to disable macros as long as
they can see the enable/disable message when the workbook opens.

You can digitally sign the workbook so they don't get the message or you can
render the workbook useless if the users disable macros.

i.e. if users disable macros and open the workbook all they see is a blank sheet
with a message like "in order to use this workbook, macros must be enabled.
please close and re-open with macros enabled". Other worksheets will be hidden.

If they do choose to close and reopen, the workbook returns to normal view with
worksheets unhidden.

You need workbook_open and workbook_beforeclose code to achieve this.


Gord

On Sun, 20 Aug 2006 21:41:02 -0700, John Calder
wrote:

Thank you for your response.

The problem that I am having is that although the code works fine, some of
the users of this spreadsheet take the option of disabling the macros when
opening the file. As the code is designed to enforce capitals in column 12
when the "disable macros" option is taken when opening the spreadsheet, the
enforced capitals no longer works. What I would like is that when the
spreadsheet opens, that the user does not get the option of enable/disable
macros but the code still runs.

If there is some code that could do this and if I knew where to put it then
maybe this would solve my problem.

Thanks

John




"Gord Dibben" wrote:

John

This is event code.

The code does run automatically whenever you enter text in any cell in column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John

Update to problem

I spoke to the people using the spread sheet and they in fact take the
"enable macro" option on startup. What they are doing is entering a lower
case letter in the cell and BEFORE the hit "enter" they are grabbing the
"handle" of the curser and dragging it down the colum. This results in the
code enforcing the first entry to a capital letter but the following entries
remain lower case. Not sure if the code can be adjusted to accomodate this?

John


"John Calder" wrote:

Thanks Bob & Gord for your replies. As I dont have the visual basic skills to
achieve all of this I think I will just reinforce with the users that they
must enable the macros. As there are only a couple of users that consitantly
disregard the "enable macros" option I guess I was trying to make the sheet
"Idiot Proof" for these users.

The other question I asked was the worksheet I have is protected and
contains a number of cells that allow data entry.

The problem I have is that a number of people use this spreadsheet and some
of these users change the formatting in the cells that allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?





At least I know what can and cant be done in this situation in regards to
the enable/disable macro so I have at least learned something from you both.


Many Thanks

John


"Gord Dibben" wrote:

John

You do not have the option of not allowing users to disable macros as long as
they can see the enable/disable message when the workbook opens.

You can digitally sign the workbook so they don't get the message or you can
render the workbook useless if the users disable macros.

i.e. if users disable macros and open the workbook all they see is a blank sheet
with a message like "in order to use this workbook, macros must be enabled.
please close and re-open with macros enabled". Other worksheets will be hidden.

If they do choose to close and reopen, the workbook returns to normal view with
worksheets unhidden.

You need workbook_open and workbook_beforeclose code to achieve this.


Gord

On Sun, 20 Aug 2006 21:41:02 -0700, John Calder
wrote:

Thank you for your response.

The problem that I am having is that although the code works fine, some of
the users of this spreadsheet take the option of disabling the macros when
opening the file. As the code is designed to enforce capitals in column 12
when the "disable macros" option is taken when opening the spreadsheet, the
enforced capitals no longer works. What I would like is that when the
spreadsheet opens, that the user does not get the option of enable/disable
macros but the code still runs.

If there is some code that could do this and if I knew where to put it then
maybe this would solve my problem.

Thanks

John




"Gord Dibben" wrote:

John

This is event code.

The code does run automatically whenever you enter text in any cell in column 12
which is column L.

No need to remove anything from the code.

On August 9th you posted that you had everything worked out.

What transpired since then?

Are you having a problem?


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 17:14:02 -0700, John Calder
wrote:

Hi

I run Win 2K with Excel 2K

Question 1

I have some VB code in the "Sheet 1" module of my excel file.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 12 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

I would like this code to run automatically whenever the file is opened. (I
am assuming that this would remove the enable/disable dialog box option when
opening the file)

Can someone tell me how I might be able to do this?


Question 2

The worksheet I have is protected and contains a number of cells that allow
data entry. The problem I have is that a number of people use this
spreadsheet and some of these users change the formatting in the cells that
allow data entry.

Is there any code that I can add that would still allow the data entry but
not allow changes to the formatting?

Any help is much appreciated.

John