Lock format in cell - even if copy and pasting to that cell

Re: Lock format in cell - even if copy and pasting to that cell

Hi cjc,

I'd be interested to see other solutions.

This is the best i can produce. Maybe a start? Please note that this example assumes that you have A1 formated with your standards: Custom Format being #-#####-#####-# and DataValidation being Custom with the formula =Len(A1)=12, restricting the user to enter 12 digits, no more, no less. You could adjust to accept this and/or more and/or less with something like this =OR(LEN(A1) = 12, LEN(A1) = 15)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:A65000")) Is Nothing Then
        Range("A1").Copy
        With Target
            .PasteSpecial Paste:=xlPasteValidation
            .PasteSpecial Paste:=xlPasteFormats
            .ClearOutline
        End With
    End If
End Sub

Stefan
p.s.
This would need to go into the Sheet module where users have the problem applying the right input.

Re: Lock format in cell - even if copy and pasting to that cell

Quote from StefanG

Stefan
p.s.
This would need to go into the Sheet module where users have the problem applying the right input.

Stefan, your idea looks like a good way to get things sorted, the only other thing is does / will it throw an error if say 13 numbers are added etc ? if so it may need an error handler that if so it clears the contents, resets the format and maybe a msgbox telling the user the correct layout needed to be input ? also is there a way without copy a1 to apply the formats needed ?

if it does not throw errors then all is good

Re: Lock format in cell - even if copy and pasting to that cell

Hi DOC,

Quote

will it throw an error if say 13 numbers are added

Well, that would be sorted out with the data validation, which restricts input longer/shorter then required. I could not figure how to do that in the code, hence thought why not have a "mastercell" and copy its correct formatting and validation... btw, this does not allow to paste single cells in the set area, which seemed to cause some problems with the users in the given example. I'm sure there are nicer and much better ways. This seemed like an interesting problem and thought to give it a try... we'll lern more, when the pro's step in, as usual.
Stefan

Re: Lock format in cell - even if copy and pasting to that cell

Quote from cjc

Hi - thanks so much for responding! I used both your suggestions, but now anytime I click on one of the cells with the data validation, I get a runtime error "pastespecial method of range class failed."
Also, I should have mentioned my range may change if more rows are needed. What would I need to do in the sheet module to adjust for this?
Thanks! cjc

Can you post up a small sample workbook, the range should be fine as it takes in most of col a so it should be fine. als when you say click on a cell is this to paste the data ? and can you supply some sample data ( same page is fine of data to be pasted.

Re: Lock format in cell - even if copy and pasting to that cell

I think the Worksheet_SelectionChange event will fire immediately upon selection of the cell which will clear the clip board where the user has stored the value they are copying.

Jim

Re: Lock format in cell - even if copy and pasting to that cell

I think StefanG's idea will work with the assumption that there will be something already copied. Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:A65000")) Is Nothing Then
        On Error Resume Next
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub

Jim

Re: Lock format in cell - even if copy and pasting to that cell

Hi,

Quote

cjc:
I get a runtime error "pastespecial method of range class failed."

I'm unable to reproduce the error. However, MS-KB article 231090 addresses this error and appears to be related to "xlColumnWidths". So I wonder if the (adjusted?) code you used caused the error¿

Maybe you could, as DOC suggested, post a sample, or see if, when you add, as Jim has in this code, On Error Resume Next, which would skip the error, it works (but will it still perform as needed?)
Stefan

Re: Lock format in cell - even if copy and pasting to that cell

That's odd.. I wonder what paste constants 1-7 stand for...

Re: Lock format in cell - even if copy and pasting to that cell

Just use Data Validation with Formula is: and use the Worksheet Change Event to re-apply as needed.

Re: Lock format in cell - even if copy and pasting to that cell

Hi Jim,

Quote from JimFuller1

That's odd.. I wonder what paste constants 1-7 stand for...

I couldnt find anything documented, but found this to be true:
1= xlPasteAll
2= xlPasteFormulas
3= xlPasteValues
4= xlPasteFormats
5= xlPasteComments
6= xlPasteValidation
7= xlPasteAllExceptBorders
8= xlPasteColumnWidths
... and couldnt find an operator for
xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats
... or an operator to combine these into one line of code. So i guess something like this has to do (?).

With Selection
        .PasteSpecial Paste:=4
        .PasteSpecial Paste:=6
    End With

Then again, why not stay with the "real" code instead of having to remember what the ## stand for... unless confusion is the goal ;-).
Stefan

Re: Lock format in cell - even if copy and pasting to that cell

Agree, the constants should be used when writing code. Unfortunately the macro recorder often uses the numeric representation.

See PasteSpecial method as it applies to the Range object in the VBA help.