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
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
...
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
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
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