• 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...

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

Private Sub Worksheet_Activate only works when run sub user form is used.

Frncis

Member
I have the code below that only runs when Run sub user form is used. The really strange thing is that it used to work.
Code:
Private Sub Worksheet_Activate()

Application.Speech.Speak "Please check the date & year of the meeting dates this month,!! correct as necessary", SpeakAsync:=True
         'MsgBox "Please check the date & year of the meeting dates this month," & vbNewLine _
  & vbNewLine & "correct as necessary!", vbInformation, "Vocational Services - OVR    " & ActiveSheet.Name
   Application.Wait (Now + TimeValue("00:00:07"))
End Sub
I have checked everything, I could think of. Could you look at the code& see if I am missing something?
 

Marc L

Excel Ninja
Close Excel - and all the not necessary opened applications even restart the computer - and retry …​
If it remains the same try on a brand new workbook, maybe the current is damaged.​
 

Frncis

Member
I tried your suggestion. I closed all applications, restarted the computer, opened a blank workbook. I had the same results. (Not working). Similar code is working. :confused:
 

Frncis

Member
question #1 the file is similar to another file with a different name. Question #2 the massage is supposed to appear when the sheet is activated. Question #3 yes same result.
Right before your response , i found the solution. I have a sub to call different worksheets. I made anew sub called " Message" & placed the code there. It worked like a charm. Here is the solution:
Code:
Sub October_Meetings()
      Dim MyValue
     Dim i As String
    
    'MsgBox prompt:="Continue to October OVR Meeting List?", Title:="Voc. Rehab. - OVR Entry"
    i = MsgBox("Continue to October OVR Meeting List?", vbYesNo, "Vocational Services - OVR   " & ActiveSheet.Name)
    
    If Not i = vbYes Then Exit Sub
    
    'First message shows in the body of the box, message 2 shows at the top of the box.
    Do
        MyValue = Application.InputBox("Only Click Ok or Cancel after your Selection!!!!!!!" & vbCrLf & _
                               "Enter 1 for October Meeting # 1" & vbCrLf & _
                               "Enter 2 for October Meeting # 2" & vbCrLf & _
                               "Enter 3 for October Meeting # 3" & vbCrLf, "Vocational Services - OVR   " & ActiveSheet.Name)
        ' Sub messaage box exit.
        If MyValue = False Then
            Exit Sub
        ElseIf (MyValue = 1) Or (MyValue = 2) Or (MyValue = 3) Then
            Exit Do
        Else
            MsgBox "You have not made a valid entry.  Please try again.", vbInformation, "Vocational Services - OVR   " & ActiveSheet.Name
        End If
    Loop    'Code to Execute When Condition = value_1
    Select Case MyValue
        Case 1
                     If ActiveSheet.CodeName = "Sheet2" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October Walk In Training Data Entry!", vbInformation, "Vocational Services - OVR   " & ActiveSheet.Name
                    Else
                        Sheets("October_Meeting_#_1").Activate
                        Range("A1").Select
                        Call Message
                    End If
        'Code to Execute When Condition = value_2
        Case 2
                     If ActiveSheet.CodeName = "Sheet3" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October OVR meeting # 2!", vbInformation, "Vocational Services - OVR   " & ActiveSheet.Name
                    Else
                        Sheets("October_Meeting_#_2").Activate
                        Range("A1").Select
                    End If
        
        'Code to Execute When Condition = value_3
        Case 3
                     If ActiveSheet.CodeName = "Sheet4" Then
               ' The message below only shows when you are on the active sheet.
                        MsgBox "You are already on October OVR meeting # 3!", vbInformation, "Vocational Services - OVR   " & ActiveSheet.Name
                    Else
                        Sheets("Extra_October_Meeting").Activate
                        Range("A1").Select
                End If
                
    End Select
End Sub
The funny part is that in the second workbook the application speak is on the worksheet.
 
Top