Dropdown sometimes other times auto value

[Mrstinkleton]

Hi all I'm hoping someone can help. Look all over for a solution but no look. Now I'm pretty proficient with Excel and not bad at VBA (still refreshing my skills) bit I'm stuck! Let me try to explain. I want a cell to autopopulate with M or F for gender if the group looks like this 10CS-B or 10CS-G but if the group looks like this 10CS-M I want dropdown to become enabled so I can select M or F depending on the gender of the student as 10CS-M for example would be a mixed gender group. Thanks all.

 

[Fluff]

This is another option, that will ask the user to input a value for the mixed classes

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        Select Case Right(Target.Value, 1)
            Case "B"
                Target.Offset(, 2) = "M"
            Case "G"
                Target.Offset(, 2) = "F"
            Case "M"
                Target.Offset(, 2) = InputBox("Please enter M or F for " & vbLf & "group " & Target.Value)
        End Select
    End If
    
End Sub

 

[My Aswer Is This]

I thought of this myself but thougth it would be easier to just do it manually. But we will see.

This is another option, that will ask the user to input a value for the mixed classes

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        Select Case Right(Target.Value, 1)
            Case "B"
                Target.Offset(, 2) = "M"
            Case "G"
                Target.Offset(, 2) = "F"
            Case "M"
                Target.Offset(, 2) = InputBox("Please enter M or F for " & vbLf & "group " & Target.Value)
        End Select
    End If
    
End Sub

 

[Fluff]

I thought of this myself but thougth it would be easier to just do it manually. But we will see.

Tend to agree, but as the OP initially asked for a dropdown to be enabled, I thought I'd give a similar solution.

 

[My Aswer Is This]

Here is another option:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "10CS-B" Then Target.Offset(, 2).Value = "M"
If Target.Value = "10CS-G" Then Target.Offset(, 2).Value = "F"
If Target.Value = "10CS-M" Then
ans = MsgBox("Choose Yes for M" & vbNewLine & "Choose No for F", vbYesNo + vbQuestion, "My Answer")
If ans = vbYes Then Target.Offset(, 2).Value = "M"
If ans = vbNo Then Target.Offset.Offset(, 2).Value = "F"
End If
End If
End Sub

 

[Mrstinkleton]

Thank you all. I Wil try these solutions. In the meantime as time was against me, I used conditional formatting to colour the cells if single gender and disabled menu. This works quite well and avoids VBA, as on the analysis Shef I can use an if function to say M or F if needed or the gender as input for a mixed class.