1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove CF's, DV's, Formulas & Protect

Discussion in 'VBA Macros' started by dparteka, Nov 17, 2017.

  1. dparteka

    dparteka Member

    Messages:
    163
    I've been attempting to put this together and have concluded it's just beyond my skill level, any help would be awesome and greatly appreciated... thanks for looking.

    I'm using the code shown below which looks in column-A for "+", then for all rows that have this character it locks each cell in that row and then protects the sheet, I'm hoping to expand this to also include the following only in those rows that have "+" in column-A:
    a. Remove all conditional formats
    b. Remove all data validations
    c. Replace all formulas with Values
    d. Change all fonts to black
    e. Change all “+” to “-“

    Here's everything that should happen
    1. Unprotect the worksheet
    2. Remove all conditional formats
    3. Remove all data validations
    4. Replace all formulas with Values
    5. Change all fonts to black
    6. Change all “+” to “-“
    7. Lock each cell
    8. Protect the worksheet

    Code (vb):

    Private Sub CommandButton2_Click()
      ActiveSheet.Unprotect
      Dim rChk As Range, r1st As Range
      Set r1st = Columns("A").Find(What:="+", _
      after:=Cells(Rows.Count, "A"), _
      LookIn:=xlValues, LookAt:=xlPart, _
      searchdirection:=xlNext)
      If Not r1st Is Nothing Then '+ is found
     Set rChk = r1st
      Do
      rChk.EntireRow.Locked = True
      Set rChk = Columns("A").FindNext(after:=rChk)
      Loop While rChk.Address <> r1st.Address ' else endless loop
     End If
      Set r1st = Nothing
      Set rChk = Nothing
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
      , AllowSorting:=True, AllowFiltering:=True
    End Sub
  2. chirayu

    chirayu Well-Known Member

    Messages:
    786
    Try This. Didn't really understand your replace + with - request cos formula to value = you get the output. not the formula. right now my macro checks the formula rather than output so edit accordingly

    Code (vb):
    Sub CheckyWecky()

    ActiveSheet.Unprotect Password:="1"

    Dim Rng As Range
    Dim SubRng As Range

    Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each SubRng In Rng
     
        If SubRng.Formula Like "*+*" Then
            SubRng.FormatConditions.Delete
            SubRng.Validation.Delete
            SubRng = SubRng.Value
            SubRng.Font.Color = RGB(0, 0, 0)
            SubRng.Replace What:="+", Replacement:="-", LookAt:=xlPart
            SubRng.EntireRow.Locked = True
        Else
            SubRng.EntireRow.Locked = False
        End If
    Next SubRng

    ActiveSheet.Protect Password:="1"


    End Sub
  3. dparteka

    dparteka Member

    Messages:
    163
    Thank you for helping... it's not working and I'm not sure what's happening but it may have to do with the formula in column-A. When I run the macro it wipes out all the formulas in column-A, I'm guessing because there's a "+" in the actual formula. The reason for the replacement of + to - was so the next time the macro runs it doesn't look at the rows that have the -. When running the macro it pretty much just does the + - replacement and nothing else.

    Attached Files:

  4. chirayu

    chirayu Well-Known Member

    Messages:
    786
    Ah ok my macro was reading the + in the formula rather than the + as a value. I'm assuming then that the formula itself doesn't need to be edited but instead the cell needs to be value pasted & then changed to a - etc
    I was confused by your requirements because I thought you meant change the + inside the formula to a - and then value paste

    Code (vb):
    Sub CheckyWecky()

    ActiveSheet.Unprotect Password:="1"

    Dim Rng As Range
    Dim SubRng As Range

    Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each SubRng In Rng
     
            If SubRng = "+" Then
                SubRng.FormatConditions.Delete
                SubRng.Validation.Delete
                SubRng = "-"
                SubRng.Font.Color = RGB(0, 0, 0)
                SubRng.EntireRow.Locked = True
            Else
                SubRng.EntireRow.Locked = False
            End If
    Next SubRng

    ActiveSheet.Protect Password:="1"

    End Sub
    Thomas Kuriakose likes this.
  5. dparteka

    dparteka Member

    Messages:
    163
    chirayu... it's like magic, very impressive. In the first code you provided you included SubRng = SubRng.Value to replace formulas with values. I tried adding this to your latest code but it's not working. I may not have been clear on this, cells in columns A, E, L, N, O, P, Q have formulas that need to be replaced with values, how would I go about doing that?
  6. chirayu

    chirayu Well-Known Member

    Messages:
    786
    SubRng only refers to the cells listed in Rng i.e. in the code this bit tells the macro to count number of non empty cells in column A and store it as a Range

    Code (vb):
    Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

    Then this bit starts a loop to run through each cell address that was stored in Rng. Each individual cell is essentially a SubRng

    Code (vb):
    For Each SubRng In Rng
    'Rest of the macro
    Next SubRng

    So When we say SubRng = SubRng.Value it means take the value of the individual cell & put it in that cell - essentially replacing the formula.

    If you want to replace other cells in the same row then you need to store the row number from the SubRng as per modified code below.

    Code (vb):
    Sub CheckyWecky()

    ActiveSheet.Unprotect Password:="1"

    Dim Rng As Range
    Dim SubRng As Range
    Dim SubRow As Integer

    Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each SubRng In Rng

        If SubRng = "+" Then
            SubRow = SubRng.Row
            SubRng.FormatConditions.Delete
            SubRng.Validation.Delete
            SubRng = "-"
            SubRng.Font.Color = RGB(0, 0, 0)
            SubRng.EntireRow.Locked = True
            Range("E" & SubRow) = Range("E" & SubRow).Value
            Range("L" & SubRow) = Range("L" & SubRow).Value
            Range("N" & SubRow) = Range("N" & SubRow).Value
            Range("O" & SubRow) = Range("O" & SubRow).Value
            Range("P" & SubRow) = Range("P" & SubRow).Value
            Range("Q" & SubRow) = Range("Q" & SubRow).Value
        Else
            SubRng.EntireRow.Locked = False
        End If
     
    Next SubRng

    ActiveSheet.Protect Password:="1"

    End Sub

    Right now the rest of the stuff like remove data validation/ conditional formats/ font colour black etc will only work for SubRng but if you want those to work for the entire row then change Below bits as shown

    Code (vb):
    'Replace This
    SubRng.FormatConditions.Delete
    SubRng.Validation.Delete
    SubRng.Font.Color = RGB(0, 0, 0)

    'With This
    SubRng.EntireRow.FormatConditions.Delete
    SubRng.EntireRow.Validation.Delete
    SubRng.EntireRow.Font.Color = RGB(0, 0, 0)
    Thomas Kuriakose and dparteka like this.
  7. dparteka

    dparteka Member

    Messages:
    163
    Perfect, does everything I was looking for, thanks for the education, you're the best

Share This Page