[for] urgent urgent!!!!!! Turn to god for help, has been studying for a long time, but still can&#03-CodePudding

Turn the VBA code:
Toward triggers, such as B5 column options for tellers, D5: F5 editable, the rest of the cell cannot edit and fill in the grey, (A5, B5, C5 is not controlled by the code, always editable)

Such as B6 column is the savings counter, G6: J6 editable, the rest of the cell cannot edit and fill in the grey, (ditto A6, B6, C6 is not controlled by the code, always editable)

Such as B7 comprehensive teller, D7 and G7: H7 editable, the rest of the cell cannot edit and fill in the grey, (A7, B7, C7 is not controlled by the code, always editable)

The and so on, such as B8 is the lobby manager,,,,,,,, jobs will set up five, when the trigger to the designated position, the bank's automatic identification unit editable and an editing scope, do not edit lock and fill gray),

CodePudding user response:

Line by line detection,

Such as detection to B5 for "brought to counter:"

 Range (" A5: F5). Select 
Selection. Locked=False 
Selection. FormulaHidden=False 
Range (" G5: Z5 "). Select 
With the Selection. The Interior 
. ColorIndex=15 
. The Pattern=xlSolid 
. PatternColorIndex=xlAutomatic 
End With

Note that the default protection properties of the cell is locked, so will the attribute is set to not accessible cell lock,
The rest of the cell, is set to grey,

After all the lines have been processed, Protect the worksheet

 ActiveSheet. Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

This code as long as you use the record macro in the tools menu, you can get,

CodePudding user response:

Put the following code, paste into the code window of "worksheet",
Like you is this "table" Sheet2, so you want to be in VBE varies "engineering" browser window, double-click the "Sheet2", and then paste the code:

 Private Sub Worksheet_Change (ByVal Target As Range) 
Const SHADEVALUE As Double=0.1 
Dim sText As String 
Dim sRange As String 
Dim lRow As Long 

If (2 & lt;> Target. The Column) Then the Exit Sub 
If (1 & lt; Target. Rows. Count) Then the Exit Sub 
SText=Target. The Value 
LRow=Target. Row 
Me. Unprotect 
With Me. The Range (" D "& amp; LRow & amp; ":" J & amp; LRow) 
The Locked=False 
. The Interior. The Pattern=xlNone 
End With 
If (0 & lt; InStr (1, "brought to the teller integrated teller savings clerk lobby manager", sText)) Then 
The Select Case InStr (1, "brought to the teller integrated teller savings clerk lobby manager", sText) \ 4 
Case 0: 'let public teller D, the F editable 
With Me. The Range (" E "& amp; LRow) 
The Locked=True 
. The Interior. The Pattern=xlSolid 
. Interior. PatternColorIndex=xlAutomatic 
. Interior. ThemeColor=xlThemeColorDark1 
. Interior. TintAndShade=SHADEVALUE 
. Interior. PatternTintAndShade=0 
End With 
With Me. The Range (" G "& amp; LRow & amp; ":" J & amp; LRow) 
The Locked=True 
. The Interior. The Pattern=xlSolid 
. Interior. PatternColorIndex=xlAutomatic 
. Interior. ThemeColor=xlThemeColorDark1 
. Interior. TintAndShade=SHADEVALUE 
. Interior. PatternTintAndShade=0 
End With 
Case 1: 'integrated teller D, G, H editable 
With Me. The Range (" E "& amp; LRow & amp; ":" F & amp; LRow) 
The Locked=True 
. The Interior. The Pattern=xlSolid 
. Interior. PatternColorIndex=xlAutomatic 
. Interior. ThemeColor=xlThemeColorDark1 
. Interior. TintAndShade=SHADEVALUE 
. Interior. PatternTintAndShade=0 
End With 
With Me. The Range (" I "& amp; LRow & amp; ":" J & amp; LRow) 
The Locked=True 
. The Interior. The Pattern=xlSolid 
. Interior. PatternColorIndex=xlAutomatic 
. Interior. ThemeColor=xlThemeColorDark1 
. Interior. TintAndShade=SHADEVALUE 
. Interior. PatternTintAndShade=0 
End With 
Case 2: 'savings counter G, J editable 
With Me. The Range (" D "& amp; LRow & amp; ":" I & amp; LRow) 
The Locked=True 
. The Interior. The Pattern=xlSolid 
. Interior. PatternColorIndex=xlAutomatic 
. Interior. ThemeColor=xlThemeColorDark1 
. Interior. TintAndShade=SHADEVALUE 
. Interior. PatternTintAndShade=0 
End With 
With Me. The Range (" G "& amp; LRow) 
The Locked=False 
. The Interior. The Pattern=xlNone 
End With 
Case 3: 'lobby manager 
The Debug. Print "lobby manager 
"
End the Select 
End the If 
With Me. Protection. AllowEditRanges 
. If (0=. Count) Then Add the Title:="regional P001", Range:=the Columns (" A, C) 
End With 
Me. Protect DrawingObjects:=False 
End Sub

CodePudding user response:

Judging is kept,