Select case using a range

**sigfreid** · 07-31-2009, 05:25 AM

Hi,

I'm creating a small spreadsheet for client data in Excel and I want it formatted a certain way, I did consider data validation but it proved to just be annoying.

I've been working on some VBA code to automatically change whatever text is typed into a cell to the correct case (ucase, lcase or proper) and while I can get it working for a single range of cells getting it to work for more is proving difficult.

This is what I have so far;

Private Sub Worksheet_change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    
    Select Case Target
    
    Case Range("C2:C65535")
    
    If Not Application.Intersect(Me.Range("C2:C65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    Case Range("D2:D65535")
    
    If Not Application.Intersect(Me.Range("D2:D65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    Case Range("B2:B65535")
    
    If Not Application.Intersect(Me.Range("D2:D65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    End Select
ErrHandler:
    Application.EnableEvents = True
    
    
End Sub

**royUK** · 07-31-2009, 06:12 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I have added them this time

**sigfreid** · 07-31-2009, 06:58 AM

I think I've fixed it

Private Sub Worksheet_change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    
    Select Case Range("C2:C65535").Text
    
    Case ""
    
    Case Else
    
    If Not Application.Intersect(Me.Range("C2:C65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    End Select
    
        Select Case Range("D2:D65535").Text
    
    Case ""
    
    Case Else
    
    If Not Application.Intersect(Me.Range("D2:D65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            Target.Value = StrConv(Target.Text, vbUpperCase)
            'Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    End Select
    
        Select Case Range("B2:B65535").Text
    
    Case ""
    
    Case Else
    
    If Not Application.Intersect(Me.Range("B2:B65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
    End Select
ErrHandler:
    Application.EnableEvents = True
    
    
End Sub

Appears to work, could probably do with bit of tidying up tho. I wonder is it possible to combine all the ranges that I want in upper case into a single case and then all the ranges I want in proper case into a single one etc? One case for each range will look pretty awful.

**StephenR** · 07-31-2009, 07:10 AM

Why not like this?

Private Sub Worksheet_change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    
    If Not Application.Intersect(Me.Range("B2:D65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbUpperCase)
            Application.EnableEvents = True
        End If
    End If
    
ErrHandler:
    Application.EnableEvents = True
    
End Sub

**sigfreid** · 07-31-2009, 07:57 AM

Originally Posted by StephenR

Why not like this?

Well primarily becuase column B is to be in Proper Case and C an D are in Upper Case, but also because I don't want it to affect the column titles.

I did try

Private Sub Worksheet_change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    
    If Not Application.Intersect(Me.Range("D2:D65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbUpperCase)
            Application.EnableEvents = True
        End If
    End If

    If Not Application.Intersect(Me.Range("B2:B65535"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
    
ErrHandler:
    Application.EnableEvents = True
    
End Sub

But it wouldn't work, hence the cases. I'm Currently trying to tidy up the code a bit for instance, where I need C2:C65535 , D2:D65535 and say G2:G65535 in upper case is it possible to have all 3 ranges in the same case?

**StephenR** · 07-31-2009, 08:19 AM

Select Case Target.Column
Case 2
'code
Case 3
'code
'etc
End Select

Select case using a range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select case using a range

Re: Select case using a range

Re: Select case using a range

Re: Select case using a range

Re: Select case using a range

Re: Select case using a range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions