insert a row with enter on a specific cell

[Guest]

I am using excell 2003
I am trying to insert a row with a border on a protected worksheet, so that
I can tab or enter through the unlocked cells but when I get to a specific
cell it will add a row to continue with the smae line above.
EX.
A1:A3 , C1:C3 and E1:E3 all have borders around each cell and are unlocked.
I can tab from A1,A2,A3,C1 etc. or Enter A1,C1,E1,A2 etc...I need to insert
a new row if I hit enter at A3,C3 or E3, so i can contiue on with the
bordered chart ive made.
So the border will now be A1:B3 and everything else has shifted down to
D13 and so on.
I hope this makes sence to someone. thanks in advanced

 

[britwiz]

I am using excell 2003
I am trying to insert a row with a border on a protected worksheet, so that
I can tab or enter through the unlocked cells but when I get to a specific
cell it will add a row to continue with the smae line above.
EX.
A1:A3 , C1:C3 and E1:E3 all have borders around each cell and are unlocked.
I can tab from A1,A2,A3,C1 etc. or Enter A1,C1,E1,A2 etc...I need to insert
a new row if I hit enter at A3,C3 or E3, so i can contiue on with the
bordered chart ive made.
So the border will now be A1:B3 and everything else has shifted down to
D13 and so on.
I hope this makes sence to someone. thanks in advanced

Hi Oakie

I'm not entirely sure of your description as, if you insert a row,
everything does indeed move down so I don't see how A1:A3 becomes A1:B3
or C1:C3 becomes D13.

However, based on your description rather than your ranges, try this in
the sheet's code (right-click the sheet's tab > View Code and paste):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
If Target.Row >= 3 And _
Target.Borders(xlEdgeBottom).LineStyle = xlContinuous Then
Application.EnableEvents = False
ActiveSheet.Unprotect
With Target
myValue = .Value
.Value = ""
.EntireRow.Insert
.Offset(-1, 0) = myValue
End With
ActiveSheet.Protect
Application.EnableEvents = True
End If
End Sub

The above code will increase all three "charts" by one row.

If you only want to amend the current "chart" try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
If Target.Row >= 3 And _
Target.Borders(xlEdgeBottom).LineStyle = xlContinuous Then
Application.EnableEvents = False
ActiveSheet.Unprotect
Target.Borders(xlEdgeBottom).LineStyle = xlNone
With Target.Offset(1, 0)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
ActiveSheet.Protect
Application.EnableEvents = True
End If
End Sub

Regards

Steve

 

[britwiz]

Hi Oakie

My mistake.

If you choose the second option, you'll need this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
If Target.Row >= 3 And _
Target.Borders(xlEdgeBottom).LineStyle = xlContinuous Then
Application.EnableEvents = False
ActiveSheet.Unprotect "mypassword"
Target.Borders(xlEdgeBottom).LineStyle = xlNone
With Target.Offset(1, 0)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Locked = False 'unlock the new cell
End With
ActiveSheet.Protect "mypassword"
Application.EnableEvents = True
End If
End Sub

This will unlock the new cell for use. Also note where you can input a
password to make the sheet a little more secure.

Regards

Steve

 

[Guest]

Thanks for the help Steve, You are sort of on the right track.
the first code just inserted a new row, without coping the chart down after.
Its also inserted unlocked cells as it inserts row. its closer to what im
looking for, I think


A B C D E
1 |------|--------|---------------------------|
this is how my chart looks right now (imagine the middle horizontal line as
top and bottom borders. A1 B1 and C1 cells are unlocked. then I have 4 more
of the same charts below with different headings. say at A4 the next chart
starts.
So, if I enter something into A1 and hit enter it will bring me down to my
next unlocked cell a A4, or if I enter something into A1 and hit tab it will
go to B1.
The only time I want to insert a row and copy the chart down is in C1. And
the same cells unlocked as the ones above, so If i need to change any
information I can always go back.

A B C D E
1 |-------|-----------|---------------------------------|
2 |-------|-----------|---------------------------------|

or, with information typed in

| Status | Trade | Description |
|---------|---------| Descrption |

SO, I type info in status, hit tab, type something in Trade, hit tab, type
something in description and hit tab it would insrt new line with chart and I
could start typing where Status is and so on. Or i hit enter after typing in
description and it would inset new line and contiue to type a new
decription. So it could look something like this

| Something | Something | Something |
| | | Something
|
| | Something | Something |
| | | Something
|
|--------------|--------------|-----------------------------------|

At the end it would have to be an empty line so I can enter to the next set
of charts.

Hopefully this helps out a little more.
Thanks again.

 

[britwiz]

Thanks for the help Steve, You are sort of on the right track.
the first code just inserted a new row, without coping the chart down after.
Its also inserted unlocked cells as it inserts row. its closer to what im
looking for, I think

Hi Oakie

Sorry for the late response (it's been a busy day) but try this:

Paste these two procedures in a module:

***

Sub EnterKeyProcess()
With ActiveCell
If .Column = 3 And _
.Borders(xlEdgeBottom).LineStyle = xlContinuous And _
.Value <> "" Then
Application.EnableEvents = False
ActiveSheet.Unprotect "mypassword"
Range(.Offset(1, -2), .Offset(1, 0)).EntireRow.Insert
With Range(.Offset(1, -2), .Offset(1, 0))
.Borders.LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlNone
.Locked = False
End With
.Offset(1, 0).Select
ActiveSheet.Protect "mypassword"
Application.EnableEvents = True
Else
If .Column = 3 Then
.Offset(1, -2).Select
Else
.Offset(1, 0).Select
End If
End If
End With
End Sub

Sub TabKeyProcess()
Dim c As Range
With ActiveCell
If .Column = 3 Then
If .Offset(1, -2).Locked = True Then
ActiveSheet.Unprotect "mypassword"
For Each c In .CurrentRegion
If c.Locked = False Then
c.Select
ActiveSheet.Protect "mypassword"
Exit For
End If
Next
Else
.Offset(1, -2).Select
End If
Else
.Offset(0, 1).Select
End If
End With
End Sub

***

Paste these four procedures in "ThisWorkbook":

***

Private Sub Workbook_Activate()
Application.OnKey "{ENTER}", "EnterKeyProcess"
Application.OnKey "{TAB}", "TabKeyProcess"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{ENTER}"
Application.OnKey "{TAB}"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{ENTER}"
Application.OnKey "{TAB}"
End Sub

Private Sub Workbook_Open()
Application.OnKey "{ENTER}", "EnterKeyProcess"
Application.OnKey "{TAB}", "TabKeyProcess"
End Sub

***

It's not perfect (Tab only moves within one range) but I think it's
very close to what you're after - if it's not I apologise for
misinterpreting your brief.

Have a nice weekend

Regards

Steve