Excel- enter/tab choosing locked cell on sheet

[Pharmboy]

See that 'case 13 or 9, offset' cell piece?

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        If KeyCode = vbKeyF And Shift = 2 Then PrintARX1
        If KeyCode = vbKeyR And Shift = 2 Then ClearForm
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
    End Select
End Sub

In the sheet this is used, that offset (9 or 13 either one) will select a cell that is "locked" when I use tab or enter if a locked cell is in the way of the direction specified above. It will only select 1, and then continue on to the next appropriate 'unlocked' cell afterwards. But the FIRST time it can go down or over to a 'locked' cell from the unlocked cells, it will.
Is there a piece of code necessary to force it to always skip 'locked' cells on the sheet, even though those particular cells are set as 'locked' in the format? Something like instead of 'activecell.offset' it should be 'activeUNLOCKEDcell.offset' or something else?
Thanks.

[westconn1]

there may well be a better method, but try like this

Select Case KeyCode
        Case 9
            do
               ActiveCell.Offset(0, 1).Activate
            loop until activecell.locked = false
        Case Else
    End Select

edit i just noticed that each key was moving in different direction, so make similar loop for enterkey

[Pharmboy]

This partially works. It does not select the locked cell like it used to and stay there, so that's good. It does continue in that same column or row forever though if an unlocked cell isn't in it's path. So if I have 2 columns unlocked, and it hit tab on column 2, it loops forever in the same row > column 2 only. Same for rows going down and enter, it goes 'down rows' forever beyond my last row.

It's more complicated b/c my rows and columns don't match up perfectly, at least in the order I wish to select them with 'enter' or 'tab'.

Is it possible to do an 'if/then' type of statement where it doesn't loop but checks the cell, and if locked moves down rows until it finds the left-most available unlocked cell in the next row? That's the gist of what I'm looking for in either key function. That sounds complicated to me though...

[westconn1]

That sounds complicated to me though...

not really

case 9 
x = 3 ' number of columns to search for unlocked cell
for col = activecell.column to activecolumn.column + x
    for r = activecell.row to rows.count    ' change rows.count to bottom most cell you want to check in grid
        if not cells(r, col).locked then cells(r, col).activate:  fnd = true: exit for
    next
   if fnd then exit for
next

i did not test this, so allow for typos

[Pharmboy]

Ehh... You kinda lost me here. It gives errors and says variables aren't defined.
It doesn't like x, col, activecolumn (and others?) and says they are undefined variables. I do not know how to set them. Not knowing what it means, if I put "dim x as string" it gets past that one then to the next:

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        'Dim x As String
        'Dim col As String
        If KeyCode = vbKeyF And Shift = 2 Then PrintARX1
        If KeyCode = vbKeyR And Shift = 2 Then ClearForm
    Select Case KeyCode
        Case 9
        x = 4 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To activecolumn.Column + x
        For r = ActiveCell.Row To Rows.Count ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked Then Cells(r, col).Activate:  fnd = True: Exit For
        Next
        If fnd Then Exit For
        Next
            'ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
    End Select
End Sub

Either way I'm lost on this one. I think some things were assumed I put them in there, but I don't know what...

and should rows.count be replaced entirely with a cell # or actual #? Like "E25" or "25"?

[westconn1]

It gives errors and says variables aren't defined.

you have option explicit at the top of your code pane, so all variables must be defined

dim x as integer, r as long
if you do not know what type to declare as leave out the as part and it will be a variant, until you can figure something better

and should rows.count be replaced entirely with a cell # or actual #? Like "E25" or "25"?

you are just looking for a row number, rows.count returns 65535 up to excel 2003, later versions have more rows in each sheet

also you can test all cells in a column (or part of) for locked

if not range(activecell, cells(somerow, activecell.column)).locked  ' all cells unlocked
if isnull(range(activecell, cells(somerow, activecell.column)).locked)   ' mixed, some cells unlocked
' can be combined
if isnull(range("b1:b16").Locked) or not range("b1:b16").Locked then

[Pharmboy]

I could not get your code to work using 'tab' or 'enter' to get to the correct cells and not choose locked cells. It doesn't give an error, but the 'tab' function still selects a 'locked' cell that contains a function at this point:

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        Dim x As Integer
        Dim r As Long
        Dim col
        Dim fnd
        If KeyCode = vbKeyF And Shift = 2 Then PrintARX1
        If KeyCode = vbKeyR And Shift = 2 Then ClearForm
    Select Case KeyCode
        Case 9
        x = 4 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.Row To Rows.Count ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked Then Cells(r, col).Activate:  fnd = True: Exit For
        Next
        If fnd Then Exit For
        Next
            ActiveCell.Offset(0, 1).Activate
        Case 13
        'If ActiveCell = H3 And KeyCode = Enter Then GoTo D12
        'Else: ActiveCell.Offset(1, 0).Activate
        ActiveCell.Offset(1, 0).Activate
        Case Else
    End Select
End Sub

I'm wondering if I can't resolve this problem in a different way? There are a limited selection of cells that perform inappropriately. Would it be easier to say (as I tried) "if you are in cell H3 and you hit enter, then go to cell D12"? I don't know how that syntax must be written either, but it seems like it might be easier to have 6 or so instances of that type of code.

I cannot get the 'case 9' portion to work like we wish it to. Where am I going wrong? Thanks.

[westconn1]

i tested the code for case 9 works correctly for locked cells, as long as there is some unlocked cells, within the searched area, remove the last line as that will move the selection from the first unlocked cell, to one in the next column that may be locked

when you refer to cells that are locked, please clarify
the default in my workbook is all cells are locked unless specifically unlocked, either manually or by code, this does not prevent values from being changed unless the worksheet is protected

the alternative method you suggest can be worked, probably by using a second select case for each case, the second select would be activecell.address, based on the address you can activate some other cell

case "$H$3": range("d12").select  ' or (activate)

[Pharmboy]

OK, to clarify: the sheet is protected and the particular cells are specifically unlocked that I'm referring to try to switch between.

I removed the last line as you mentioned and that helped. These two key functions do not select locked cells anymore, so that's good! But it changed the key function a bit. Now, the cells w/ the combobox's in them act differently. When I use tab or enter in the combobox cells, it "re-selects" the cell before it moves on.

example- before this code in cell D12, if I hit enter once, it went to D13. Now, when in D12 (w/ the combobox) I hit enter once, it selects the entire cell D12. Then if I hit enter again, it moves on to D13. This change does NOT happen in cells without the combobox. From H1 to H2 for instance, straight general format cells, you only have to hit enter once for the move.

Also, this code did not quite "wrap" like I was hoping. For instance I need to go from H3 to D12. 'Tab' will do this, but 'enter' will not, as has always been the case. 'Enter' goes to the 'G' column instead, as I guess it is more directly below 'H' than 'D' is in columns. Is there anything to change this?

Here is the combobox code too w/ the key stuff, anything conspicuous to make it 'double-select' the combobox cells now on tab or enter keys?

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        Dim x As Integer
        Dim r As Long
        Dim col
        Dim fnd
        If KeyCode = vbKeyF And Shift = 2 Then PrintARX1
        If KeyCode = vbKeyR And Shift = 2 Then ClearForm
    Select Case KeyCode
        Case 9
        x = 4 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.Row To Rows.Count ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked Then Cells(r, col).Activate:  fnd = True: Exit For
        Next
        If fnd Then Exit For
        Next
            'ActiveCell.Offset(0, 1).Activate
        Case 13
        x = 4 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.Row To Rows.Count ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked Then Cells(r, col).Activate:  fnd = True: Exit For
        Next
        If fnd Then Exit For
        Next
            'ActiveCell.Offset(1, 0).Activate
        Case Else
    End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsL As Worksheet
Set ws = ActiveSheet
Set wsL = Worksheets("Control")
  On Error GoTo errHandler

'If Target.Count > 1 Then GoTo exitHandler-- why did this kill it for me?

  Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
  If cboTemp.Visible = True Then
    With cboTemp
      .Top = 10
      .Left = 10
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      .Value = ""
    End With
  End If

  On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    If Left(str, 8) = "INDIRECT" Then
        str = Replace(Replace(str, "INDIRECT(", ""), ")", "")
        str = ws.Range(str).Value
    End If
    With cboTemp
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  Resume exitHandler
End Sub

[westconn1]

for enter key you should change all rows to cols and cols to rows, so as to move down instead of across, or whatever

the cell with the combobox may not be the activecell even when the combobox receives keystrokes

[Pharmboy]

For some reason the enter key functions fine as-is, but I will switch the row-column on it.

Is there any way around the activecell-combobox double-keystroke issue?

If not, I will probably try to make it go from 1 cell to another based on keystroke instead.

[westconn1]

For some reason the enter key functions fine as-is, but I will switch the row-column on it.

if that works correctly then maybe it is the tab one that needs changing?
based on what you want to do, one should search by rows, the other by columns

Is there any way around the activecell-combobox double-keystroke issue?

i did not notice this when i was testing, but maybe i was using different combobox, or something, post a sample workbook that demonstrates the problem, zip before attaching

[Pharmboy]

Here's the example. The issues:

- need cell H3 to go to D12 on 'enter' key (it does on 'tab')
- need column D to not tab to the locked G column cells (the way it is now w/ your code it does not, but you have to 'double-tab' to move 1 cell. Without your portion of code on the 'tab' key it will select the locked white cells in column G).
- need cell D23 to go to E25 on 'enter' key (currently D23 goes to a locked-D24. Again, with your code it does not select D23 to D24, but it does not go to E25 either. It just cycles the D column up and down.)

You'll notice currently in the file, the 'enter' key has the original code that just moves it down a cell. It takes only 1 keystroke to move a cell. The 'tab' portion has the different code on it, and although it does not select the 'G' column or any other locked cells, it does take a double-tab now.
Thanks.

[westconn1]

each time i try to test with your file it crashes my excel, took a while to figure why

to avoid the double tab change the code to

Case 9
        x = 7 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.Row To 23  ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked And Not Cells(r, col).Address = ActiveCell.Address Then Cells(r, col).Activate: fnd = True: Exit For
        Next
        If fnd Then Exit For
        Next

see if this is improvement

maybe i need a diagram of where you want to go from each cell by tab or enter
draw some arrows or something one colour for tab

[Pharmboy]

The enter/tab now do not require a double-tab. I don't know how to modify this:

Case 9
        x = 10 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.row To 25  ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked And Not Cells(r, col).Address = ActiveCell.Address Then Cells(r, col).Activate: fnd = True: Exit For
        Next

to work for 'tab' though. It is tabbing down now instead of over and I cannot see why based on the above. I can't change it to work.

As far as what function I still need to do what. I would take just one change. I would have 'enter' from cell H3 -> D12. Currently with 'enter' H3 -> G13, but I wish it would go to D12. In post#3 I mentioned about finding the 'left-most' cell on the 'enter' key going down. This is what I was referring to: the enter key going from H3 to D12 in the above file.

I figure these must all run together for you. You're probably helping about 23 poor foolish souls like me with their fun work! Thank you for that...

[westconn1]

try

case 9
  if activecell.address = "$H$3" then range("d12").activate: exit sub
x = 10 ' number of columns to search for unlocked cell
        For r = ActiveCell.row To 25  ' change rows.count to bottom most cell you want to check in grid
        For col = ActiveCell.Column To ActiveCell.Column + x        
       If Not Cells(r, col).Locked And Not Cells(r, col).Address = ActiveCell.Address Then Cells(r, col).Activate: exit sub
        Next 
      next

[Pharmboy]

Well, it didn't work. I don't know what to say . The 'enter' key doesn't use the H3 to-> D12 portion. And now the 'tab' is back to double-keystrokes while moving from the comboboxes. Check the actual file if you wish, thanks:

Select Case KeyCode
        Case 9
        x = 10 ' number of columns to search for unlocked cell
        For r = ActiveCell.row To 25  ' change rows.count to bottom most cell you want to check in grid
        For col = ActiveCell.Column To ActiveCell.Column + x
        If Not Cells(r, col).Locked And Not Cells(r, col).Address = ActiveCell.Address Then Cells(r, col).Activate: fnd = True: Exit For 'Exit Sub
        Next
        If fnd Then Exit For
        Next
            'ActiveCell.Offset(0, 1).Activate
        Case 13
        If ActiveCell.Address = "$H$3" Then Range("D12").Activate: Exit Sub
        x = 10 ' number of columns to search for unlocked cell
        For col = ActiveCell.Column To ActiveCell.Column + x
        For r = ActiveCell.row To 25  ' change rows.count to bottom most cell you want to check in grid
        If Not Cells(r, col).Locked And Not Cells(r, col).Address = ActiveCell.Address Then Cells(r, col).Activate: Exit Sub 'fnd = True: Exit For
        Next
        'If fnd Then Exit For
        Next
            'ActiveCell.Offset(1, 0).Activate
        Case Else
    End Select

[westconn1]

as the combobox never goes to H3 the code for combobox keypress is never called you would need to add code in selectionchange event
try this change

On Error GoTo errHandler
 
'If Target.Count > 1 Then GoTo exitHandler-- why did this kill it for me?
If Target.Address = "$H$4" Then Range("d12").Activate: Exit Sub
  Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next

the double tab effect is because cells in cols e and f are not locked so can be selected under the combobox

[Pharmboy]

1- The addition of line #4 above did not change the action as desired. I tried to change 'target.address' to 'activecell.address' for fun to no avail.

2- I cannot lock cols e and f since they are merged. So the tab will have to be double tab effect as is?

[westconn1]

1- The addition of line #4 above did not change the action as desired. I tried to change 'target.address' to

worked when i tested before
it would also depend on your setting, which way the enter key moves the selection, mine is down, hence target of H4

So the tab will have to be double tab effect as is?

i am sure the would be some work around, try

For col = ActiveCell.Column To ActiveCell.Column + x step3

[Pharmboy]

My enter moves down also. This was a hapenstance, but I found out: when the sheet is locked, H4 is not an option and the code doesn't work. When I unlock the sheet, the code works. Something necessary to put in for the difference of the locked sheet?

[westconn1]

i have tested with the sheet (as downloaded) both locked and unlocked, from h3 i go to d12, in fact even if i click on h4 i go to d12
the only other consideration is if different versions of excel work differently in this area

on further consideration, the code i had suggested originally (before i had seen the workbook) may not be the best for this application, and continually trying to fix may not be the best solution

apart from h3, all the other places you want the enter or tab to work from are d12:d23 and g12:g23?

on enter
1) in d column you want to move down 1 row?
2) in g column next row unlocked 2nd row?

on tab
1) from d move to g if unlocked, where if locked?
2) from g to where?

on row 23 got to E25?

[Pharmboy]

From H3, no matter if enter or tab, go to D12.

Yes to what you have described for 'enter', down one row in all of column d and skip every other in column g as every other is locked.

For tab, from d to g if unlocked, and simply down 1 if g=locked.

on row 23:
if column d, then go to e25 on enter only and tab still goes to column g
if column g row 23 and tab OR enter, then go to e25

I'm using excel 2003 sp3 unfortunately.

[Pharmboy]

I could unlock H4 and D24 and implement the above code that works on onlocked cells w/ this version. But that would be like cheating, no?

[westconn1]

you can try this it seemed to work correctly, except that everytime it activated a cell in col G it selected, but excel crashed

Select Case KeyCode
        Case 9
            If ActiveCell.Row = 23 Then Range("e25").Activate: Exit Sub
            If ActiveCell.Column = 4 Then
                If Not ActiveCell.Offset(, 1).Locked Then
                    ActiveCell.Offset(, 1).Activate:exit sub
                    Else: ActiveCell.Offset(1).Activate
                End If
            End If
            If ActiveCell.Column = 7 Then ActiveCell.Offset(1, -1).Activate
        Case 13
            If ActiveCell.Row = 23 Then Range("e25").Activate: Exit Sub
            If ActiveCell.Column = 4 Then ActiveCell.Offset(1, 0).Activate
            If ActiveCell.Column = 7 Then ActiveCell.Offset(2).Activate
        Case Else
    End Select

[Pharmboy]

I had to take out your line #3 above, as I didn't want 'tab' to move to row 25 unless it was from 'column 7'. I didn't need to add anything for it b/c it acts appropriately going from g23 to e25 on it's own.

Everything seems to work good, except that old pesky "h3 to d12" issue. That still doesn't work. I tried adding:
If Target.Address = "$H$4" Then Range("D12").Activate: Exit Sub
If ActiveCell.row = 4 Then Range("d12").Activate: Exit Sub
to either the 'case 13' key or the 'selection change' combobox portion and neither worked.

If I unlock cell H4, either works! It just doesn't seem to like it locked, like since the 'target' or 'row' is never active, it doesn't trigger it.

Should I just leave cell H4 unlocked and move on since that appears to work? I hate to spend too much of your time or keep crashing your excel for some reason!

[westconn1]

I tried adding:
If Target.Address = "$H$4" Then Range("D12").Activate: Exit Sub
If ActiveCell.row = 4 Then Range("d12").Activate: Exit Sub
to either the 'case 13' key or the 'selection change' combobox portion and neither worked.

it will not work in the combobox keypress event at all
it must be selection change event

i am used to excel crashing now, save everything before running any code

i can not compensate for any differences in the way excel 2000 handles selecting of locked cells, compared to your version (or other versions)

[Pharmboy]

OK so I guess I will just unlock H4 to get that portion to work.

One more thing- try this file and keep hitting the tab key from cell d12. Does it double-back on itself in the same row sometimes for you too? It's hard to explain and doesn't happen the first couple of tabs for me. Then from like d15 on down, if it can tab to the g column it does, then when you press tab again, it goes back to the d column in the same row. Then back to g again, and then it moves down a row. So it goes (with all tab hits) to cells- d14,d15,g15,d15,g15,d16,d17,g17,d17,g17,d18 etc...

Does it work that way for you too? Thanks.

[westconn1]

it was unable to test fully when i tried last night as excel would crash, though this does not seem to be a problem for you, at least you have not said so
i will look again tonight, also if possible try on another computer, amazing what will work on one and not another

[Pharmboy]

I have tried this file on all the computers I can here at work, and it runs fine. Never crashed once at any point through all this testing. This place uses Office Professional edition, Excel 2003 SP3.