Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
Rich
The code is change event code which runs on a cell by cell basis as text is entered in column 12(L). Not a macro to be run once. Gord Dibben MS Excel MVP On Sun, 20 Aug 2006 18:11:01 -0700, Rich wrote: question 1 = put the code in a open_workbook event so the code auto runs the macro when the book is opened question 2 = you could protect the cells you which data to be entered but allow data entry only , "John Calder" wrote: Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell me how I might be able to do this? Question 2 The worksheet I have is protected and contains a number of cells that allow data entry. The problem I have is that a number of people use this spreadsheet and some of these users change the formatting in the cells that allow data entry. Is there any code that I can add that would still allow the data entry but not allow changes to the formatting? Any help is much appreciated. John |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
You cannot stop them getting that message without setting your security
setting to low (which is a very bad idea), but you can take some defensive action. The standard way to approach this is as follows. - create a worksheet with a message on explaining that for this workbook to run it needs macros enabled, maybe even a few screenshots - hide all other worksheets] - add some code in the Workbook_Open event that un hides the other sheets, but hides that sheet. What happens is that if they do not enable macros, they will only see the warning sheet, telling them how to do it. If the enable macros, it will startup as the workbook it should be. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "John Calder" wrote in message ... Thank you for your response. The problem that I am having is that although the code works fine, some of the users of this spreadsheet take the option of disabling the macros when opening the file. As the code is designed to enforce capitals in column 12 when the "disable macros" option is taken when opening the spreadsheet, the enforced capitals no longer works. What I would like is that when the spreadsheet opens, that the user does not get the option of enable/disable macros but the code still runs. If there is some code that could do this and if I knew where to put it then maybe this would solve my problem. Thanks John "Gord Dibben" wrote: John This is event code. The code does run automatically whenever you enter text in any cell in column 12 which is column L. No need to remove anything from the code. On August 9th you posted that you had everything worked out. What transpired since then? Are you having a problem? Gord Dibben MS Excel MVP On Sun, 20 Aug 2006 17:14:02 -0700, John Calder wrote: Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell me how I might be able to do this? Question 2 The worksheet I have is protected and contains a number of cells that allow data entry. The problem I have is that a number of people use this spreadsheet and some of these users change the formatting in the cells that allow data entry. Is there any code that I can add that would still allow the data entry but not allow changes to the formatting? Any help is much appreciated. John |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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" wrote: Lets' be honest, the idiots are smarter than us <g Gord Dibben MS Excel MVP |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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" wrote: Lets' be honest, the idiots are smarter than us <g Gord Dibben MS Excel MVP |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 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 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 Gord Dibben MS Excel MVP |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
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 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 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) |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
2 Questions
John
Was a good lesson for me also. Appreciate you sticking with me as I fumbled through from one step to the next. Now, I just hope no user actually does disable macros<g There are methods of dealing with that also, but let's wait and see. Gord On Wed, 23 Aug 2006 18:15:02 -0700, John Calder wrote: 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 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 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) Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping status on posed questions in Excel | Excel Discussion (Misc queries) | |||
I'm having trouble finding my posted questions? | Excel Discussion (Misc queries) | |||
VLOOKUP - 2 Questions | Excel Worksheet Functions | |||
Excel - how can I practice formulas on financial questions? | Excel Discussion (Misc queries) | |||
Pivot Table for survey data w/ questions as Rows & poss answrs as | Excel Discussion (Misc queries) |