Moving thru locked cells

[iamtheeggman]

I have a protected sheet which the user inputs info in cells in columns B, D and F. In row 56 is a merged cell across A thru G. Using enter to move from cell to cell I am only able to move thru the unlocked cells in column B and into the merged cell and back to the first unlocked cell in B again. It should move thru the cells in B first then D then F. I guess the merged cell is causing the problem but is there a way to go thru the cells in B then the cells in D then the cells in F and finish in the merged cell at the end. The user puts daily sales numbers in the cells and uses the big merged cell to record notes about anything which may have happened during the day. ANY THOUGHTS??

 

[Tazguy37]

You could change the Move Selection after Enter Direction to Right. But you wanted to go through all of Column B, then all of Columns D and F, then the merged cell, right?

It could be done with some VBA code, I bet, if that's not what you're after.

Post back and let us know. Hope that helps!

 

[iamtheeggman]

The input order is much easier going down thru the column first. Is there a way to put the text for the notes into one cell. If I leave the cells to the right empty the text will flow thru but if they need more space they would run out. The merged cell is tall enough for five lines of text and is formatted to wrap the text. I am open to some VBA code but I could not figure out how to get the active cell to follow the order I wanted. I think in older versions of Excel you could hold down the CTRL key as you selected cells in any order and as you hit enter it would follow the order you set. In Excel 2003 you can highlight cells in a specific order using the CTRL key but once you hit enter you are back to one cell highlighted.

 

[Derek]

Hi there

Insert 2 new blank columns A & B then:
List the cell addresses you wish to visit - in visit order - in Column A, starting at A2 and moving down without any gaps. (eg B1 D4 G3)
Hide columns A and B if you wish (or make font color same as background)

Right click your sheet tab, left click View Code and paste this into the white area:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Address(rowabsolute, columnabsolute) = Range("A2").Value Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns("A:A").Copy Range("B1")
Range("B2:B500").Copy Range("B1")
Application.EnableEvents = True
Exit Sub
Else
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("B2").Value = "" Then
Columns("A:A").Copy Range("B1")
Range("B2:B500").Copy Range("B1")
Else
Range("B2:B500").Copy Range("B1")
End If
Range(Range("B1").Value).Select
Application.EnableEvents = True
End If
End Sub


Whichever cell you select it will reselect the next cell in your list and loop around indefinately. The cursor position can be reset to the beginning at any time by selecting the first cell in your list.
regards
Derek

 

[erik.van.geit]

Hi, iamtheeggman
Welcome to the Board !

to my sense the most simple way to solve this is
1. unmerge the cells and
2. in the properties choose horizontal alligment "center accroos selection"

kind regards,
Erik

 

[erik.van.geit]

Derek,

if you really want to use code for this, you don't need extra columns

example
http://www.mrexcel.com/board2/viewtopic.php?t=134081&postdays=0&postorder=asc&start=13


best regards,
Erik

 

[iamtheeggman]

Thanks for the help. I will try these in the morning

 

[iamtheeggman]

Thanks for the help. I will try these in the morning

The help I,ve gotten so far is good but not solving the problem as a whole. I figured out that when you hold the CTRL key and click cells you can then scroll thru those cells in the order you clicked on them when the sheet is not locked but once you protect the sheet the CTRL key trick does not work anymore Any guess as to why? Also if I unmerge the big cell I am able to move thru all the cells okay but I don't have a big area for notes. I used a text box originally but this workbook consists of 31 sheet for daily entry plus a monthly recap sheet at the end. Each month the user starts a new month by running a macro that saves the current workbook (Oct 05 sales) as a new one (Nov 05 sales) and clears all the unlocked cells for the 31 days so the user can start a new month. I would like to be able to clear the contents of the Notes cell also. That is why I went to a big merged cell Vs a text box. I couldn't get the marco to clear the text box for each day. perhaps there is a way around that problem instead. Any thoughts.

 

[erik.van.geit]

this is going to be rather "heavy"
I hope you will take the main things and leave the unnecessary ones, but I felt it necessary to provide comments on a lot of things.

the CTRL key trick does not work anymore

when "Ctrl-selecting columnareas" the cells I can go down the column then next column ... on a protected sheet
hold CONTROLbutton
SELECT B2:B55
SELECT D2:D55
SELECT F2:F55

using code this would be

Range("B2:B56,D2:D56,F2:F56").Select
Selection(1).Activate

problem is when you make a mistake and have to "go up"

That is why I went to a big merged cell Vs a text box. I couldn't get the marco to clear the text box for each day.

because there are 31 sheets ???
you can clear those boxes using simple code

The help I,ve gotten so far is good but not solving the problem as a whole.

Sounds a bit dangerous statement... Let me reply whith another dangerous one: This could also depend on what you've done with the help sofar."
truth will certainly be somewhere between the statements

but I don't have a big area for notes.

what did you do with the trick you recieved to use "center text accross selection"?
what did you do with the link you received if you anyway you want to use code ?
http://www.mrexcel.com/board2/viewtopic.php?t=134081&postdays=0&postorder=asc&start=13

looking back to this "heavy" post
the main thing is
if you really want to use textboxes YOU CAN

Sub test()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
    sh.OLEObjects("TextBox1").Object = ""
    Next sh
End Sub

a large post with some possibilities ?
best regards,
Erik

 

[iamtheeggman]

What works

Sorry for the "heavy/dangerous" post.
After trying several of thew sugestions I wasn't getting what I was after probably because I didn't understand everything. I tried "center across selection on only one cell. Once I figured out I could use it on multiple cells and lock all but the first one it solved all of my problems. Thanks, I am humbled!javascript:emoticon('')
Redface

Re: CRTL selection I had similar code which read
Range("C4,C6:C9,C11:C14,C17:C23,C25,H5:H19,H21:H39,C29:C33,C39").Select
Range("C39").Activate

It still deactivates the cells as soon as I hit enter even when I changed Range to Selection - Any thoughts on what I am doing wrong.

Re: Sub test()

I tried running this in my exsisting workbook and came up with
Runtime error '1004'
Method 'OLEObjects' of object 'worksheet' failed.

What did I miss?

Thanks for the help so far. You guys rock!