Excel 2000 Macro to prevent moving beyond a cell without making a choice from Dropdown

[billfinn]

Good morning!
I am trying to make it mandatory for one specific cell to have a value before the user can move on. I came up with the following macro that works fine if the user happens to scroll to or click the designated cell. I would like to be able to prevent the user from scrolling past that row or making choices beyond that row unless the cell has a value. The cell in question uses Data Validation with choices of 8-Inch,10-Inch,12-Inch,14-Inch,16-Inch and a blank cell which I have saved as the default. I have a sample file but it's simply a list cells with data validation




Thanks very much for any assistance that anyone can offer.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$A$49")) Is Nothing Then Exit Sub
    Select Case Target.Address
    Case Is = "$A$49"
    If Range("$A$49").Value = "" Then
    MsgBox "You must make a selection in $A$49 before you can continue"
    Range("$A$49").Select
    End If
    End Select
End Sub

Thanks,
Bill

 

[DanteAmor]

Try the following. The variable "cell" must go in the "Declarations" section (at the beginning of the code)

Dim celda
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A49")) Is Nothing Then
        celda = "A49"
    Else
        If celda = "A49" Then
            If Range("A49").Value = "" Then
                MsgBox "You must make a selection in $A$49 before you can continue"
                Range("A49").Select
            End If
        End If
    End If
End Sub

 

[billfinn]

RESOLVED
Dante,
Thanks very much for your response!
I just opened my browser to close this one out as I came up with a solution, realizing that all I needed to do was have two conditions that had to be met in order to prevent the user from moving on without choosing from the drop-down menu.
I ended up using the following code. I also tried it with your code and it worked just fine. I appreciate your input!

Dim PreviousValues As Variant



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Store current values of all cells
    Application.EnableEvents = True
    PreviousValues = Me.UsedRange.Value


  If Range("B140").Value = "DBL 2x4 Cellulose Wall" And Range("A185").value = "" Then
  MsgBox "You must make a selection in $A$185 before you can continue"
  End If


End Sub

Thanks,
Bill

 

[DanteAmor]

Im glad to help you