2 Questions

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

0
JohnCalder (178)
8/21/2006 12:14:02 AM
excel.newusers 15348 articles. 2 followers. Follow

16 Replies
775 Views

Similar Articles

[PageSpeed] 1

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
> 
0
Rich (271)
8/21/2006 1:11:01 AM
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
<JohnCalder@discussions.microsoft.com> 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

0
Gord
8/21/2006 2:33:16 AM
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 <Rich@discussions.microsoft.com> 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
>> 

0
Gord
8/21/2006 2:36:07 AM
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
> <JohnCalder@discussions.microsoft.com> 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
> 
> 
0
JohnCalder (178)
8/21/2006 4:41:02 AM
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" <JohnCalder@discussions.microsoft.com> wrote in message
news:F28C3918-98D6-41DB-B662-2729C06717D2@microsoft.com...
> 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
> > <JohnCalder@discussions.microsoft.com> 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
> >
> >


0
bob.NGs1 (1661)
8/21/2006 8:15:34 AM
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
<JohnCalder@discussions.microsoft.com> 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
>> <JohnCalder@discussions.microsoft.com> 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
>> 
>> 

0
Gord
8/21/2006 3:24:28 PM
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
> <JohnCalder@discussions.microsoft.com> 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
> >> <JohnCalder@discussions.microsoft.com> 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
> >> 
> >> 
> 
> 
0
JohnCalder (178)
8/21/2006 10:10:36 PM
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
> > <JohnCalder@discussions.microsoft.com> 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
> > >> <JohnCalder@discussions.microsoft.com> 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
> > >> 
> > >> 
> > 
> > 
0
JohnCalder (178)
8/21/2006 10:12:43 PM
Pretty strange bunch you have there.

Why would they copy a letter down the column?

To stop that behavior you could amend the code to disable drag and drop but
remember this........i"just when you have it idiot-proof out pops another
species of idiot"

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


Gord Dibben  MS Excel MVP

On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
<JohnCalder@discussions.microsoft.com> wrote:

>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
>> > <JohnCalder@discussions.microsoft.com> 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
>> > >> <JohnCalder@discussions.microsoft.com> 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
>> > >> 
>> > >> 
>> > 
>> > 

0
Gord
8/22/2006 12:30:24 AM
Lets' be honest, the idiots are smarter than us <g>

-- 
 HTH

Bob Phillips

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

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:4hjke2t6b8cvohjmhpp39glfa8s185rl34@4ax.com...
> Pretty strange bunch you have there.
>
> Why would they copy a letter down the column?
>
> To stop that behavior you could amend the code to disable drag and drop
but
> remember this........i"just when you have it idiot-proof out pops another
> species of idiot"
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column <> 12 Then Exit Sub
>     On Error GoTo ErrHandler
>     Application.EnableEvents = False
>     Application.CellDragAndDrop = False
>     Target.Formula = UCase(Target.Formula)
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
>
> Gord Dibben  MS Excel MVP
>
> On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
> <JohnCalder@discussions.microsoft.com> wrote:
>
> >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
> >> > <JohnCalder@discussions.microsoft.com> 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
> >> > >> <JohnCalder@discussions.microsoft.com> 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
> >> > >>
> >> > >>
> >> >
> >> >
>


0
bob.NGs1 (1661)
8/22/2006 8:01:19 AM
I wouldn't say smarter but they sure have more stamina and inventiveness<g>




On Tue, 22 Aug 2006 09:01:19 +0100, "Bob Phillips" <bob.NGs@somewhere.com>
wrote:

>Lets' be honest, the idiots are smarter than us <g>

Gord Dibben  MS Excel MVP
0
Gord
8/22/2006 4:55:03 PM
Thanks guys, I got quite a laugh from your replies.

I will try the code you sent me Bob, and then look fowrwrd to seeing how 
they over come that !

Thanks again !

John


"Gord Dibben" wrote:

> I wouldn't say smarter but they sure have more stamina and inventiveness<g>
> 
> 
> 
> 
> On Tue, 22 Aug 2006 09:01:19 +0100, "Bob Phillips" <bob.NGs@somewhere.com>
> wrote:
> 
> >Lets' be honest, the idiots are smarter than us <g>
> 
> Gord Dibben  MS Excel MVP
> 
0
JohnCalder (178)
8/22/2006 9:01:01 PM
Bob I think your code will almost get me there, just one more thing, is there 
something that can be added that enables "drag and drop" on "exit" of the 
work sheet?

Thanks

John



"Bob Phillips" wrote:

> Lets' be honest, the idiots are smarter than us <g>
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (replace somewhere in email address with gmail if mailing direct)
> 
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:4hjke2t6b8cvohjmhpp39glfa8s185rl34@4ax.com...
> > Pretty strange bunch you have there.
> >
> > Why would they copy a letter down the column?
> >
> > To stop that behavior you could amend the code to disable drag and drop
> but
> > remember this........i"just when you have it idiot-proof out pops another
> > species of idiot"
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column <> 12 Then Exit Sub
> >     On Error GoTo ErrHandler
> >     Application.EnableEvents = False
> >     Application.CellDragAndDrop = False
> >     Target.Formula = UCase(Target.Formula)
> > ErrHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > Gord Dibben  MS Excel MVP
> >
> > On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
> > <JohnCalder@discussions.microsoft.com> wrote:
> >
> > >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
> > >> > <JohnCalder@discussions.microsoft.com> 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
> > >> > >> <JohnCalder@discussions.microsoft.com> 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
> > >> > >>
> > >> > >>
> > >> >
> > >> >
> >
> 
> 
> 
0
JohnCalder (178)
8/22/2006 9:19:02 PM
John

In the same worksheet module............

Private Sub Worksheet_Deactivate()
    Application.CellDragAndDrop = True
End Sub

When switching to another sheet the above will run.

But if you will save/close the workbook without switching sheets the drag and
drop won't be re-enabled.

You need more code to accomodate that event.

Right-click on the Excel Icon left of "File" on the menubar and select "View
Code"

Copy/paste this into that Thiswotkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CellDragAndDrop = True
End Sub


Gord


On Tue, 22 Aug 2006 14:19:02 -0700, John Calder
<JohnCalder@discussions.microsoft.com> wrote:

>Bob I think your code will almost get me there, just one more thing, is there 
>something that can be added that enables "drag and drop" on "exit" of the 
>work sheet?
>
>Thanks
>
>John
>
>
>
>"Bob Phillips" wrote:
>
>> Lets' be honest, the idiots are smarter than us <g>
>> 
>> -- 
>>  HTH
>> 
>> Bob Phillips
>> 
>> (replace somewhere in email address with gmail if mailing direct)
>> 
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news:4hjke2t6b8cvohjmhpp39glfa8s185rl34@4ax.com...
>> > Pretty strange bunch you have there.
>> >
>> > Why would they copy a letter down the column?
>> >
>> > To stop that behavior you could amend the code to disable drag and drop
>> but
>> > remember this........i"just when you have it idiot-proof out pops another
>> > species of idiot"
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > If Target.Column <> 12 Then Exit Sub
>> >     On Error GoTo ErrHandler
>> >     Application.EnableEvents = False
>> >     Application.CellDragAndDrop = False
>> >     Target.Formula = UCase(Target.Formula)
>> > ErrHandler:
>> > Application.EnableEvents = True
>> > End Sub
>> >
>> >
>> > Gord Dibben  MS Excel MVP
>> >
>> > On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
>> > <JohnCalder@discussions.microsoft.com> wrote:
>> >
>> > >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
>> > >> > <JohnCalder@discussions.microsoft.com> 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
>> > >> > >> <JohnCalder@discussions.microsoft.com> 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
>> > >> > >>
>> > >> > >>
>> > >> >
>> > >> >
>> >
>> 
>> 
>> 

0
Gord
8/22/2006 10:48:03 PM
Gord

So very close !!!  I really do appreciate your help with this.

Your code works fine in regards to returning the "enable - drop and drag" 
after you close the file. There is however one small glitch. Even although I 
have included this code:

Private Sub Worksheet_Deactivate()
     Application.CellDragAndDrop = True
 End Sub

in the same work sheet as per your instruction, when you first open the file 
the drag down handle is available on the curser. Then, once you do a single 
drag down with it the curser then no longer allows the drag down to function 
as the drag and drop is then no longer activated. Is there something I am 
missing or is this just the way it works?

Many Thanks


John



"Gord Dibben" wrote:

> John
> 
> In the same worksheet module............
> 
> Private Sub Worksheet_Deactivate()
>     Application.CellDragAndDrop = True
> End Sub
> 
> When switching to another sheet the above will run.
> 
> But if you will save/close the workbook without switching sheets the drag and
> drop won't be re-enabled.
> 
> You need more code to accomodate that event.
> 
> Right-click on the Excel Icon left of "File" on the menubar and select "View
> Code"
> 
> Copy/paste this into that Thiswotkbook module.
> 
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>     Application.CellDragAndDrop = True
> End Sub
> 
> 
> Gord
> 
> 
> On Tue, 22 Aug 2006 14:19:02 -0700, John Calder
> <JohnCalder@discussions.microsoft.com> wrote:
> 
> >Bob I think your code will almost get me there, just one more thing, is there 
> >something that can be added that enables "drag and drop" on "exit" of the 
> >work sheet?
> >
> >Thanks
> >
> >John
> >
> >
> >
> >"Bob Phillips" wrote:
> >
> >> Lets' be honest, the idiots are smarter than us <g>
> >> 
> >> -- 
> >>  HTH
> >> 
> >> Bob Phillips
> >> 
> >> (replace somewhere in email address with gmail if mailing direct)
> >> 
> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> news:4hjke2t6b8cvohjmhpp39glfa8s185rl34@4ax.com...
> >> > Pretty strange bunch you have there.
> >> >
> >> > Why would they copy a letter down the column?
> >> >
> >> > To stop that behavior you could amend the code to disable drag and drop
> >> but
> >> > remember this........i"just when you have it idiot-proof out pops another
> >> > species of idiot"
> >> >
> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > If Target.Column <> 12 Then Exit Sub
> >> >     On Error GoTo ErrHandler
> >> >     Application.EnableEvents = False
> >> >     Application.CellDragAndDrop = False
> >> >     Target.Formula = UCase(Target.Formula)
> >> > ErrHandler:
> >> > Application.EnableEvents = True
> >> > End Sub
> >> >
> >> >
> >> > Gord Dibben  MS Excel MVP
> >> >
> >> > On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
> >> > <JohnCalder@discussions.microsoft.com> wrote:
> >> >
> >> > >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
> >> > >> > <JohnCalder@discussions.microsoft.com> 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
> >> > >> > >> <JohnCalder@discussions.microsoft.com> 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
> >> > >> > >>
> >> > >> > >>
> >> > >> >
> >> > >> >
> >> >
> >> 
> >> 
> >> 
> 
> 
0
JohnCalder (178)
8/23/2006 4:21:01 AM
Absolutely Brilliant !

Well done Gord !

It works exactly how you said it would. I realise this has taken some of 
your time up and I appreciate it very much. This whole excercise probably 
seems a liitle extravagant but this has become a battle between me and a 
couple of users and I now feel the battle has been won.

Of course I realise that its impossible to "idot proof" a spreadsheet but 
this a a great step towards it

Once again many thanks for your expert help

John Calder



"Gord Dibben" wrote:

> The disabling of drag and drop will not take place until the original
> Worksheet_Change event is triggered.
> 
> You could disable drag and drop in workbook_open code, but D&D is global setting
> so you get into problems there unless your sheet is always active when you open
> the workbook.
> 
> This is getting more complex as we move along.
> 
> First............
> 
> Remove the line  Application.CellDragAndDrop = False form the original
> worksheet_change code.
> 
> Then add the following to the sheet module along with the Worksheet_Deactivate
> and the Worksheet_Change
> 
> Private Sub Worksheet_Activate()
>      Application.CellDragAndDrop = False
> End Sub
> 
> Now add this to the Thisworkbook module
> 
> Private Sub Workbook_Open()
>     Sheets("Sheet1").Activate  'where sheet1 is you sheetname
> End Sub
> 
> This is what we will have................
> 
> The workbook_open code activates the worksheet which diasbles D&D because of the
> worksheet_activate.
> 
> The worksheet_change will Uppercase the entries.
> 
> If you switch to another worksheet or workbook, the worksheet_deactivate will
> enable D&D
> 
> If you save/close the workbook, the workbook_beforeclose will enable D&D
> 
> How we doing so far?<g>
> 
> 
> Gord
> 
> On Tue, 22 Aug 2006 21:21:01 -0700, John Calder
> <JohnCalder@discussions.microsoft.com> wrote:
> 
> >Gord
> >
> >So very close !!!  I really do appreciate your help with this.
> >
> >Your code works fine in regards to returning the "enable - drop and drag" 
> >after you close the file. There is however one small glitch. Even although I 
> >have included this code:
> >
> >Private Sub Worksheet_Deactivate()
> >     Application.CellDragAndDrop = True
> > End Sub
> >
> >in the same work sheet as per your instruction, when you first open the file 
> >the drag down handle is available on the curser. Then, once you do a single 
> >drag down with it the curser then no longer allows the drag down to function 
> >as the drag and drop is then no longer activated. Is there something I am 
> >missing or is this just the way it works?
> >
> >Many Thanks
> >
> >
> >John
> >
> >
> >
> >"Gord Dibben" wrote:
> >
> >> John
> >> 
> >> In the same worksheet module............
> >> 
> >> Private Sub Worksheet_Deactivate()
> >>     Application.CellDragAndDrop = True
> >> End Sub
> >> 
> >> When switching to another sheet the above will run.
> >> 
> >> But if you will save/close the workbook without switching sheets the drag and
> >> drop won't be re-enabled.
> >> 
> >> You need more code to accomodate that event.
> >> 
> >> Right-click on the Excel Icon left of "File" on the menubar and select "View
> >> Code"
> >> 
> >> Copy/paste this into that Thiswotkbook module.
> >> 
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >>     Application.CellDragAndDrop = True
> >> End Sub
> >> 
> >> 
> >> Gord
> >> 
> >> 
> >> On Tue, 22 Aug 2006 14:19:02 -0700, John Calder
> >> <JohnCalder@discussions.microsoft.com> wrote:
> >> 
> >> >Bob I think your code will almost get me there, just one more thing, is there 
> >> >something that can be added that enables "drag and drop" on "exit" of the 
> >> >work sheet?
> >> >
> >> >Thanks
> >> >
> >> >John
> >> >
> >> >
> >> >
> >> >"Bob Phillips" wrote:
> >> >
> >> >> Lets' be honest, the idiots are smarter than us <g>
> >> >> 
> >> >> -- 
> >> >>  HTH
> >> >> 
> >> >> Bob Phillips
> >> >> 
> >> >> (replace somewhere in email address with gmail if mailing direct)
> >> >> 
> >> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> >> news:4hjke2t6b8cvohjmhpp39glfa8s185rl34@4ax.com...
> >> >> > Pretty strange bunch you have there.
> >> >> >
> >> >> > Why would they copy a letter down the column?
> >> >> >
> >> >> > To stop that behavior you could amend the code to disable drag and drop
> >> >> but
> >> >> > remember this........i"just when you have it idiot-proof out pops another
> >> >> > species of idiot"
> >> >> >
> >> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> >> > If Target.Column <> 12 Then Exit Sub
> >> >> >     On Error GoTo ErrHandler
> >> >> >     Application.EnableEvents = False
> >> >> >     Application.CellDragAndDrop = False
> >> >> >     Target.Formula = UCase(Target.Formula)
> >> >> > ErrHandler:
> >> >> > Application.EnableEvents = True
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > Gord Dibben  MS Excel MVP
> >> >> >
> >> >> > On Mon, 21 Aug 2006 15:12:43 -0700, John Calder
> >> >> > <JohnCalder@discussions.microsoft.com> wrote:
> >> >> >
> >> >> > >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
> >> >> > >> > <JohnCalder@discussions.microsoft.com> 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
> >> >> > >> > >> <JohnCalder@discussions.microsoft.com> 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)
0
JohnCalder (178)
8/24/2006 1:15:02 AM
Reply:

Similar Artilces:

Easy Control question..
I have a dialog with some controls... and each control is linked to a member variable, such that i can the control value (eg a CEdit), and the linked member variable will change. How do i do the reverse? That is, at runtime i want to change the control. Say I have a CEdit with a member variable m_CEditVal. If I go m_CEditVal = "sdfsd", the CEdit does not update. How do I do this? "hamishd" <Hamish.Dean@gmail.com> wrote in message news:1157275706.966396.172990@e3g2000cwe.googlegroups.com... >I have a dialog with some controls... and each control is linked to a...

easy question, but not to me
a b c d e f Purchase Purchase Today's Market $ Profit % Profit Date Price Date Value 1/1/02 100.00 9/4/03 150.00 50.00 given the above, what is the formula that I should put in column f to get the percentage profit Many thanks Hi =D2/B2-1 format result cell as % -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "L. T. Portella" <portella@optonline.net> wrote in message news:gKb7b.99716$Ay2.23290547@news4.srv.hcvl...

duplex printing question, change record order to line up with other side?
i have a report that i need to make the opposite side match up with the correct record. it is setup to print 4 records, 2 across then 2 below. like 4 post cards on a landscape page. records for each customer need to match up with the mailing address on the reverse side. so the records are switched if i were to make the report right now. basicly records start 1 and 2 on the top of the landscaped page then 3 and 4 under. so on the reverse side records need to be 2 and 1 then 4 and 3 under. i havent made the opposite side for mailing address yet. i need some info on how i need to do this....

sharing workbook #2
i have a sheet, which i want to share it with others. i want others t append data to it on a continuos basis and all that data should b saved. can somebody "teach " me how to go about it? for eg: i want to record login and logout time by employees in a x sheet. this login and logout time would be entered by the employee themselves, against their names that is in a column -- Message posted from http://www.ExcelForum.com ...

Newbie question about Ex 2003
Hi to the Gurus amongst you, sorry about the length of this but I feel the need to fill you in with the scenario before I ask the questions. I have just got a new job with a global company. I used to work at a school before with two domains and about 800 users. I have installed and maintained Exchange 2002 at the school. The new company wants to get better organised with their e-mail. They are not running any form of Exchange, they have Linux servers with their own portal and a couple or three W2k servers (Don't know yet which service packs are installed). They are using Outlook...

What is the Orb? And other new outlook questions
I just reinstalled Outlook (same version (2003) that I was previously using), and there is some behavior that is new that I don't understand. First, when I look at (read) a message, there is an "orb" (ball) to the left of the address in the "From" line, if I put the mouse on it I am shown some information about the sender (including his online/offline status). Where did this come from, can I turn it off. Also, I now have a line at the top of the header area showing me the "zone" of the message ("You are now looking at this message in the internet...

DirectShow MPEG-2
I'm trying to play various files that I've converted to different formats. I've tried three different formats. Two of the videos play, but with no sound. One video won't play because RealPlayer SP says it has to download update and then it can't because "Microsoft DirectShow compatible MPEG-2 decoding software isn't installed". I have wasted a great deal of time trying to find what I need and can't. When I found one site and tried to download it said I didn't need it because I have SP3. I have windows xp home edition 2002, sp3. I want to...

Entering Numbers with 2 Decimal Places
Hi, I've been using Excel for years, yet this is my first time on this newsgroup. When reconciling my checkbook, I can set my 10-key calculator to 2 decimal places, so that whenever I enter a number, it automatically converts it to dollars and cents. Is there a way to do this in Excel so that I don't have to hit the "." ? Thanks, Bruce Choose Tools>Options On the Edit tab, add a check mark to Fixed Decimals Set the number of places to 2 Click OK Bruce wrote: > Hi, > > I've been using Excel for years, yet this is my first time on this > newsgroup....

ughh.... Office 2004 sp2 (11.2) update error
Let me start this out by saying I'm a PC engineer, I do not do Macs, but my wife had a G4 and we just upgraded it. I know I'm not the only one getting this error as I've seen it listed about a dozen times, but the only people that I see with a solution have external hard drives. I've loaded Office 2004 with SP2 Version 11.2 (050714). I've tried auto update as well as downloading. and I keep getting the #$%#@ error "The installer could not locate the correct version of the software required to install this update. See the Read Me file included with this install to det...

Questions for you experts.
I’m looking for some tips on setting up my CRM 3.0 on SBS 2k3 R2 for Services. We primarily offer three types of service - Consulting, Application Development, and Technical Support. We have some clients that have service contracts with us that let them allocate a fixed amount of hours per month to any or all of those services at a fixed rate. Other clients will just purchase one of those three services at our regular service rates. We don’t sell any products though, just services. I have some questions for any experience CRM user: 1) I’d like to have a single contract for...

CString -> char (easy question)
char input[MAX_LENGTH]; CString s = "HELLO"; How to I get input to hold s ("HELLO")? Hamish wrote: > char input[MAX_LENGTH]; > CString s = "HELLO"; > > How to I get input to hold s ("HELLO")? > > strcpy(input, s); -- Scott McPhillips [VC++ MVP] > char input[MAX_LENGTH]; > CString s = "HELLO"; > > How to I get input to hold s ("HELLO")? #ifndef _UNICODE strcpy(input,s); #else strcpy(input,W2A(s)); #endif Look at T2A macro. ------------ Ajay Kalra ajaykalra@yahoo.com The most important q...

Multiple Installation Question
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3294564435_42315548 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit I participated in the free upgrade of MS Office 2008 from purchasing Mac Office 04.. I noticed when I received the software I was only given 1 Product Key... I�m getting ready to upgrade to a new iMac and was wondering if I was going to run into a problem installing the software on the new machine since I was only given 1 Key instead of the ...

From new Publisher to old #2
Hi. I have constructed a document in Publisher XP, and I am now sitting at a computer with Publisher 2000. And I need the document now. Is there anyway that I online can convert the document. I realise I should have converted at the computer with the newer Publisher, and I though I did - but I must have forgotten. Is there any way I can get around my stupid mistake, or will I HAVE to pay? I will convert it for you. Send it to gsauer at columbus dot rr dot com It is very possible you will lose formatting and if images are involved, the file may increase quite a bit. -- Mary Sauer MSFT MV...

2 Servers
We have a new Windows Server 2008 as a Domain controller. We also have the existing Server with Windows Server 2003. We are thinking of using the older Windows Server 2003 as a Mail Server running Exchange Server 2007. How should we configure the Server runing Exchange SErver 2007: (a) as a Domain Controller (Backup Domain Controller) (b) as a Member Server Your response will be appreciated. Regards, Guido it doesn't matter.. if you will only run the mail server on it.. or you can create a virtual server and run only the mail server on it as well.. easier ...

Smart List export to Excel #2
Hi, We are using Dynamics 9.0 and Office 2003. When we try to do a Smart List export to Excel, we keep getting an "Exception_Class_Object_Exception" that references varying object errors, such as 'Cells' or 'Value'; the object errors will change each time we try an export. As a workaround, I've found that closing Excel before doing the export allows the export to complete successfully. However, that solution isn't acceptable, as our Dynamics users would like to be able to work in Excel, doing other tasks, while the export is processing. Does anyone...

How to print 2000+ characters seen in formula bar? #2
Formula bar shows +1024 characters, screen only shows 1024. Printer only prints 1024. How can I print all? Add some alt-enters to force new lines within the cell. Use a smaller font. Widen the column width lengthen the row height. Curious wrote: > > Formula bar shows +1024 characters, screen only shows 1024. Printer only > prints 1024. How can I print all? -- Dave Peterson ec35720@netscape.com Thanks, but none of your suggestions work. I'm using Excel 2003 by the way. "Dave Peterson" wrote: > Add some alt-enters to force new lines within the cell. &g...

Font problem #2
I'm using the free trial download of Publisher & at work I use Publisher 2003. Regardless of which one, the font is changing whenever I type a ' or press the spacebar. I'm using Helvetica, and Times New Roman is the default. The toolbar shows Helvetica, but these characters switch the font to TNR. I've searched the help menu which has been useless. Any suggestions would be great. Thanks! ...

Open file(2) from file(1) and then close file(1)
I need to open a file(2) when I open file(1) and then close file(1) File(1) name can change so I need variables. sequence will be Open File(1) Open File(2)... I do it by the following macro: Public LaunchFile As String 'in this public variable I want to keep file(1) name Private Sub Workbook_Open() Dim lDir, lFile, lFileMtr As String lDir = Sheets("launch").Range("B2") & "\" 'direction of my file to open lFile = Sheets("launch").Range("B3") & ".xls" 'file name to openm lFi...

OWA error message #2
I got "440 Login Timeout" when open OWA. Without SSL, it's OK. I am using Windows 2003 and Exchange 2003. Please help, Thank you inadcance. Lisa Have you installed the OWA hotfix and Exchange 2003 Service Pack 1? http://support.microsoft.com/?kbid=831464 This may help as well.... http://msdn.microsoft.com/library/en-us/e2k3/e2k3/_esdk_arch_webdav_security.asp Bob "Lisa" <anonymous@discussions.microsoft.com> wrote in message news:043a01c48fb9$4689e360$a401280a@phx.gbl... > I got "440 Login Timeout" when open OWA. Without SSL, > it's OK. ...

Publisher v 2.0
I have some files from an old 3.1 system that I would like to import into a newer v of Publisher. Can I open them with Publisher 2003 etc? You should be able to open them... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bill" <anonymous@discussions.microsoft.com> wrote in message news:081001c4e5f5$c7f92a40$a501280a@phx.gbl... >I have some files from an old 3.1 system that I would > like to import into a newer v of Publisher. Can I open > them with Publisher 2003 etc? >-----Original Message----- >...

Contacts with 2 or more Email addresses
I have a couple of contacts that have more than one email address. When I send an email, it wants to send it to ALL there email address when I ricght click on a persons contact name and choose create a new mail message. Is there a way to make it default to a certain email address? I have office 2007 on Windows XP delete the addresses you don;'t want to include. The send to contact action doesn't have a way to identify the address you wish to use, so rather than pop up a dialog first, it adds all the addresses so you can choose which address (or send to all). -- Diane Poremsky...

Outbound message queue #2
Hi I have Exchange 5.5 :( In the Internet Mail Service, Queues, Outbound messages awaiting delivery, there are hundreds of emails that look like SPAM. I know our Exchange Server is not a mail relay so where are all of these emails coming from and how do I stop them. I tried turning on all the message tracking and log file options I could find and looking at the log file but it didn't really show anything obvious except the domain "mail.ru" was on a like 30 messages. (I put "mail.ru" in the domain reject list) Can someone help me please................... Tha...

Date Header and Layout Questions
I have 1 table with headings of: TaskType SiteName StartDate EndDate On a report, I want the header to show every Monday's date going horizontally displaying every Monday from now until 2 years from now. Then I want the Vertical column to be the TaskType. Within the report I want the SiteName to be identified in both the Monday's Date Column and the TaskType Column. For example, 30-Nov-09 (Monday) TaskType-DSR SiteName - DC. The date being on the header, the TaskType on each row and the SiteName cross referenced between them. Does this make any sense? ...

Excel password #2
Hi all, I've an old worksheet in Excel 2k, and I forgot the password I saved with it. Is there a way to retrieve it? thanks N! Xau N! Xau, Workbook password to open???? If so, here's a post by Norman Harker that should give you a start. John <snip> Hi! If it is a workbook password then here is the standard post on that one: The following sites advertise ability to recover, amongst other things, Excel file passwords. These passwords are imposed when saving Excel files and are not to be confused with *internal* passwords on sheets and workbook structure. http://www.i...

Re: Front-end question
If i have 2 front-end and one crash, the other will function properly? or i must modify something? i must change the server (on clients) where all clients points? "Mark Arnold [MVP]" <mark@mvps.org> wrote in message news:oihld0ha1q9jacg65p4vjo3g7j2mmo5eq0@4ax.com... > "MMC" <ryuken_26@yahoo.it> wrote: > > >I don't understand very well, how FE server function.... > >Just download "Using Exchange 2000 Server and Exchange Server 2003 Front-End > >Servers"but i don't understand how FE and BE comunicate in a multiple server...