Excel: protected sheet TAB question 2

[DajOne]

Other than vertical or horizontal, is there a way to specify, in a protected worksheet, the sequence of cells accessed with the TAB key?

Example. Cell A2 B3 A4 B5 are unprotected for the user to enter data, the rest of the spreadsheet is protected. In an horizontal mode, the TAB sequence will be A2, A4, B3, B5. How can I make the TAB jump from lets say B5, B3, A2, A4 in that order?

Thanks

 

[xlbo]

Use a combination of selection_change and worksheet_change events.

Use the selection_change event to update a public variable holding the address of the selection

In the change event, compare that within a SELECT CASE statement to obtain the next cell to move to eg

Case "$B$3"
range("$A$2").select
case "$A$2"
range("$A$4").select
...


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[combo]

You can combine 'OnKey' action with ID property for cells.
code in a standard module:

Public Sub GoToNextCell()
If ActiveCell.ID <> "" Then
    Range(ActiveCell.ID).Select
End If
End Sub

code in worksheet's module:

Private Sub Worksheet_Activate()
With Me
    .Range("B5").ID = "B3"
    .Range("B3").ID = "A2"
    .Range("A2").ID = "A4"
    .Range("A4").ID = "B5"
End With
Application.OnKey "{TAB}", "GoToNextCell"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}", ""
End Sub

combo

 

[DajOne]

I want to thank you both for the solutions you have provided. I am very sorry I cannot appreciate your solutions as I do not understand the language it is written in or where to put it. BUT I am looking into this and will do my best to make it work...

Thanks to you two..

 

[pbrodsky]

If you are not familiar with code (and it DOES pay to become familiar with it), the only other approach I can think of would be to create a new sheet for the users to use for input (with the cells in the order you want them to use), and link to those cells from the 'real' data sheet.

 

[xlbo]

For info, it is VBA and would go in a SHEET module within the Visual Basic Editor (ALt+F11 to bring it up)

I guess, by your answer that you were looking for a non-VBA solution. Unfortunately, I don't think that there is one in this case. You can force excel to only allow access to certain cells but you can't set a default move order...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DajOne]

pbrodsky, unfortunately this is not possible due to the complexity of the spreadsheet but thanks anyway..True it would have PAID if I would know VBA.

Geoff, thanks for the info you gave me. I looked at examples in MS help and understand where it comes from..

Next step is the diff between a standard and worksheet module...

Thanks again !!!

 

[xlbo]

Standard module is access by following
Insert>Module
from the VBE menu - this would hold general subs / code / functions

A SHEET module can be access either by right clicking on the sheet tab and choosing "View Code" or by double clicking on the sheet in the Project Explorer window of the VBE

These modules typically hold subs / functions which are relevant to that sheet only. It can also hold "Events" which fire (run) at certain times. These can be selected by choosing "Worksheet" from the left dropdown. Once this is done, the available events are shown by clicking on the RIGHT dropdown

The one's you would need for this are the SELECTION_CHANGE event (fires when the selection on a worksheet changes) and the CHANGE event (fires when a cell's content is changed)

Have a look in the help files for keyword "Target". This is used in both of the above and can be used to refer to either the newly selected cell or the cell that was changed (dependant on the event that is running)

Hope that helps (or at least gives you somewhere to start looking)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[combo]

When you are within the Visual Basic Editor, click 'Insert' menu, choose 'Module'. This will insert a standard module into active VBA project (VBA items for a given workbook). This is a place to write regular functions and procedures.
The VBA projects and their structure are displayed in a Project Explorer window. If you can't see it, it cat be displayed using 'View' menu.
Sheets items and ThisWorkbook in a Project Explorer window represent worksheets and workbook objects, when you double-click their icon, a worksheet/workbook module is displayed. You can put event handling procedures here, as 'Worksheet_Activate' for instance. They are executed automatically, when the event occurs.

combo

 

[DajOne]

Well I did 'input' my cell range in the worksheet module and input the GoToNextCell subroutine (?) in the standard module.

My eyes were too big I guess.. The selectionChange inputs Private Sub Worksheet_SelectionChange(ByVal Target As Range) as code.. Dont get that one yet.

The boss is yelling at me to do something else than this.. (LOL).. Will be back at it tomorrow..

Thanks for all the help..

 

[xlbo]

Dajone - combo's code should work as is.

Simply copy the 1st sub into a STANDARD module and the code in the 2nd box into the WRKSHEET module

Select one of your 4 cells in question and press TAB

Just tried it on mine and works very nicely

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DajOne]

I tried to do exactly what combo's instructions were. After I protect the worksheet, the tabbing goes in horizontal mode...

Standard Module

Public Sub GoToNextCell()
If ActiveCell.IS <> "" Then
Range(ActiveCell.ID).Select
End If
End Sub

Worksheet Module

Private Sub Worksheet_Activate()
With Me

.Range("E4").ID = "Y4"
.Range("Y4").ID = "E6"
.Range("E6").ID = "Y6"
.Range("Y6").ID = "J12"
.Range("J12").ID = "J14"
.Range("J14").ID = "M12"
.Range("M12").ID = "M13"
.Range("M13").ID = "M14"
.Range("M14").ID = "M17"
.Range("M17").ID = "P12"
.Range("P12").ID = "P13"
.Range("P13").ID = "P14"
.Range("P14").ID = "P15"
.Range("P15").ID = "P17"
.Range("P17").ID = "S12"
.Range("S12").ID = "S14"
.Range("S14").ID = "S17"
.Range("S17").ID = "V12"
.Range("V12").ID = "V14"
.Range("V14").ID = "V17"
.Range("V17").ID = "AB12"
.Range("AB12").ID = "AE12"
.Range("AE12").ID = "AH12"
.Range("AH12").ID = "AH17"
.Range("AH17").ID = "AK14"
.Range("AK14").ID = "AK17"
.Range("AK17").ID = "C30"
.Range("C30").ID = "C31"
.Range("C31").ID = "C33"
.Range("C33").ID = "D35"
.Range("D35").ID = "AA33"
.Range("AA33").ID = "Y36"
.Range("Y36").ID = "Y39"
.Range("Y39").ID = "E4"
End With
Application.OnKey "{TAB}", "GoToNextCell"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}", ""
End Sub

 

[xlbo]

Coupla things

1: If ActiveCell.IS <> "" Then should be:
If ActiveCell.ID <> "" Then

2: This is set up when the sheet is activated so after pasting the code in, select cell E4 on the sheet this is being applied to. Then select another sheet. Then come back to this sheet again - then press TAB

This should only need to be done the 1st time it is tried after inputting the code as at that point the sheet_ACTIVATE event has not fired so you need to make it fire to initialise the whole thing..



Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[combo]

Thanks Geoff for the explanations, always coming faster then mine.

I would also suggest to check the security settings. You should get macro warning dialog and enable macros while opening the workbook. Otherwise go Tools>Macro>Security, change protection level to Medium; high level setting disables macros without any warning.

The ugly thing is that you need to deselect and select again the sheet to initialise TAB key procedure. This can be overcome, but requires more coding.

combo

 

[DajOne]

Geoff, Combo,

Thanks for the help.. I think I am making progress..

Changed the IS to ID in the general module
Security is on the lowest level to accept all macros

After entering the code, I save, and reopen the sheet as an user would do..

Select E4, then select another sheet, then come back to the sheet and press tab. Tried different things (from help files) and I come back with this

Run Time Error '1004'
Application defined error or object define error. Debug shows this line in yellow .Range ("E4").ID="Y4"

Combo, when you say that
The ugly thing is that you need to deselect and select again the sheet to initialise TAB key procedure

Does this means that the user would have to do this setp every time he opens the spreadsheet or it is a one time only after it is re-saved?

Thanks guys...

 

[xlbo]

What version of Excel ??

I have a feeling that the ID property is fairly new...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DajOne]

Oh crap...

Excel 2000 9.0.6926 SP-3

 

[xlbo]

Easy way to check

In the VBE, press F2 to bring up the object model

Go to "Range" in the classes window and then go to "I" in the "Members of Range" window

If ID is there, your code is safe. If not, the ID property wasn't in your version of excel I'm afraid - don't worry too much though - there are workarounds - my suggestion wouldn't use the ID property in any case - it just isn't quite as slick as combo's

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DajOne]

I checked and ID is in there..

 

[xlbo]

Very odd - I just pasted the last code you posted into a new workbook and it worked perfectly...
You have pust the Activate & Deactivate event subs in the workSHEET module havn't you ??

Can you post your latest code please ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question