Check Sheet Name In Macro

[t0ny84]

Hi,

I am hoping someone can point me in the right direction, I am trying to edit my below code so that it:
1) Checks to see if the active sheet is in the array.
2) If the active sheet is in the array then continue running macro.
3) If the active sheet isn't in the array then do nothing.
4) If cancel button on message box is pushed then cancel.

Sub ClearAssignedColours()
' Resets calendar cells to white.
Application.ScreenUpdating = False
UserForm1.Show vbModeless
Dim confq As String

shtstounhide = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12")

If ActiveSheet.Name <> worksheets(shtstounhide).name Then
Exit Sub
ElseIf activesheet.name = worksheets(shtstounhide).name then
confq = MsgBox("Are you sure you want to reset the calendar?", vbOKCancel + vbCritical + vbSystemModal, "Confirmation")
If confq = vbCancel Then
Exit Sub
Else
Range("A7:N36").Interior.Color = vbWhite
Range("A37:D42").Interior.Color = vbWhite
End If
End If
End If
Application.ScreenUpdating = True
End Sub

Thanks in advance,
t0ny84

 

[My Aswer Is This]

I'm not sure what your trying to do.
But I always use case statements instead of arrays

Look at this code and see if you can modify it to your needs
Not sure why your script says do you want to reset the calendar.

Sub ClearAssignedColours()
'Modified  10/29/2020  3:47:46 AM  EDT
Select Case ActiveSheet.Name
    Case "Alpha", "Bravo", "Charlie"
    Cells(1, 1).Value = "Yes"
    Case Else
        Cells(1, 1).Value = "No"
End Select

End Sub

 

[dmt32]

Hi

Try

Sub ClearAssignedColours()
    Dim confq       As VbMsgBoxResult
    Dim shtstounhide As Variant
    
    ' Resets calendar cells to white.
    
    UserForm1.Show vbModeless
    
    shtstounhide = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12")
    
    If Not IsError(Application.Match(ActiveSheet.Name, shtstounhide, 0)) Then
        
        confq = MsgBox("Are you sure you want To reset the calendar?", vbOKCancel + vbCritical + vbSystemModal, "Confirmation")
        If confq = vbCancel Then Exit Sub
        
        Range("A7:N36").Interior.Color = vbWhite
        Range("A37:D42").Interior.Color = vbWhite
    End If
    
End Sub

Dave

 

[t0ny84]

Hey My Aswer Is This and dmt32 a big thank you to both of you for your help!

My Aswer Is This - The message box is an extra layer of protection in case a user accidently clicks on the clear button. The macro is going on a calendar spreadsheet where people colour tasks.

dmt32 - your code works perfectly thank you so much.

Thank you both so so much again! Till next time!

t0ny84

 

[dmt32]

you are welcome - glad we were able to help

Dave