Excel Formatting in Drop Down Lists

[Lori Brehmer]

I am using various drop down lists in my spreadsheet however when a user
types text rather than selecting the entry from the drop down list the
formatting is not constant. For example. if Jun (proper case) is listed in
my drop down list, the user can enter jun (all lower case) in the cell and it
accepts it. The pivot table reports then have two distinct entries, Jun and
jun.

How can I require the formatting to be consistent with that of the drop down
list?

 

[Gord Dibben]

You could try educating users to only select from list.

Failing that.......with DV lists being case-insensitive.......you could add
some event code to change entries to Proper Case when they are made.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "A1,B2,C3,D4,E5,F6"
If Not Intersect(Target, Me.Range(sINPUTS)) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = Application.Proper(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range to suit then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Wed, 20 May 2009 08:44:01 -0700, Lori Brehmer <Lori

 

[Gord Dibben]

You could try educating users to only select from list.

Failing that.......with DV lists being case-insensitive.......you could add
some event code to change entries to Proper Case when they are made.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "A1,B2,C3,D4,E5,F6"
If Not Intersect(Target, Me.Range(sINPUTS)) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = Application.Proper(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range to suit then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Wed, 20 May 2009 08:44:01 -0700, Lori Brehmer <Lori