Macro to lock string in cell

[msoutopico]

Hi there,

I have an Excel file with several cells containing text. The text contains some codes and I would like to know whether it's possible to lock those codes (by means of some formula or VBA macro, for example). The aim is that any text contained in the cell can be modified except those codes.

I know a whole cell can be locked/protected but I don't think it is possible to lock only part of the content of the cell. Am I right?

The codes could be matched with some regexp pattern or wildcard. E.g. The cell's content is, say: "Blabla some text [ABC_123] blabla more text blabla." I would like to lock any text being matched by regexp \[.+?\].

Thank you very much for your answers in advance!

Cheers, Manuel

 

[pjmorris]

Hi Manuel,

Firstly welcome to the forum, hopefully you will find plenty of answers here.

You need to Validate the cell your concerned about. To do so highlight the cell and then click on Data Validation on Data ribbon. In the dialogue box that appears, in the Validation Criteria section under Allow: select Custom.
In the Formula box enter =FIND("ABC_123",K6)>0 assuming that K6 is the cell in question. You can copy the validation (Paste Special) to any other cells that are relevant.

Hope this helps.

Regards

 

[msoutopico]

Hi Peter,

Thank you very much for your helpful answer. I had to tweak the formula a little bit to my system's locale, but it seems to work. I assume that the ">0" bit means that the substring should be found "anywhere" within the cell.

This formula seems to be perfect to match one literal substring in the cell, but my case is different:

1) There might be more than one code in each cell.
2) The codes will all be different.

A1	Blabla some text [ABC_123] blabla more text [ABC_456] blabla.
A2	Blabla some text [XYZ_123] blabla more text [XYZ_456] blabla.

<tbody>
</tbody>

I suppose I could cover all codes combining formulae with AND:

=AND(FIND("[ABC_123]",A1)>0, FIND("[ABC_456]",A1)>0)

However, I would need to write different formulae for each cell. My initial idea to use regular expressions was meant to avoid that, but
I realize that that might not work with the FIND approach.

Would writing a specific formula for each cell to match the codes literally be the only way to go?

Really thankful for your help!
Cheers, Manuel

 

[Rick Rothstein]

Hi Peter,

Thank you very much for your helpful answer. I had to tweak the formula a little bit to my system's locale, but it seems to work. I assume that the ">0" bit means that the substring should be found "anywhere" within the cell.

This formula seems to be perfect to match one literal substring in the cell, but my case is different:

1) There might be more than one code in each cell.
2) The codes will all be different.

A1	Blabla some text [ABC_123] blabla more text [ABC_456] blabla.
A2	Blabla some text [XYZ_123] blabla more text [XYZ_456] blabla.

<tbody>
</tbody>

Here is some event code that, if I did it correctly, will prevent the user from changing any code (3 upper case letters followed by an underline followed by 3 digits) surrounded by square brackets from being changed, but allowing any other text in the to be changed. Try it and let me know if it actually works for you...

Dim CodeText As String, OriginalText As String

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long, Codes() As String
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    Codes = Split(Trim(CodeText))
    For X = 0 To UBound(Codes)
      If InStr(Target.Value, Codes(X)) = 0 Then
        MsgBox "You tried change a code value which is not allowed!"
        Target.Value = OriginalText
      End If
    Next
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim X As Long, CellText As String, Parts() As String
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    OriginalText = Target.Value
    CellText = Replace(Target.Value, "[", "]")
    Parts = Split(CellText, "]")
    For X = 1 To UBound(Parts) Step 2
      If Parts(X) Like "[A-Z][A-Z][A-Z]_###" Then CodeText = CodeText & "[" & Parts(X) & "] "
    Next
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the above event code, exactly as presented, into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[pjmorris]

Hi Mauel,

Thjs is a slightly different approach:

Firstly, create a Userdefined Function by copying this code into a module in VBA:

Function StrCheck(str As String)
    Dim src As Range
    Dim code As String
    Dim n As Integer, m As Integer
    Dim chk As Boolean
    
    Set src = Range("codes")
    
    n = 1
    While InStr(n, str, "[") > 0
        n = InStr(n, str, "[")
        code = Mid(str, n, 9)
        
        chk = False
        For m = 1 To src.Count
            chk = (code = src(m))
            If chk Then Exit For
        Next m
        If chk = False Then 'the code hasn't been found in the src list and hence this is an error
            StrCheck = chk
            Exit Function
        End If
        n = n + 1
    Wend
    StrCheck = True
End Function

then, on sheet 2, create a dynamic list named 'codes', in the name manager this would be defined as =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A$2:$A$100)). I placed the word 'Codes' in cell A1 and then put a list of valid codes in the cells below it. This has the advantage that you can add to the list (or delete) whenever you want.

Then in a blank cell adajacent to the cell to be protected, for example in K8 enter =strcheck(A8) if the cell to be checked is in A8. Then, finally, in the cell to be protected setup a custom validation with the formula =K8. It seems that validation does not allow the use of UserDefined functions as custom validation hence the need to put the formula in another cell. Perhaps Rick will have a better solution.

This solution has the benefit of being able to define precisely the acceptable codes. However it is worth noting that none of these solutions will prevent one valid code being changed to another nor the code being deleted entirely.

Hope this helps.

 

[Rick Rothstein]

However it is worth noting that none of these solutions will prevent one valid code being changed to another nor the code being deleted entirely.

Excellent point which I completely missed! Here is my event code approach modified to account for this...

Dim CodeText As String, OriginalText As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    If CodeText <> "" Then
      If Target.Value = "Delete=Okay" Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
      ElseIf CodeText <> Codes(Target.Text) Then
        MsgBox "You tried change a code value which is not allowed!"
        Application.EnableEvents = False
        Target.Value = OriginalText
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    OriginalText = Target.Value
    CodeText = Codes(Target.Value)
  End If
End Sub

Function Codes(ByVal S As String) As String
  Dim X As Long, CellText As String, Parts() As String
  CellText = Replace(S, "[", "]")
  Parts = Split(CellText, "]")
  For X = 1 To UBound(Parts) Step 2
    If Parts(X) Like "[A-Z][A-Z][A-Z]_###" Then Codes = Codes & "[" & Parts(X) & "] "
  Next
End Function

Obviously, the above code should completely replace the code I posted earlier. Note that I have also given you a method to clear a cell that contains codes in it (otherwise once text with codes in it has been placed, it would never be able to be deleted)... the way to do this is to enter "Delete=Okay" (without the quotes, but with the letter casing shown) into the cell containing codes that you want to clear.


HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the above event code, exactly as presented, into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[msoutopico]

Dear Rick and Peter,

I have to say that I'm fondly grateful for your willingness to help. Thank you so much.

I have a feeling that I should be using other kind of tool (perl, python, php, etc.) to check this out of Excel, but I will still give it a go to Excel/VBA. I would like to understand your code samples, guys, but rather than asking you to comment it, I will study it and do some research to find about the functions you use, I think that's the best way to learn.

@Rick, I have a couple of questions, though.

1) Your procedure prevents changing the expression inside the square brackets (e.g. [ this is protected ] but one can remove the brackets, which are part of the code. That is, [ABC_123] should be protected and not only ABC_123. Is that possible?

2) On the other hand, my codes where just a simplification. The real codes are [[T001]], [[T00101]], [[T004a01]], [[T80]], [[T99901_1]], [[T99904]]. etc. I can create the expression "\[\[T[0-9a-b_]@\]\]" (or just "\[\[T[!\]]@\]\]"), without the quotes, using Word advanced wildcards, which I suppose can be used in VBA. However, I suppose something should be tweaked in your procedure, but I don't know what.

3) One last thing. Would it be possible to use .NET regular expressions instead of wildcards in your procedure at all?

I hope my questions are somehow interesting for you. Thanks a lot!

Cheers, Manuel

 

[Rick Rothstein]

@Rick, I have a couple of questions, though.

See individual responses below...

1) Your procedure prevents changing the expression inside the square brackets (e.g. [ this is protected ] but one can remove the brackets, which are part of the code. That is, [ABC_123] should be protected and not only ABC_123. Is that possible?

I do not believe that is true, at least not for the last code I posted in Message #6.

2) On the other hand, my codes where just a simplification. The real codes are [[T001]], [[T00101]], [[T004a01]], [[T80]], [[T99901_1]], [[T99904]]. etc. I can create the expression "\[\[T[0-9a-b_]@\]\]" (or just "\[\[T[!\]]@\]\]"), without the quotes, using Word advanced wildcards, which I suppose can be used in VBA. However, I suppose something should be tweaked in your procedure, but I don't know what.

Why would you simplify your codes like that? All doing that will get you (as you have seen) is a great answer to a question you really do not have and which you do not really care about! You will not be able to you Word macro (regular) expressions in Excel macro... Word and Excel are two completely different worlds with completely different data handling object handling functions at their disposal. So, I have a question about the structure of your real codes... would looking for text that starts with "[[T" and ends with "]]" be sufficient to identify your codes or is it possible for there to be text inside your cell values that start and end with those which are not real codes? If the answer to the first part of my question is that those text fragments are enough to identify your codes, then I have all I need to develop a solution for you; however, if the answer to the latter part of my question is "yes", then you will need to describe the "shape" of your codes in more detail; for example...


1. Can there be more than one lower case letter in the code?


2. If yes, can they be next to each other or will there always be one (or more?) digit(s) between them?


3. Can there be more than one underline character in the code?


4. If yes, can they be next to each other or will there always be one (or more?) digit(s) between them?

3) One last thing. Would it be possible to use .NET regular expressions instead of wildcards in your procedure at all?

I do not know any of the .NET languages, so I cannot answer your question directly. However, there is a way to include a scripting object for Regular Expressions, but unless your needed text patterns are horribly complex, I have found that, generally, using native VBA coding is quicker in the long run than calling out to a RegExp engine via the scripting objects... you needed text patterns do not seem to be horribly complex to me, so I would recommend sticking with native VBA coding throughout. And as soon as you answer the questions I posed to your #2 question above, I will code up a solution (using my event code approach) for you.

 

[msoutopico]

Hi Rick,

Simplifying my problems is a habit that I have when asking for help, I do that out of courtesy with the aim of simplifying the problem for those who might want to help, so that they/you can focus on the core issue that poses the problem and not be distracted by accessory elements. In this case I was interested in the procedure itself and I didn't think changing the strings to be matched would change the procedure at all. I'm sorry that you spent time providing an elaborate answer that wasn't 100% helpful :/

Answer to your question: Right now, if a string begins with [[T and ends with ]], it's a code that must be protected. However, future versions of my data might be different. In any case, the answer to the second part of your question is "no".

Protecting those codes is the main need I have, but just in case you prefer to have the whole picture, the data also include some other tags/codes whose delimiters should be maintained, even if the content changes. Therefore:

[[T99901_1]] should be kept unaltered: [[T99901_1]]
(Q100-Q115) should be kept unaltered: (Q100-Q115)
(Q104) should be kept unaltered: (Q104)
{{foo}} could be changed to {{bar}} but not to /bar/, bar, {bar}, {bar or anything which doesn't begin with {{ and end with }}
<1<foo>1> could be changed to <1<bar>1> but not to <bar>, bar, or anything which doesn't begin with <1< and ends with >1>
<foo> could be changed to <bar> but not to <bar/, bar, or anything which doesn't begin with < and ends with >

There could be more than one <foo><foo>, {{foo}}, etc. in each cell. In case it helps, the cells containing the codes are all in one column, and this column is a duplicate of another column that is not editable (the whole column is locked).

	D	F
1	Blabla [[T99901_1]] blabla [[T99902]] blabla.	Blublu [[T99901_1]] blublu [[T99902]] blublu.
2	Blabla {{foo}} blabla {{moo}} and [[T80]].	Blublu {{bar}} blublu {{jar}}and [[T80]].

<tbody>
</tbody>

As you can see, the problem is more complex. My hope was to be able to use your help to build a simple macro for my main pattern and then elaborate it more on my own to account for all cases.

By the way, I have seen there's an add-in to enable VBScript regex in Excel (which I assume to be like .NET regex): https://github.com/malcolmp/excel-regular-expressions. I will have a look, bearing in mind your advice to stick to native VBA coding.

Thanks a lot!
Cheers, Manuel</foo>

 

[Rick Rothstein]

Hi Rick,

Simplifying my problems is a habit that I have when asking for help, I do that out of courtesy with the aim of simplifying the problem for those who might want to help, so that they/you can focus on the core issue that poses the problem and not be distracted by accessory elements. In this case I was interested in the procedure itself and I didn't think changing the strings to be matched would change the procedure at all. I'm sorry that you spent time providing an elaborate answer that wasn't 100% helpful :/
<foo><bar><bar><foo><bar><bar with="" ends="" and="" <="" begin="" doesn't="" which="" anything="" or="" bar,="" ,=""><foo>

As I said, it has been my experience that simplifying a problem almost always leads to more work for the volunteers whose help you seek and, as a result, leads to delays in your getting the answer you seek. In the case for your thread, the code you told us you had (square bracket, 3 upper case letters, dash, 3 digits) was not even close to what you are now saying you have. Again, it has been my experience that posters who do that (simplify their data so it does not look anything like their real data), almost always come back here when they cannot figure out how to modify someone else's code to match what their data actually looks like.
</foo></bar></bar></foo></bar></bar></foo>

Answer to your question: Right now, if a string begins with [[T and ends with ]], it's a code that must be protected. However, future versions of my data might be different. In any case, the answer to the second part of your question is "no".

Protecting those codes is the main need I have, but just in case you prefer to have the whole picture, the data also include some other tags/codes whose delimiters should be maintained, even if the content changes. Therefore:

[[T99901_1]] should be kept unaltered: [[T99901_1]]
(Q100-Q115) should be kept unaltered: (Q100-Q115)
(Q104) should be kept unaltered: (Q104)
{{foo}} could be changed to {{bar}} but not to /bar/, bar, {bar}, {bar or anything which doesn't begin with {{ and end with }}
< 1<foo>1> could be changed to <1<bar>1> but not to <bar>, bar, or anything which doesn't begin with <1< and ends with >1>
<foo> could be changed to <bar> but not to <bar with="" ends="" and="" <="" begin="" doesn't="" which="" anything="" or="" bar,="" ,="">

There could be more than one <foo><foo>, {{foo}}, etc. in each cell. In case it helps, the cells containing the codes are all in one column, and this column is a duplicate of another column that is not editable (the whole column is locked).
</foo>

</foo></bar></bar></foo></bar></bar></foo>The following code should correctly lock the codes within the text in Column F for the "[[T..]]" and "(Q..)" types of code. As for the other codes, given they can be changed within their delimiters, as long as the delimiters are not changed... that is different from barring an outright change in any part of the code, so I'll have to think about that a little more in order to determine how implement it. Anyway, again, here is the event code approach, so installation instructions are the same as before, for the "[[T..]]" and "(Q..)" types of codes...

Dim CodeText As String, OriginalText As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    If CodeText <> "" Then
      If Target.Value = "Delete=Okay" Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
      ElseIf CodeText <> Codes(Target.Text) Then
        MsgBox "You tried change a code value which is not allowed!"
        Application.EnableEvents = False
        Target.Value = OriginalText
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    OriginalText = Target.Value
    CodeText = Codes(Target.Value)
  End If
End Sub

Function Codes(ByVal S As String) As String
  Dim X As Long, CellText As String, Parts() As String
  ' [[T...]]
  CellText = Replace(S, "[[", "]]")
  Parts = Split(CellText, "]]")
  For X = 1 To UBound(Parts) Step 2
    If Parts(X) Like "T*" Then Codes = Codes & "[[" & Parts(X) & "]] "
  Next
  '  (Q...)
  CellText = Replace(S, ")", "(")
  Parts = Split(CellText, "(")
  For X = 1 To UBound(Parts) Step 2
    If Parts(X) Like "Q*" Then Codes = Codes & "(" & Parts(X) & ") "
  Next
End Function