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.

 

[My Aswer Is This]

One of my questions:
What is: "if the group looks like"

Tell me what a "group" is

I know of no way to have a script stop and allow you to choose a value from a drop down list and then continue.

I think you should tell us how your sheet is laid out.
What column has what type data.

The script should be able for example to look down column "C" and If it see's 10CS-B or 10CS-G
Do this or if it see's this do this.

But you need to tell us where to look for what and then if found do what.

Sounds like to me you want to look for :

10CS-B or 10CS-G

Can I assume if it's 10CS-B some cell would have "M" entered or if it was 10CS-G "F" would be entered

So what would be entered if it was "10CS-M" can I assume we would enter "Mixed"

See these are the details we need.

 

[Fluff]

Rather than having a dropdown, you can use an input box.
with your classes like this

Excel 2013 32 bit
	A	B
3	10CS-B	M
4	10CS-G	F
5	10CS-M	M
6	10CS-B	M
7	10CS-G	F
8	10CS-M	F
9	10CS-B	M
10	10CS-G	F
11	10CS-M	M
12	10CS-B	M
13	10CS-G	F
Unique

Try

Sub Inputbx()

    Dim Cl As Range

    For Each Cl In Range("A3:A13")
        Select Case Right(Cl, 1)
            Case "B"
                Cl.Offset(, 1) = "M"
            Case "G"
                Cl.Offset(, 1) = "F"
            Case "M"
                Cl.Offset(, 1) = InputBox("Please enter M or F for group " & vbLf & Cl.Value)
        End Select
    Next Cl
    
End Sub

 

[My Aswer Is This]

If my assumptions are correct try this:

Modify to your needs or explain in more detail what your needs are.

Note code marked in red and modify to your needs.

Sub Group_Check()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim c As Long
Dim cc As Long
c = "[COLOR=#ff0000]3[/COLOR]" 'Modify to your needs 3 equals column 3 look in for "10CS-B) for example
cc = [COLOR=#ff0000]"5[/COLOR]" 'Modify to your needs 5 equals column 5 to enter M F or Mixed in.
'This assumes we continue look down the sheet as long as there are values in column "A"
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, c).Value = "10CS-B" Then Cells(i, cc).Value = "M"
        If Cells(i, c).Value = "10CS-G" Then Cells(i, cc).Value = "F"
        If Cells(i, c).Value = "10CS-M" Then Cells(i, cc).Value = "Mixed"
    Next
Application.ScreenUpdating = True
End Sub

 

[Mrstinkleton]

Hi all

Firstly thanks for your replies. Let m try to clarify a bit more. Below is a screenshot of my spreadsheet. This contains two of the groups or classes I teach. One is a mixed group and one is a boys group. What I want is if a class is single gender i.e. the class name ends in a "B" or "G" then either Male or Female gets entered automatically in column E. If however the class name ends in "M" this indicates a mixed gender class and the user needs to select either "M" or "F" manually. It's more of a time saver element and to reduce input errors. Thanks again.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]NAME[/TD]
[TD]GROUP[/TD]
[TD]EMAIL[/TD]
[TD]GENDER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]S2[/TD]
[TD]10BCS_B[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]S3[/TD]
[TD]10BCS_B[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]S4[/TD]
[TD]10BCS_B[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]S5[/TD]
[TD]10BCS_B[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]S6[/TD]
[TD]10CCS_M[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]S7[/TD]
[TD]10CCS_M[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]S8[/TD]
[TD]10GCS_G[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]S9[/TD]
[TD]10GCS_G[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]S10[/TD]
[TD]10GCS_G[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]S11[/TD]
[TD]10GCS_G[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]S12[/TD]
[TD]10GCS_G[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mrstinkleton]

Sorry column D

 

[My Aswer Is This]

So are you saying you want this to happen automatically at the time when you enter the value in column "B"

And why do you say you want the script to enter "M" or "F" automatically but not enter "Mixed" automatically.

 

[Mrstinkleton]

So are you saying you want this to happen automatically at the time when you enter the value in column "B"

And why do you say you want the script to enter "M" or "F" automatically but not enter "Mixed" automatically.

Yes automatically ("M" or "F") as soon as the group/class name is entered if the class/group name ends in "B" or "G" but if it ends in "M" I want the user to be able to select "M" or "F" as we need to identify which students are "M" or "F" for data analysis purposes. In the school groups/classes are usually single gender but at GCSE level we sometimes need to mix classes but we need to be able to see how well each gender performs, so by having gender identified in a field we can easily do this but as I said the classes are mixed on occasion so the teacher/user would need to manually select gender from a list but if the class is single gender then it makes sense for the gender to be auto completed for them.

Thanks again

James

 

[My Aswer Is This]

Edit

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

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"
End If
End Sub