Macro not working

[dandy]

**CORRECTION** The problems listed below only happens for certain users. Maybe different versions of excel?

I have two macros that are supposed to run upon clicking a CommandButton.

First, there are several cells that either contain "YES" or "NO"
If the cell equals "YES", the corresponding sheet should be active, otherwise it should be hidden.

Second, the other macro will send an email to everybody on the email list.

In order to complete both of these actions, I have the following code. The CommandButton asks the user to verify that they saved the file to a certain folder and if the answer is yes, it's supposed to complete the above two macros.

However, it does not seem to be working for the first part (i.e. the sheets are staying active even when the cell contains "NO").

Please let me know if you need more information.

Private Sub CommandButton1_Click()

    Dim Msg As String, Ans As Variant
    Msg = "Have you saved this file to the NPI working folder?"
    
    Ans = MsgBox(Msg, vbYesNo)
    
    Select Case Ans
        Case vbYes 'If answer is yes then continue
        
            Call Vis
            Call Contact
        Worksheets("Cover").Activate
        
        Case vbNo 'If answer is no, display message
        Dim Msg1 As String
        Msg1 = MsgBox("Save this file to the NPI Working Folder and then continue")
        
        GoTo Quit:
    
    End Select

Quit:

End Sub

 

[JLGWhiz]

There is nothing in the code posted that would make any other sheet the active sheet. What does the other two procedures do? Or what do you want to happen if No is selecdted?
BTW, the GoTo Quit: and the Quit: label are superfluous to the intent of the macro. They can be deleted and the macro will still work as intented.

 

[dandy]

Sorry, here is the code that activates and deactivates the sheets based on "YES" or "NO"

It's really strange. I've tested this a few times in a row (all users are using Excel 2013) and sometimes the tabs are all visible and sometimes they are hidden according to the YES/NO selection.

Public Sub Vis()

Dim vN As Variant, Found As Range
Worksheets("Cover").Activate
If Range("C21") = "Yes" Then
Sheets("Engineering").Visible = True
Else
Sheets("Engineering").Visible = False
End If
If Range("C22") = "Yes" Then
Sheets("Tooling_Fixture").Visible = True
Else
Sheets("Tooling_Fixture").Visible = False
End If
If Range("C23") = "Yes" Then
Sheets("Weld_Braze").Visible = True
Else
Sheets("Weld_Braze").Visible = False
End If
If Range("C24") = "Yes" Then
Sheets("NDT").Visible = True
Else
Sheets("NDT").Visible = False
End If
If Range("C25") = "Yes" Then
Sheets("Heat Treat").Visible = True
Else
Sheets("Heat Treat").Visible = False
End If
If Range("C26") = "Yes" Then
Sheets("Dry_Film_Lube").Visible = True
Else
Sheets("Dry_Film_Lube").Visible = False
End If
If Range("C27") = "Yes" Then
Sheets("Clean_Verification").Visible = True
Else
Sheets("Clean_Verification").Visible = False
End If
If Range("C28") = "Yes" Then
Sheets("Technical_Processes").Visible = True
Else
Sheets("Technical_Processes").Visible = False
End If
If Range("C29") = "Yes" Then
Sheets("Quality").Visible = True
Else
Sheets("Quality").Visible = False
End If
If Range("C30") = "Yes" Then
Sheets("Document_Control").Visible = True
Else
Sheets("Document_Control").Visible = False
End If
If Range("C31") = "Yes" Then
Sheets("Purchasing").Visible = True
Else
Sheets("Purchasing").Visible = False
End If
If Range("C32") = "Yes" Then
Sheets("Planning").Visible = True
Else
Sheets("Planning").Visible = False
End If

End Sub

 

[JLGWhiz]

Your command button code does not determine whether cells C21:C32 contain "Yes" or "No". It simply determines whether you run the 'vis' and 'contact' macros or not. Do not confuse the vbYes and vbNo values of the message box response with your "Yes" and "No" in column C. I do not know what controls the values of C21:C32, (Yes/No) but when vis macro runs, it is reacting to those cell values and not what the user selected in the command button macro. Does this help clear up the problem?

 

[Fluff]

Does the Contact sub save the file?
If not & the user doesn't resave after running the macro, then the visible property of the sheets will be the same as when the macro was run.

 

[JLGWhiz]

Probably the answer is to just put a line of code in the vis macro to save the file and be sure it is saved.

 

[dandy]

The values of C21:C32 are controlled by the user.
I realize that the vbYes and vbNO are independent of the Yes/No in column C but since the command button code is calling the 'Vis' macro, shouldn't it still hide those sheets based on the values in column C?

 

[JLGWhiz]

The values of C21:C32 are controlled by the user.
I realize that the vbYes and vbNO are independent of the Yes/No in column C but since the command button code is calling the 'Vis' macro, shouldn't it still hide those sheets based on the values in column C?

Yes, it should, but if the user selects vbNo then the vis does not run and as Fluff points out, the visible properties remain as last set when vis did run. I am not sure if you want to run vis if the file is not saved. As I said in post #6 , maybe adding a line to save the file before vis is called, or in the vis macro would fix it. From what I can glean in the above posts, you don't necessarily want to run vis if the file is not saved.

 

[dandy]

I think I understand now. Thanks for all your help!

 

[JLGWhiz]

I think I understand now. Thanks for all your help!

Glad we could assist and thanks for the feedback.
Regards, JLG