Worksheet specific code

[Wescotte]

I have a worksheet setup as a form where the user can enter data. I
allow the tab/right and shift+tab/left to navigate to the next and
previous cells where form data is to be filled in. However if the user
navigates to another worksheet or even workbook the changes are global.
How would I go about making the sub below specific to worksheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count > 1 Then
Exit Sub
End If

Application.OnKey "{TAB}", "MoveNext"
Application.OnKey "{RIGHT}", "MoveNext"
Application.OnKey "+{TAB}", "MovePrev"
Application.OnKey "{LEFT}", "MovePrev"
End Sub

Thanks
Eric

 

[Tom Ogilvy]

You would have to use the worksheet activate and deactivate events to set
and reset the OnKey settings.

http://www.cpearson.com/excel/events.htm
Chip Pearson's overview of Events.

 

[Harald Staff]

Hi Eric

Try putting this in the ThisWorkbook module and remove your SelectionChange
code from the worksheet:

Private Sub Workbook_Activate()
Call Workbook_SheetActivate(ActiveSheet)
End Sub

Private Sub Workbook_Deactivate()
Call Worksheet_Deactivate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" Then
Application.OnKey "{TAB}", "MoveNext"
Application.OnKey "{RIGHT}", "MoveNext"
Application.OnKey "+{TAB}", "MovePrev"
Application.OnKey "{LEFT}", "MovePrev"
Else
Application.OnKey "{TAB}"
Application.OnKey "{RIGHT}"
Application.OnKey "+{TAB}"
Application.OnKey "{LEFT}"
End If
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "{RIGHT}"
Application.OnKey "+{TAB}"
Application.OnKey "{LEFT}"
End Sub

HTH. Best wishes Harald

 

[Wescotte]

Hmm that worked great thanks for the help.. Got another question.. I'm
using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

and for specific cells I'm verifying the input is valid and making sure
the format options are set correctly.. I need to do two more things..

1. Check if any format changes occured to specific cells and if they
have call a sub to restore them to the values I have preset in my code.
2. Allow the user to delete complete rows if the selected rows are say

row 12

Right now I just have it on a cell being changed verify the data is
correct and then force the cell format options to my specific values
(font size, alignment and boarder otions etc etc). This works but it
may slow things down to constantly be changing these values.. Granted I
call Application.ScreenUpdating = False before making any changes but
I'm still not sure if it's the optimal way to do this.

The only problem is if the user selects a range and deletes the cells
from teh sheet. It loops thru each cell and performs all my validation
checking on each one.. It doesn't actually ever delete the cell from
the sheet. How can I detect if the user has selected a row and is
attempting to delete it?

This is a quick overview of what I'm actually doing right now

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each Rng In Target
With Rng

' START SUDO CODE
if .Row = Y and .Column = X Then
if Contents are valid Then
Reset Format Options to defaults
else
.Value = ""
End If

'END SUDO CODE

End With
Next Rng
Application.EnableEvents = True
Application.ScreenUpdating = True