Custom Cell format

[iLuvbase]

I need to create a custom cell format that is immune to how someone enters types their data.

Basically we generate quotes on a Q# basis, but we may have multiple configurations for a single quote number.

e.q - we may have Q4457, Q4457A, Q4457B, C - etc.

However - coworkers enter data in different manners.

For example - if I enter 4457, I want the cell to display Q4457.

If I enter Q4457 I want it to read Q4457.

If i enter 4457A, I need it to read Q4457A.

If I enter Q4457A, I need it to read Q4457A.

Things I've tried -

Q# - Displays properly for 4457 or Q4457, and Q4457A - does not work for 4457A (Shows only 4457A, no leading Q)

Q@ - Displays properly for 4457 and 4457A - Does not work for Q4457 or Q4457A (displays 2 Qs at beginning)

 

[grizz]

the code supplied by Mikerickson works well ---- will put Q in column A

 

[iLuvbase]

Did exactly what was specified - right click sheet, view code, pasted code - works for all of Column A, but I don't need it to work for Column A.

I need it to work for a specific cell (Row 2 - Merged Cells columns AM-AR (Columns 39-42 if doing numerical)

Tried specifying in the code as

#If .Cells.Count = 1 And Not (Application.Intersect(.Cells, Columns(2, 39)) Is Nothing) Then#

 

[Rick Rothstein]

I need it to work for a specific cell (Row 2 - Merged Cells columns AM-AR (Columns 39-42 if doing numerical)

And you told us this when? It might have help to have mentioned this earlier. Give this modification to mikerickson'a code a try...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Address(0, 0) = "AM2" Then
            If CStr(.Value) Like "[!Qq]*" Then
                .Value = "Q" & CStr(.Value)
            Else
                .Value = "Q" & Mid(CStr(.Value), 2)
            End If
        End If
    End With
End Sub

By the way, I changed his second If..Then test a little bit.

 

[iLuvbase]

Still doesn't work for the merged cells -

If I change the cell location from AM2 to an unmerged cell (say AM4) and type in AM4, it works properly.

The issue with the code not working for the particular cell seems to derive from the Merged Cell feature - and no, I can't unmerge the cells due to layout of the overall page.

 

[Rick Rothstein]

Still doesn't work for the merged cells -

If I change the cell location from AM2 to an unmerged cell (say AM4) and type in AM4, it works properly.

The issue with the code not working for the particular cell seems to derive from the Merged Cell feature - and no, I can't unmerge the cells due to layout of the overall page.

I tested the code with merged cells before I posted it and it worked fine here. The cells I merged were AM2:AR2 which seemed to be the range that you said was merged... is it? By the way... is this formatting only for one row (Row 2) or will there be other rows that also need it (the code will need to be modified if so).

 

[iLuvbase]

will be used ONLY for merged cells beginning AM2-AR2

Just tried using different merged cells -

First tried using unmerged cell - only AM4 - test worked, responded properly.

Then merged cells AM4-AR4, repeated test - did not work. Cell displays only what I type manually - no correction adding "Q" as a prefix.

 

[Rick Rothstein]

You have to be careful with the wording you use when you ask your questions... we tend to code to what you say, not what we think you might have meant. You said: "I need it to work for a specific cell (Row 2 - Merged Cells columns AM-AR (Columns 39-42 if doing numerical)"... that is, even for a merged cell, the single cell AM2 only, so I coded for that. Now you are saying any row in Column AM (it doesn't really matter if the cells in Column AM are merge or not, as long as Column AM is the left most cell). Anyway, see if this code works for you...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If Not Intersect(Columns("AM"), Target) Is Nothing Then
            If CStr(.Value) Like "[!Qq]*" Then
                .Value = "Q" & CStr(.Value)
            Else
                .Value = "Q" & Mid(CStr(.Value), 2)
            End If
        End If
    End With
End Sub

 

[iLuvbase]

You have to be careful with the wording you use when you ask your questions... we tend to code to what you say, not what we think you might have meant. You said: "I need it to work for a specific cell (Row 2 - Merged Cells columns AM-AR (Columns 39-42 if doing numerical)"... that is, even for a merged cell, the single cell AM2 only, so I coded for that. Now you are saying any row in Column AM

I apologize if I was unclear - but I did not say I needed it to work for all columns.

I stated that it needed to work ONLY for the Merged Cell range AM2:AR2 .

will be used ONLY for merged cells beginning AM2-AR2

But, when it failed to work for that merged range, i tested on a different cell (AM4) where it worked, and then merged the range AM4:AR4 (and the code no longer worked).

I appreciate the wonderful effort you have put forth thus far, everyone here has been great. However, it still just isn't working for me.

 

[grizz]

Rick Rothstein's code for merged cell row 2 works for me ---- you do not have to change anything in this code --- assuming your merged cells are AM2-AR2
question -- how are your cells merged

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Address(0, 0) = "AM2" Then
            If CStr(.Value) Like "[!Qq]*" Then
                .Value = "Q" & CStr(.Value)
            Else
                .Value = "Q" & Mid(CStr(.Value), 2)
            End If
        End If
    End With
End Sub

 

[iLuvbase]

Rick Rothstein's code for merged cell row 2 works for me ---- you do not have to change anything in this code --- assuming your merged cells are AM2-AR2
question -- how are your cells merged

Don't know what to tell you guys other than it just does not work for me in this form. No matter what code I've tried, it just won't work for the merged cells.

Perhaps it's not the issue of the merged cells specifically, but maybe it has to due with the fact that I'm stuck in Excel 97 until the company upgrades to Office 2010 at the end of this year.

I assume that most everyone else is using a newer version of Excel.