Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
Hi,
Maybe someone can help me. i need to create a marco that preforms the following: at the beginning all worksheet is locked, but the cells on column A. 1) if data is entered into cell a7 then cell b7 unlocks and if data is entered to cell b7 cell c7 unlock and so forth for row 1. the same must apply to all the other rows on the worksheet. the only cells that are not locked are those on column A from row 7, and the rows 1-7 which are locked all the time. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
Try this idea. Right click sheet tabview codecopy/paste this
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Locked = True Then Exit Sub ActiveSheet.Unprotect Target.Offset(, 1).Locked = False ActiveSheet.Protect End Sub -- Don Guillett SalesAid Software "Guye" wrote in message ups.com... Hi, Maybe someone can help me. i need to create a marco that preforms the following: at the beginning all worksheet is locked, but the cells on column A. 1) if data is entered into cell a7 then cell b7 unlocks and if data is entered to cell b7 cell c7 unlock and so forth for row 1. the same must apply to all the other rows on the worksheet. the only cells that are not locked are those on column A from row 7, and the rows 1-7 which are locked all the time. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
one more thing i completely forgot about,
is it possible to creat the reversible, if i delet the info from the cell the next cells will be locked thank guy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
hi
How can i activate the previous Sub automaticaly when the file opens. thanks guy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
Here is something simple that will work.
The code goes in the worksheet module, not a general module. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 7 Then Exit Sub If Target.Value < "" Then Me.Unprotect Target.Offset(0, 1).Locked = False Me.Protect End If End Sub Mike F "Guye" wrote in message ups.com... Hi, Maybe someone can help me. i need to create a marco that preforms the following: at the beginning all worksheet is locked, but the cells on column A. 1) if data is entered into cell a7 then cell b7 unlocks and if data is entered to cell b7 cell c7 unlock and so forth for row 1. the same must apply to all the other rows on the worksheet. the only cells that are not locked are those on column A from row 7, and the rows 1-7 which are locked all the time. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
Here is the reverse procedure, but it will only work if you clear one cell
at a time from column A only. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 7 Then Exit Sub If Target.Value < "" Then Me.Unprotect Target.Offset(0, 1).Locked = False Me.Protect End If If Target.Column < 2 And Target.Value = "" Then Application.EnableEvents = False Me.Unprotect With Target.EntireRow .Clear .Locked = True End With Target.Locked = False Me.Protect Application.EnableEvents = True End If End Sub Mike F "Guye" wrote in message oups.com... one more thing i completely forgot about, is it possible to creat the reversible, if i delet the info from the cell the next cells will be locked thank guy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
I am not sure what you mean. This is Worksheet Event code, which means when
any cell content is changed, the code will run. Mike F "Guye" wrote in message oups.com... hi How can i activate the previous Sub automaticaly when the file opens. thanks guy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
thank Mike,
you have been of great help to me |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
hi
no, its not an event code. the file is just made to store information. by the way, the Sub you gave me locks the cells but it also formats all the rows characteristic such as color etc, etc, and not only erase the information on the row |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
The code that Don and I gave you is event code, if pasted where Don said to.
Apparently you did or it would not work right. To fix the cell formats from being changed when you clear a cell in column A, change .Clear to ..ClearContents. Mike F "Guye" wrote in message oups.com... hi no, its not an event code. the file is just made to store information. by the way, the Sub you gave me locks the cells but it also formats all the rows characteristic such as color etc, etc, and not only erase the information on the row |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help
thanks for the code
it works now perfectly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |