Dropdown sometimes other times auto value

Mrstinkleton

New Member
Joined
Sep 1, 2017
Messages
5
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
Rather than having a dropdown, you can use an input box.
with your classes like this
<b>Excel 2013 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10CS-B</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">10CS-G</td><td style=";">F</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">10CS-M</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">10CS-B</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">10CS-G</td><td style=";">F</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">10CS-M</td><td style=";">F</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">10CS-B</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">10CS-G</td><td style=";">F</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">10CS-M</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">10CS-B</td><td style=";">M</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">10CS-G</td><td style=";">F</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Unique</p><br /><br />

Try
Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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.

Code:
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

New Member
Joined
Sep 1, 2017
Messages
5
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.

ABCDE
1
NAMEGROUPEMAILGENDER
2S210BCS_BM
3S310BCS_BM
4S410BCS_BM
5S510BCS_BM
6S610CCS_MF
7S710CCS_MM
8S810GCS_GF
9S910GCS_GF
10S1010GCS_GF
11S1110GCS_GF
12S1210GCS_GF
13
14
15
16
17
18
19
20
21
22

<tbody>
</tbody>
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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

New Member
Joined
Sep 1, 2017
Messages
5
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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

Code:
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
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top