multiple force mandatory cell entry before continue

[handri]

hi,

I created a worksheet. Before others can use this worksheet for data entry. They must fill specific cell as below at first place. Than continue entering data. If not, they are not able to continue their work.

1. B3
2. D3
3. F3.

How do i can do this.

thank you

 

[louisH]

Hi,

You can make the sheet protected via some code.

Right click on the sheet tab and click view code.
In the VBA Editor that appears, paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3, D3, F3")) Is Nothing Or Target.Count > 1 Then Exit Sub
    
    If Range("B3").Value = "TheValueOfB3" And Range("D3").Value = "TheValueOfD3" And Range("F3").Value = "TheValueOfF3" Then
        Target.Worksheet.Unprotect Password:="YourPassWord"
    Else
        Target.Worksheet.Protect Password:="YourPassWord"
    End If
    
End Sub

Replace "TheValueOf" and "YourPassWord" with the value you want in the cell to unprotect.

You might also want to Unlock these 3 cells before pasting this macro.
Right click on the cell, go to cell format, and in the protectin tab, uncheck "Locked".

If it didn't help please tell me what are the conditions for the sheet to be editable.

 

[handri]

thanks louish, i dont need to protected this sheet with code. For the cell B3, D3 and F3. They must fill some number regarding jobs required. But the 1st thing is before they start to entering data this 3 cell B3, D3 and F3 must fill 1st.

thank you

Hi,

You can make the sheet protected via some code.

Right click on the sheet tab and click view code.
In the VBA Editor that appears, paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3, D3, F3")) Is Nothing Or Target.Count > 1 Then Exit Sub
    
    If Range("B3").Value = "TheValueOfB3" And Range("D3").Value = "TheValueOfD3" And Range("F3").Value = "TheValueOfF3" Then
        Target.Worksheet.Unprotect Password:="YourPassWord"
    Else
        Target.Worksheet.Protect Password:="YourPassWord"
    End If
    
End Sub

Replace "TheValueOf" and "YourPassWord" with the value you want in the cell to unprotect.

You might also want to Unlock these 3 cells before pasting this macro.
Right click on the cell, go to cell format, and in the protectin tab, uncheck "Locked".

If it didn't help please tell me what are the conditions for the sheet to be editable.

 

[louisH]

thanks louish, i dont need to protected this sheet with code. For the cell B3, D3 and F3. They must fill some number regarding jobs required. But the 1st thing is before they start to entering data this 3 cell B3, D3 and F3 must fill 1st.

thank you

Yes i understood that. But if you want to disable editing before these 3 cells are filled, you'll have to protect/unprotect the sheet.
So if the value they entered in the cell isn't important, use this :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3, D3, F3")) Is Nothing Or Target.Count > 1 Then Exit Sub
    
    If Range("B3").Value <> "" And Range("D3").Value <> "" And Range("F3") <> "" Then
        Target.Worksheet.Unprotect Password:="YourPassWord"
    Else
        Target.Worksheet.Protect Password:="YourPassWord"
    End If
    
End Sub

 

[handri]

Yes i understood that. But if you want to disable editing before these 3 cells are filled, you'll have to protect/unprotect the sheet.
So if the value they entered in the cell isn't important, use this :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3, D3, F3")) Is Nothing Or Target.Count > 1 Then Exit Sub
    
    If Range("B3").Value <> "" And Range("D3").Value <> "" And Range("F3") <> "" Then
        Target.Worksheet.Unprotect Password:="YourPassWord"
    Else
        Target.Worksheet.Protect Password:="YourPassWord"
    End If
    
End Sub

hi louish

In my vba. There is sheet1 and ThisWorkbook. Which one and how do i save as type file.

thanks

 

[louisH]

hi louish

In my vba. There is sheet1 and ThisWorkbook. Which one and how do i save as type file.

thanks

Paste it in your sheet module (Sheet1). If you want to save the file you have to save it as .xlsm (macro-enabled).