Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 D1:D3 and so on. I hope this makes sence to someone. thanks in advanced |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Oakie wrote: 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 D1:D3 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 D1:D3. 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. " wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Oakie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do I insert the address of a selected cell into a fixed cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How do I format a cell so that only specific numbers can be enter. | Excel Discussion (Misc queries) | |||
How do I insert an image into a specific cell within a protected . | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |