Excel VBA to go to specific sheet & cell in the current workbook not working.

FrancisM

FrancisM

Joined
Dec 13, 2017
Messages
71
Reaction score
19
I have code that runs when the workbook is activated. However, when I want to choose "yes" to go to that cell, it doesn't go to the cell. It is probably something, very simple that, I am missing. I have tried several things, such as activating the sheet & selecting the cell, & several other things, with no success. :confused: :wall:
Here is the code, it is located on thisworkbook:
Code: 
'Greets user.
Dim sGreet As String
    If Hour(Time) <= 12 Then
        sGreet = "Good morning... it's " & WeekdayName(Weekday(Date)) & ", " & MonthName(Month(Date), False) & " " & Day(Date) & ", " & Year(Date) & " & there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left in the month."
        'MsgBox "there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left."
    Else
        sGreet = "Good afternoon... it's " & WeekdayName(Weekday(Date)) & ", " & MonthName(Month(Date), False) & " " & Day(Date) & ", " & Year(Date) & " & there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left in the month."
    End If
    Application.Speech.Speak sGreet
    Application.Wait (Now + TimeValue("00:00:015"))
    'MsgBox sGreet & ".", vbInformation, "Vocational Services"
   
    'Activates a reminder to change the month for the pop up Calendar.
      MsgBox " If today is the 1st of the month, or close to the first, change the month for the Popup Calendar. Today is  " & Date & " & there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left in the month.", vbInformation, _
                       "Vocational Services Reminder"
       Application.Speech.Speak " Do you want to change the month for the Popup Calendar? "
        Application.Wait (Now + TimeValue("00:00:07"))
       Ans = MsgBox("Do you want to change the month for the Popup Calendar?", vbYesNo, "Vocational Services Reminder")
              If MsgBoxResult = vbNo Then
                 'Sheets(“TOC”).Select

'Range(“A1”).Select

    Exit Sub
    ElseIf MsgBoxResult = vbYes Then
ThisWorkbook.Sheets("TOC").Activate
ActiveSheet.Range("C58").Select

     End If
   

      'Hide sheet tabs
    'ActiveWindow.DisplayWorkbookTabs = False
 
End Sub
One final Question, is it possible to simplify this code?
 
Last edited:
I was not able to get it to work. However I found a better solution. I provided directions , which accomplished the same thing. High Tree I liked you, because, you got me thinking in a different direction. This new direction will work even better than I planned.
Here is the full code.
Code: 
Private Sub Workbook_Open()
 'Greets user.
Dim sGreet As String
    If Hour(Time) <= 12 Then
        sGreet = "Good morning... it's " & WeekdayName(Weekday(Date)) & ", " & MonthName(Month(Date), False) & " " & Day(Date) & ", " & Year(Date) & " & there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days" & vbCrLf & "left in the month." & vbCrLf & " Set the Calendar, around or on the 1st of the month."
        'MsgBox "there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left."
    Else
        sGreet = "Good afternoon... it's " & WeekdayName(Weekday(Date)) & ", " & MonthName(Month(Date), False) & " " & Day(Date) & ", " & Year(Date) & " & there are " & CDbl(Application.EoMonth(Date, 0) - Date) & " days left in the month."
    End If
    Application.Speech.Speak sGreet
    Application.Wait (Now + TimeValue("00:00:01"))
    MsgBox sGreet & vbCrLf & "" & vbCrLf & " If you want to set the Calendar for the new month." & vbCrLf & "" & vbCrLf & "Go to the 1st meeting of the month & scroll down.", vbInformation, "Vocational Services"
   Sheets(“TOC”).Select

Range(“A1”).Select

      'Hide sheet tabs
    ActiveWindow.DisplayWorkbookTabs = False
End Sub
A FYI. I added Hide sheet tabs to Workbook open, Workbook_SheetDeactivate, Workbook_SheetChange, & Worksheet_Activate to prevent the user from using the sheet tabs. This works even if the show tabs is checked.
 
Back
Top