VBA Macro stopped working

Question

I have a piece of code which clears selected cells.

Private Sub CommandButton6_Click()
ThisWorkbook.Sheets("MoM-Log").Unprotect Password:="Password"
ThisWorkbook.Sheets("MoM-Template").Unprotect Password:="Password"
ThisWorkbook.Activate
Dim cell As Object
Dim count As Integer
For Each cell In selection
    cell.Clear
    With cell
        .BorderAround ColorIndex:=1, Weight:=xlThin
        .HorizontalAlignment = xlCenter
        If .Column = 3 Or .Column = 6 Then
            .HorizontalAlignment = xlLeft
        End If
         If .Column = 3 Or .Column = 4 Or .Column = 6 Then
            .WrapText = True
        End If
    End With
Next cell
ThisWorkbook.Sheets("MoM-Log").Protect Password:="Password"
ThisWorkbook.Sheets("MoM-Template").Protect Password:="Password"
End Sub

This code was working fine. But it stoppped working once i appended a extra code to it. Which is,

Sub DeletePopUpMenu()
  ' Delete the popup menu if it already exists.
  On Error Resume Next
  Application.CommandBars("MyPopUpMenu").Delete
  On Error GoTo 0
End Sub

Sub CreateDisplayPopUpMenu()
 ' Delete any existing popup menu.
  Windows(ThisWorkbook.Name).Activate
  Sheet1.Select
  Call DeletePopUpMenu

  ' Create the popup menu.
  Call Custom_PopUpMenu_1

 ' Display the popup menu.
  On Error Resume Next
  Application.CommandBars("MyPopUpMenu").ShowPopup
  On Error GoTo 0
End Sub

 Public Sub Custom_PopUpMenu_1()
  Dim MenuItem As CommandBarPopup
  ' Add the popup menu.
  With Application.CommandBars.Add(Name:="MyPopUpMenu", Position:=msoBarPopup, _
     MenuBar:=False, Temporary:=True)

    ' First, add two buttons to the menu.
    With .Controls.Add(Type:=msoControlButton)
        .Caption = "Save As..."
        .FaceId = 71
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
    End With
 End With
End Sub

 Public Function selection(R As Integer, st As String, y As Workbook) As Integer
    count_col = 1
    Do
        x = y.Sheets("Sheet1").Cells(R, count_col)
        If x = st Then ' St is a desired string
            Exit Do
        End If
        count_col = count_col + 1
    Loop While count_col <> 100
    selection = count_col
 End Function

So please help to how can i overcome this. It is very important to be solved soon.

Thank you

Answer 1

Add your selection just above the line For Each cell In selection

For example

Sheets("Sheet1").Range("A1:F17").Select
For Each cell In selection

Answer 2

Yes in debug mode, it gives the following error, Compiler Error: Argument not optional at line, "For Each cell In selection" – user2148238 46 mins ago

You are getting that error because the selection is not a valid Range. What must have been happening that the other code must be moving the selection to something else which is not a range. To ensure that you have a valid range, use TypeName as shown below.

Also Change Dim cell As Object to Dim cell As Range

Private Sub CommandButton6_Click()
    ThisWorkbook.Sheets("MoM-Log").Unprotect Password:="Password"
    ThisWorkbook.Sheets("MoM-Template").Unprotect Password:="Password"

    Dim cell As Range
    Dim count As Integer

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    For Each cell In Selection
        cell.Clear
        With cell
            .BorderAround ColorIndex:=1, Weight:=xlThin
            .HorizontalAlignment = xlCenter

            If .Column = 3 Or .Column = 6 Then .HorizontalAlignment = xlLeft
            If .Column = 3 Or .Column = 4 Or .Column = 6 Then .WrapText = True
        End With
    Next cell

    ThisWorkbook.Sheets("MoM-Log").Protect Password:="Password"
    ThisWorkbook.Sheets("MoM-Template").Protect Password:="Password"
End Sub