By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Tab key won't work

P: 4
I have created a protected worksheet that allows the user to input data into certain cells of the worksheet. They can alter the whole worksheet through some command buttons that I have created. The VB code I have used to re-protect the worksheet is as follows.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

The problem I want to fix is in these protected sheets the tab key won't work. More specifically I want the user to be able to navigate through the columns Mon thru Fri using the tab key instead of using arrow keys or mouse clicks.

If you are unfamiliar with this procedure in excel: if I start in (row 2, Mon), tab, (row 2, tues) tab (wed) tab (thurs) tab (fri) enter (row 3, Mon). This allows much easier data entry.

Can you help me turn the tab key back on?

Jan 11 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,434
We might need more detail (and perhaps to see more of the code) before we can help much. But you do know, don't you, that if a range is selected, tab can only move the focus around within that selection?
Jan 11 '08 #2

P: 4
Here is the code for creating a new week (worksheet) in my workbook. One of a number of similar command buttons that needs to unprotect and re-protect sheets.

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddWeek_Click()
  2.     Dim Week_of, week, Prev_week, prev_bal As String, num As Integer
  3.     num = 0
  4.     ActiveWorkbook.Unprotect
  5.     For Each ws In Worksheets
  6.         num = num + 1
  7.     Next ws
  8.     Prev_week = Sheets(num).Name
  9.     prev_bal = "='" & Prev_week & "'!K3"
  10.     week = month & "/" & day & "/" & year
  11.     Week_of = month & "_" & day & "_" & year
  12.     Sheets("Template").Select
  13.     Sheets("Template").Copy After:=Sheets(num)
  14.     Sheets("Template (2)").Select
  15.     Sheets("Template (2)").Name = Week_of
  16.     ActiveSheet.Unprotect
  17.     Sheets(Week_of).Range("H3").Formula = prev_bal
  18.     Sheets(Week_of).Range("H3").Select
  19.     Selection.Copy
  20.     Sheets(Week_of).Range("H4").Select
  21.     Sheets(Week_of).Range(Selection, Selection.End(xlDown)).Select
  22.     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
  23.         SkipBlanks:=False, Transpose:=False
  24.     Sheets(Week_of).Range("V1").Select
  25.     ActiveCell = "Attendance Records for the week of " & week
  26.     Sheets(Week_of).Range("B3").Select
  27.     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  28.     ActiveWorkbook.Protect Structure:=True, Windows:=False
  29.     Unload Me
  30. End Sub
Note: month, day and year are input from a form

I do not specify a range for the protection in each sheet for 2 reasons:
a) The range is in the copied sheet "Template"
b) Specifying a range kept giving me an error message
But the desired range is kept throughout all sheets.

I didn't know that the tab key worked only in the selected range (which makes sense), but regardless it is not working in that range either.

Just protecting the sheet in excel produces the same result, the tab key won't work. Excel 2002 is being used.

Hopefully this is enough info.
Jan 11 '08 #3

Expert 5K+
P: 8,434
Where are you leaving the focus? I think I also recall noticing that Tab won't work in a protected sheet unless you start out in an unprotected cell. I could be wrong, but it's probably worth checking.
Jan 13 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.