# Excel drop down menu's



## fooddestroyer (Sep 29, 2004)

As well as locking certain cells in my workbook is it possible to be able to set up certain cells as drop down menus?


----------



## ReeKorl (Mar 25, 2005)

To do this, select the cell you want as a dropdown, then click Data -> Validation. In settings tab, select Allow: List from the dropdown. In Source, either type in the list manually with list items separated by commas, or select a range of cells which has the different list items you want.

Now when you click on the cell, the little dropdown arrow will appear with the list. A user can try to edit the contents manually, but if the one they put in isn't on the list, they will get an error message.

If you play around with the settings in the Data -> Validation box, you can even set up custom error messages.


----------



## fooddestroyer (Sep 29, 2004)

Many thanks mate. Appreciate your help.

Lastly(hopefully) is there a way that you can make a cell only display fonts as uppercase or titlecase once letters have been inserted no matter if user has caps on or off?


----------



## ReeKorl (Mar 25, 2005)

There is indeed. This VBA code will change any lowercase to uppercase anywhere in the worksheet. If you want to limit it to certain cells, you'll need an IF statement to check to see if the cells are the correct ones.


```
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not (Target.Text = UCase(Target.Text)) Then
        Target = UCase(Target.Text)
    End If
End Sub
```
Say you just want to change the stuff in rows 2 and 3 to uppercase, you would put this as the code (additions in blue):


```
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
[color=blue]    If Target.Row = 2 Or Target.Row = 3 Then[/color]
        If Not (Target.Text = UCase(Target.Text)) Then
            Target = UCase(Target.Text)
        End If
[color=blue]    End If[/color]
End Sub
```
Put this code into the actual worksheet in VBA, not a separate module. NOTE: It will change ANY letters to uppercase, including if they are with numbers aswell, so 123abc456 will be changed to 123ABC456.


----------



## fooddestroyer (Sep 29, 2004)

Sorry this code is over my head mate.

Can you tell me in laymans terms?

Also is it possible to set up the spreadsheet that will automatically datasort in a pre choosen way?


----------



## ReeKorl (Mar 25, 2005)

OK, first of all I don't think there's an easy way of auto data-sorting without setting up a lot of code. It is theoretically possible, but it would take a while to code and test and I'm pretty sure there's no way of doing it built into Excel.

Now, a description of the capitalisation code.

You need to put the code into the VBA section of your workbook. Get to this by clicking Tools -> Macros -> Visual Basic Editor. In the window that comes up, find your rpoject in the top left pane, and browse to the correct worksheet (in the same way Windows Explorer's left pane works) then double click on it. In the pane on the right you now need to paste the code into it, close the VBA editor and save your project. Now, the code itself:

*Private Sub Worksheet_Change(ByVal Target As Excel.Range)*

This makes it activate whenever a change is made to a cell on the worksheet. It sets a variable called Target to be the actual cell that has changed.

*If Target.Row = 2 Or Target.Row = 3 Then*

If the cell which has been changd is either on row 2 or 3 then it will do the next, otherwise does nothing.

*If Not (Target.Text = UCase(Target.Text)) Then*

This stops it getting into an infinite loop constantly capitalising an already capital cell.

*Target = UCase(Target.Text)*

This actually does the capitalisation. Target.Text is what is actually in the cell, and is puts this value, after having UCASE'd (capitalised) it back into wherever it came from. If you want only cell D5 capitalised automatically, you'd have this code:


```
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    [color=red]If Target.Column = 4 Then[/color]
        [color=blue]If Target.Row = 5 Then[/color]
            If Not (Target.Text = UCase(Target.Text)) Then
                Target = UCase(Target.Text)
            End If
        [color=blue]End If[/color]
    [color=red]End If[/color]
End Sub
```
The black text will always be there. The red text is saying if the cell is in column 4 (column D) then keep going, otherwise stop doing anything to it. Likewise, the blue text is saying if the cell is in row 5 then keep going, otherwise stop.

You can customise the code by changing the red and blue lines, so if the area you want capitalising is a square from A3 to D6 you would change the red to say

```
[color=red]If Target.Column < 5 Then[/color]
```
and the blue to say

```
[color=blue]If Target.Row > 2 And Target.Row < 7 Then[/color]
```
Hope this makes sense!


----------



## fooddestroyer (Sep 29, 2004)

Waoh. Many thanks Bud- ill have to try that out when i get into work tomorrow mate..

Keep up the Stirling work!!


----------

