change case based on word

[Trevor3007]

hi,

hoping this finds well & safe.

I hope someone can resolve my issue?

Is it possible so that certain words in a range ie :F26-F100, can be forced to either upper \proper or lower.
so:-
if 'wip' is put into f26, it will be forced to WIP
if 'completed 'is put into f27, it will be forced to Completed
if 'tba 'is put into f27, it will be forced to To Be Advised
etc...

So, if any of the applicable is inputted in to the range (F26-F100) it will 'case,CASE, \Case,' accordingly.



Hoping this makes sense & I look forward to receiving your replies.

KR
Trevor3007

 

[dmt32]

Hi,
try following & see if will do what you want

Place code in your worksheets CODE PAGE

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitsub
    If Not Intersect(Target, Range("F26:F100")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = ForceText(Target)
    End If
exitsub:
Application.EnableEvents = True
End Sub

Place following code either in your sheets code page or in a standard module

Function ForceText(ByVal Target As Range) As String
    Dim m As Variant
    With Target
        m = Application.Match(.Value, Array("wip", "completed", "tba"), 0)
        If Not IsError(m) Then
            ForceText = IIf(m = 1, StrConv(.Value, vbUpperCase), _
            IIf(m = 2, StrConv(.Value, vbProperCase), _
            Replace(.Value, "tba", "To Be Advised")))
        Else
            ForceText = .Value
        End If
    End With
End Function

Hope Helpful


Dave

 

[ashakantasharma]

Apart from VBA you can also prepare a data-set of Short Abbreviations with Full Forms as required and use a combination of formula like =IFNA((Vlookup(),PROPER()))

 

[Peter_SSs]

1. Are these the only 3 'case-restricted' values or are they just a small sample of a larger 'case-restricted' list?
   
   
2. Are other values (without 'case-restrictions') also allowed to be entered in that range?

@dmt32
Dave, you may want to consider adjustment to your code as currently it can be defeated. Example: Select F34:F35 type wip and confirm into both cells with Ctrl+Enter. Similarly, pasting multiple values may also allow the wrong 'case' to be entered.

 

[dmt32]

@dmt32
Dave, you may want to consider adjustment to your code as currently it can be defeated. Example: Select F34:F35 type wip and confirm into both cells with Ctrl+Enter. Similarly, pasting multiple values may also allow the wrong 'case' to be entered.

Thanks for this -Aware of potential issues you outline after I posted - if indeed OP will be doing any of that but solution was quickly written based on posted requirement - Happy to wait for OPs response & if has issues with it can be updated or maybe another will have posted a cleaner solution.

Dave

 

[Trevor3007]

Apart from VBA you can also prepare a data-set of Short Abbreviations with Full Forms as required and use a combination of formula like =IFNA((Vlookup(),PROPER()))

Hi,
Thanks very much for your asstaince & my apologees for the delay in replying. Am sure your sugg is great..but its beyond my knowlege . Have great day & thanks very much again.

 

[Trevor3007]

Thanks for this -Aware of potential issues you outline after I posted - if indeed OP will be doing any of that but solution was quickly written based on posted requirement - Happy to wait for OPs response & if has issues with it can be updated or maybe another will have posted a cleaner solution.

Dave

Hi,

So sorry for the delay in replying & thank you your assitance too. I pasted the code as instructed but nothing happend. 'wip' stayed as lower case (or however I cased) same for tba & compliant?

Have a save & good day too.
KR
Trevor3007

 

[dmt32]

Hi,

So sorry for the delay in replying & thank you your assitance too. I pasted the code as instructed but nothing happend. 'wip' stayed as lower case (or however I cased) same for tba & compliant?

Have a save & good day too.
KR
Trevor3007

How are you entering values - direct entry cell at a time, multi entry as described by Peter_SSs??

Dave

 

[Trevor3007]

How are you entering values - direct entry cell at a time, multi entry as described by Peter_SSs??

Dave

So sorry Dave for not replying sooner.

Normally 'direct entry cell at a time' .

Many thanks again.
KR
Trevor3007

 

[dmt32]

So sorry Dave for not replying sooner.

Normally 'direct entry cell at a time' .

Many thanks again.
KR
Trevor3007

No worries in your own time.
If you followed instructions where to place the codes & are making direct entry one cell at a time, solution should have done what you asked for.

Are you able to place copy of your workbook in a dropbox (with sensitive data removed) this will give forum better opportunity to understand the issue

Dave