| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro type
I have the logic in a macro to turn off the scroll lock button, but now I'm
wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Why are you turning off scroll lock. That is a setting that affects all
programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
The end user is inputting information on a page where only selected cells
available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
A couple of questions what version of XL are the users using? Is the sheet
protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
The end users will be using either XL 2003 or 2007.
The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
When you protect the workbook are you allowing the user to select locked
cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
The problem is that with the scroll lock button on.
If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Give this a whirl... It checks the state of the scroll lock key. If it is on
then it disables the arrow keys. Place this code in a regular code module similar to where recorded code is placed... Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer Private Const m_cCapital = &H14 Private Const m_cNumlock = &H90 Private Const m_cScrollLock = &H91 Private Const m_cInsert = &H2D Public Function CapsLock() As Boolean CapsLock = CBool(GetKeyState(m_cCapital)) End Function Public Function NumLock() As Boolean NumLock = CBool(GetKeyState(m_cNumlock)) End Function Public Function ScrollLock() As Boolean ScrollLock = CBool(GetKeyState(m_cScrollLock)) End Function Public Function InsertKey() As Boolean InsertKey = CBool(GetKeyState(m_cInsert)) End Function And place this code into the ThisWorkbook module (right click the XL icon next to the word file in the upper left corner of XL and select view code)... Option Explicit Private Sub Workbook_Activate() Call DisableArrows End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call EnableArrows End Sub Private Sub Workbook_Deactivate() Call EnableArrows End Sub Private Sub Workbook_Open() Call DisableArrows End Sub Private Sub EnableArrows() With Application .OnKey "{Up}" .OnKey "{Down}" .OnKey "{Right}" .OnKey "{Left}" End With End Sub Private Sub DisableArrows() If ScrollLock Then With Application .OnKey "{Up}", "" .OnKey "{Down}", "" .OnKey "{Right}", "" .OnKey "{Left}", "" End With End If End Sub -- HTH... Jim Thomlinson "Brad" wrote: The problem is that with the scroll lock button on. If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Jim,
Thank you for the taking time to help me with this. Only one thing. I don't want to disable the arrow keys if the Scroll lock is on - however if the end-user selects an arrow key - Can I force an "enter" command so that the value is inserted in the cell and then the arrow key can take the end user in the direction desired? "Jim Thomlinson" wrote: Give this a whirl... It checks the state of the scroll lock key. If it is on then it disables the arrow keys. Place this code in a regular code module similar to where recorded code is placed... Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer Private Const m_cCapital = &H14 Private Const m_cNumlock = &H90 Private Const m_cScrollLock = &H91 Private Const m_cInsert = &H2D Public Function CapsLock() As Boolean CapsLock = CBool(GetKeyState(m_cCapital)) End Function Public Function NumLock() As Boolean NumLock = CBool(GetKeyState(m_cNumlock)) End Function Public Function ScrollLock() As Boolean ScrollLock = CBool(GetKeyState(m_cScrollLock)) End Function Public Function InsertKey() As Boolean InsertKey = CBool(GetKeyState(m_cInsert)) End Function And place this code into the ThisWorkbook module (right click the XL icon next to the word file in the upper left corner of XL and select view code)... Option Explicit Private Sub Workbook_Activate() Call DisableArrows End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call EnableArrows End Sub Private Sub Workbook_Deactivate() Call EnableArrows End Sub Private Sub Workbook_Open() Call DisableArrows End Sub Private Sub EnableArrows() With Application .OnKey "{Up}" .OnKey "{Down}" .OnKey "{Right}" .OnKey "{Left}" End With End Sub Private Sub DisableArrows() If ScrollLock Then With Application .OnKey "{Up}", "" .OnKey "{Down}", "" .OnKey "{Right}", "" .OnKey "{Left}", "" End With End If End Sub -- HTH... Jim Thomlinson "Brad" wrote: The problem is that with the scroll lock button on. If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Take a look at the onkey in the msdn help... You can run whatever macor you
want when the key is pressed. I am not too sure what you might want when the up or left arrow are pressed??? http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx -- HTH... Jim Thomlinson "Brad" wrote: Jim, Thank you for the taking time to help me with this. Only one thing. I don't want to disable the arrow keys if the Scroll lock is on - however if the end-user selects an arrow key - Can I force an "enter" command so that the value is inserted in the cell and then the arrow key can take the end user in the direction desired? "Jim Thomlinson" wrote: Give this a whirl... It checks the state of the scroll lock key. If it is on then it disables the arrow keys. Place this code in a regular code module similar to where recorded code is placed... Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer Private Const m_cCapital = &H14 Private Const m_cNumlock = &H90 Private Const m_cScrollLock = &H91 Private Const m_cInsert = &H2D Public Function CapsLock() As Boolean CapsLock = CBool(GetKeyState(m_cCapital)) End Function Public Function NumLock() As Boolean NumLock = CBool(GetKeyState(m_cNumlock)) End Function Public Function ScrollLock() As Boolean ScrollLock = CBool(GetKeyState(m_cScrollLock)) End Function Public Function InsertKey() As Boolean InsertKey = CBool(GetKeyState(m_cInsert)) End Function And place this code into the ThisWorkbook module (right click the XL icon next to the word file in the upper left corner of XL and select view code)... Option Explicit Private Sub Workbook_Activate() Call DisableArrows End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call EnableArrows End Sub Private Sub Workbook_Deactivate() Call EnableArrows End Sub Private Sub Workbook_Open() Call DisableArrows End Sub Private Sub EnableArrows() With Application .OnKey "{Up}" .OnKey "{Down}" .OnKey "{Right}" .OnKey "{Left}" End With End Sub Private Sub DisableArrows() If ScrollLock Then With Application .OnKey "{Up}", "" .OnKey "{Down}", "" .OnKey "{Right}", "" .OnKey "{Left}", "" End With End If End Sub -- HTH... Jim Thomlinson "Brad" wrote: The problem is that with the scroll lock button on. If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Sorry... Now that I have re-read your description there is a problem. Macros
do not fire while you are in edit mode. So there is no way to catch that the users hit the arrow key (I thought you just wanted to disable the keys). My suggestion at this point would be to just prompt the user with a message that their scroll lock is on. My event code in thisworkbook would be the place to do it. You can then offer to change it for them using your code. If they do not elect to turn the scroll lock off then disable the arrow keys. So long as you properly inform the user of what is going on then changing their setting is not a big deal... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Take a look at the onkey in the msdn help... You can run whatever macor you want when the key is pressed. I am not too sure what you might want when the up or left arrow are pressed??? http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx -- HTH... Jim Thomlinson "Brad" wrote: Jim, Thank you for the taking time to help me with this. Only one thing. I don't want to disable the arrow keys if the Scroll lock is on - however if the end-user selects an arrow key - Can I force an "enter" command so that the value is inserted in the cell and then the arrow key can take the end user in the direction desired? "Jim Thomlinson" wrote: Give this a whirl... It checks the state of the scroll lock key. If it is on then it disables the arrow keys. Place this code in a regular code module similar to where recorded code is placed... Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer Private Const m_cCapital = &H14 Private Const m_cNumlock = &H90 Private Const m_cScrollLock = &H91 Private Const m_cInsert = &H2D Public Function CapsLock() As Boolean CapsLock = CBool(GetKeyState(m_cCapital)) End Function Public Function NumLock() As Boolean NumLock = CBool(GetKeyState(m_cNumlock)) End Function Public Function ScrollLock() As Boolean ScrollLock = CBool(GetKeyState(m_cScrollLock)) End Function Public Function InsertKey() As Boolean InsertKey = CBool(GetKeyState(m_cInsert)) End Function And place this code into the ThisWorkbook module (right click the XL icon next to the word file in the upper left corner of XL and select view code)... Option Explicit Private Sub Workbook_Activate() Call DisableArrows End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call EnableArrows End Sub Private Sub Workbook_Deactivate() Call EnableArrows End Sub Private Sub Workbook_Open() Call DisableArrows End Sub Private Sub EnableArrows() With Application .OnKey "{Up}" .OnKey "{Down}" .OnKey "{Right}" .OnKey "{Left}" End With End Sub Private Sub DisableArrows() If ScrollLock Then With Application .OnKey "{Up}", "" .OnKey "{Down}", "" .OnKey "{Right}", "" .OnKey "{Left}", "" End With End If End Sub -- HTH... Jim Thomlinson "Brad" wrote: The problem is that with the scroll lock button on. If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro type
Jim,
Thanks for the nth time. Brad "Jim Thomlinson" wrote: Sorry... Now that I have re-read your description there is a problem. Macros do not fire while you are in edit mode. So there is no way to catch that the users hit the arrow key (I thought you just wanted to disable the keys). My suggestion at this point would be to just prompt the user with a message that their scroll lock is on. My event code in thisworkbook would be the place to do it. You can then offer to change it for them using your code. If they do not elect to turn the scroll lock off then disable the arrow keys. So long as you properly inform the user of what is going on then changing their setting is not a big deal... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Take a look at the onkey in the msdn help... You can run whatever macor you want when the key is pressed. I am not too sure what you might want when the up or left arrow are pressed??? http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx -- HTH... Jim Thomlinson "Brad" wrote: Jim, Thank you for the taking time to help me with this. Only one thing. I don't want to disable the arrow keys if the Scroll lock is on - however if the end-user selects an arrow key - Can I force an "enter" command so that the value is inserted in the cell and then the arrow key can take the end user in the direction desired? "Jim Thomlinson" wrote: Give this a whirl... It checks the state of the scroll lock key. If it is on then it disables the arrow keys. Place this code in a regular code module similar to where recorded code is placed... Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer Private Const m_cCapital = &H14 Private Const m_cNumlock = &H90 Private Const m_cScrollLock = &H91 Private Const m_cInsert = &H2D Public Function CapsLock() As Boolean CapsLock = CBool(GetKeyState(m_cCapital)) End Function Public Function NumLock() As Boolean NumLock = CBool(GetKeyState(m_cNumlock)) End Function Public Function ScrollLock() As Boolean ScrollLock = CBool(GetKeyState(m_cScrollLock)) End Function Public Function InsertKey() As Boolean InsertKey = CBool(GetKeyState(m_cInsert)) End Function And place this code into the ThisWorkbook module (right click the XL icon next to the word file in the upper left corner of XL and select view code)... Option Explicit Private Sub Workbook_Activate() Call DisableArrows End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call EnableArrows End Sub Private Sub Workbook_Deactivate() Call EnableArrows End Sub Private Sub Workbook_Open() Call DisableArrows End Sub Private Sub EnableArrows() With Application .OnKey "{Up}" .OnKey "{Down}" .OnKey "{Right}" .OnKey "{Left}" End With End Sub Private Sub DisableArrows() If ScrollLock Then With Application .OnKey "{Up}", "" .OnKey "{Down}", "" .OnKey "{Right}", "" .OnKey "{Left}", "" End With End If End Sub -- HTH... Jim Thomlinson "Brad" wrote: The problem is that with the scroll lock button on. If the end user enters in an item and then uses an arrow key XL assumes that additional information in being entered in the field. Example one field has the user could enter "Male" or "Female" In the field right below it "age" could be entered. With the scroll lock off - Entering "Male" arrow key down Male is entered correctly in the correct field and the cursor goes down one cell With the scroll lock on - Entering "Male" arrow key down +c9+Male is now in the cell that only "Male" be in. Which a non desirable event. I want to avoid this situation - the chance that the scroll lock key is activated and they use the arrow key to move to the next cell. My original choice was to deactivate scroll lock. You have stated that that might not be the best choice. So I have suggested that adding functionality to the arrow key might do the trick. Do this now make sense. "Jim Thomlinson" wrote: When you protect the workbook are you allowing the user to select locked cells? What is the problem if they use the arrow keys? They can use the mouse just as well... -- HTH... Jim Thomlinson "Brad" wrote: The end users will be using either XL 2003 or 2007. The sheet is protected and appropriate cells have been "Unlocked" I would prefer not to disable the arrow keys. However, when the scroll lock in on using the arrow keys does not provide the desired effect. I understand that disabling the Scroll Lock has other effects - that is why I threw out the idea that when an arrow key is hit - VBA code would activate - pretend that the enter key would be selected and then the normal arrow key function would be performed. Do you have a better suggestion? "Jim Thomlinson" wrote: A couple of questions what version of XL are the users using? Is the sheet protected and if not would it be ok to protect the sheet? If you protect the sheet then users can not enter data into any cells that they want, only the unlocked cells. If your users are all 2002 or better then when you protect the sheet you can select that the users can only select unlocked cells maning that they can not even select the cells they are not supposed to change. Finally if you still want to disable the arrow keys then you could use something like this... application.onkey "{Right}", "" 'diables application.oneky "{Right}" 'enables Check out this link http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx To do this you will need to still do some event programming to toggle these settings when the workbook is opened, activated or deactivated (possibly more if this is intended to be sheet specific)... -- HTH... Jim Thomlinson "Brad" wrote: The end user is inputting information on a page where only selected cells available to be changed. Having the scroll lock on is causing additional problems if the end user types information in one cell and before hitting enter uses on of the arrow keys. Turning off the scroll lock would "fix" this problem. Is there a better "fix"? I guess that if they hit an arrow key, I could tell excel to "enter" and then move. If this is better, is the code for this rather easy? "Jim Thomlinson" wrote: Why are you turning off scroll lock. That is a setting that affects all programs that are running. You are not only changing how XL works but how all programs work. While I am not saying don't do it I am saying be very careful here... I personally would not be heading down that path. You need to catch not only when the workbook is activated/deactivated but also when XL is activated/deactivated. -- HTH... Jim Thomlinson "Brad" wrote: I have the logic in a macro to turn off the scroll lock button, but now I'm wondering what the macro type should be. Do I need it in two places - one at the workbook level to make sure that it is turn off once the workbook is loaded and another at the workbook change level to ensure that it doesn't get selected while in the application? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop type formula - no Macro | Excel Discussion (Misc queries) | |||
| Macro error type mismatch | Excel Discussion (Misc queries) | |||
| macro to display items of a same type | Excel Programming | |||
| Macro to do the sum at the last when ever type chages | Excel Programming | |||
| Addition type macro | Excel Discussion (Misc queries) | |||
Linear Mode
