VBA: force the user to fill certain cell

[lunatu]

Hi,

is it possible to force the user to fill a certain cell before other cell can be filled? For example I want the user to fill cell A before cell B can be filled? Or maybe to display a comment when trying to fill cell B but the cell A is still empty.

Thanks in advance
Br Luna

 

[bebo021999]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B1")
If Cells(Target.Row, "A") <> "" Then Exit Sub ' if B1 is target, A1 is condition
If Not Intersect(Target, rng) Is Nothing Then
   MsgBox "Try to fill cell A"
   Range("A1").Select
End If
End Sub

 

[lunatu]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B1")
If Cells(Target.Row, "A") <> "" Then Exit Sub ' if B1 is target, A1 is condition
If Not Intersect(Target, rng) Is Nothing Then
   MsgBox "Try to fill cell A"
   Range("A1").Select
End If
End Sub

Thanks this is working perfectly! But now Im running into another proble as I have never combined multiple private sub worksheet change's. Maybe you could help me with this as well? The other code Im using is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        If UCase(Target) = UCase("Negotiate") Then
            Target.Offset(, 2) = Int(Now())
        End If
  
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        If UCase(Target) = UCase("Close (won)") Then
            Target.Offset(, 3) = Int(Now())
        End If
        
     If Not Intersect(Target, [Q:Q]) Is Nothing Then
        If UCase(Target) = UCase("Close (part-won)") Then
            Target.Offset(, 3) = Int(Now())
        End If
        
          If Not Intersect(Target, [Q:Q]) Is Nothing Then
        If UCase(Target) = UCase("Close (lost)") Then
            Target.Offset(, 4) = Int(Now())
        End If

    End If
    End If
    End If
    End If
End Sub

 

[Joe4]

Note that your original code can be simplified. When you are checking the same range for different values, it is more efficient to use CASE statements than multiple and/or nested IF statements. I have done that in the code below.

I also made it so it will apply to all of column B (not just row 1), and will remove the attempted entry in column B if column A is blank.

Private Sub Worksheet_Change(ByVal Target As Range)
   
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   ***ORIGINAL CODE (SIMPLIFIED)***
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        Select Case UCase(Target)
            Case "NEGOTIATE"
                Target.Offset(, 2) = Int(Now())
            Case "CLOSE (WON)", "CLOSE (PART-WON)"
                Target.Offset(, 3) = Int(Now())
            Case "CLOSE (LOST)"
                Target.Offset(, 4) = Int(Now())
        End Select
    End If
   
'   ***NEW CODE (applies to all of column B)***
    If Not Intersect(Target, [B:B]) Is Nothing Then
'       Check to see if column A is populated
        If Target.Offset(0, -1) = "" Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            MsgBox "You must populate column A before column B!", vbOKOnly, "ENTRY ERROR!"
        End If
    End If

End Sub

 

[lunatu]

Note that your original code can be simplified. When you are checking the same range for different values, it is more efficient to use CASE statements than multiple and/or nested IF statements. I have done that in the code below.

I also made it so it will apply to all of column B (not just row 1), and will remove the attempted entry in column B if column A is blank.

Private Sub Worksheet_Change(ByVal Target As Range)
  
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   ***ORIGINAL CODE (SIMPLIFIED)***
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        Select Case UCase(Target)
            Case "NEGOTIATE"
                Target.Offset(, 2) = Int(Now())
            Case "CLOSE (WON)", "CLOSE (PART-WON)"
                Target.Offset(, 3) = Int(Now())
            Case "CLOSE (LOST)"
                Target.Offset(, 4) = Int(Now())
        End Select
    End If
  
'   ***NEW CODE (applies to all of column B)***
    If Not Intersect(Target, [B:B]) Is Nothing Then
'       Check to see if column A is populated
        If Target.Offset(0, -1) = "" Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            MsgBox "You must populate column A before column B!", vbOKOnly, "ENTRY ERROR!"
        End If
    End If

End Sub

Thanks a lot, this was very good tip for a beginner like me ?

 

[Joe4]

You are welcome.

Here is a good little write-up on the CASE function: MS Excel: How to use the CASE Statement (VBA)

 

[lunatu]

Note that your original code can be simplified. When you are checking the same range for different values, it is more efficient to use CASE statements than multiple and/or nested IF statements. I have done that in the code below.

I also made it so it will apply to all of column B (not just row 1), and will remove the attempted entry in column B if column A is blank.

Private Sub Worksheet_Change(ByVal Target As Range)
  
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   ***ORIGINAL CODE (SIMPLIFIED)***
    If Not Intersect(Target, [Q:Q]) Is Nothing Then
        Select Case UCase(Target)
            Case "NEGOTIATE"
                Target.Offset(, 2) = Int(Now())
            Case "CLOSE (WON)", "CLOSE (PART-WON)"
                Target.Offset(, 3) = Int(Now())
            Case "CLOSE (LOST)"
                Target.Offset(, 4) = Int(Now())
        End Select
    End If
  
'   ***NEW CODE (applies to all of column B)***
    If Not Intersect(Target, [B:B]) Is Nothing Then
'       Check to see if column A is populated
        If Target.Offset(0, -1) = "" Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            MsgBox "You must populate column A before column B!", vbOKOnly, "ENTRY ERROR!"
        End If
    End If

End Sub

Hi,
still one question regarding my original code. If I also want the value on cell R to be > 0 before the date is displayed. There should not be matter in which order you fill the cells, so there can be first specific text in cell Q and after that the value in cell R increases or the other way (first cell R increase and after that you change the text in cell Q).

 

[Joe4]

Hi,
still one question regarding my original code. If I also want the value on cell R to be > 0 before the date is displayed. There should not be matter in which order you fill the cells, so there can be first specific text in cell Q and after that the value in cell R increases or the other way (first cell R increase and after that you change the text in cell Q).

That is a whole new question (different than your original) and as such, should be posted to a new thread.
Also, by doing that, it will appear in the "Unanswered threads" listing that many people use to look for new unanswered questions to respond to.

 

[lunatu]

That is a whole new question (different than your original) and as such, should be posted to a new thread.
Also, by doing that, it will appear in the "Unanswered threads" listing that many people use to look for new unanswered questions to respond to.

Great I'll do that