activating a sheet from workbook open

[Wesj43]

I am once again new to Excel VBA not having had to program it for the last 15 years. Suddenly I find I must return to my former profession and find I have lost a lot of knowledge. (If I ever had it LOL) So I am basically a noob.

I am having an issue with activating a sheet from the workbook open process. Here is my opening code

Private Sub workbook_open()
' MsgBox ("Workbook is Open")
Call MySaveAs
' Find Last row
'use End(xlUp) to determine Last Row with Data, in one column (column A)
'Rows.count returns the last row of the worksheet
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
lastRow = lastRow + 1 ' found last row with data now point to next empty row
' MsgBox ("last Row is ") & lastRow
MsgBox ("Activate Sheet")
' Activate a Sheet
ThisWorkbook.Sheets("Sheet1").Activate
MsgBox ("activated sheet")
' break code here
' Stop
End Sub

Everything works great except the macro on the activated sheet never fires.

Public Sub worksheet_activate()
MsgBox ("got to worksheet")
' MsgBox ("Format row")
Call formatrow
MsgBox ("Format Done")
' break code here
Stop
Call Get_Input
End Sub

I never get the "Got to Worksheet" msg.

I am sure I am forgetting something simple

Any help is much appreciated.

Thanks to all

Wes

 

[Jaafar Tribak]

Is the worksheet_activate() SUB declared in the worksheet module for Sheet1 or did you add the SUB to a standard module by mistake ?

 

[Wesj43]

Is the worksheet_activate() SUB declared in the worksheet module for Sheet1 or did you add the SUB to a standard module by mistake ?

Thanks for the quick reply

Yes it is declared in the Sheet1(sheet1) object

 

[Jaafar Tribak]

Thanks for the quick reply

Yes it is declared in the Sheet1(sheet1) object

Wy is the worksheet_activate() Public ? It should be Private by default.... That looks doggy to me and made me suspect it was in the wrong object module.

Is the MySaveAs routine disabling excel events at any point ?

 

[Wesj43]

No I don't believe it is. Here is the code

Private Sub MySaveAs()
' Save backup
    Dim strFolder As String
    Dim i As Long
    Dim Filename As String
    Dim folderpath As String

'Find the position of the period in the file name
    i = InStr(ActiveWorkbook.Name, ".")
'Create a default file name by concatenating the file name without the extention _
        plus the current date and time, and plus the xlsm extention
    Filename = Left(ActiveWorkbook.Name, i - 1) & "_" & Format(Now, "yyyy-mm-dd_hh_mm") & ".xlsm"
' MsgBox ("File name = ") & Filename
'Open Save As dialog to a default folder with default file name
With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .InitialFileName = "C:\Documents\" & Filename
    .InitialView = msoFileDialogViewDetails
If .Show = -1 Then
    strFolder = .SelectedItems(1)
Else
    Exit Sub
End If
'get selected folder path from FileDialog, but remove filename from FileDialog
        folderpath = Left(strFolder, InStrRev(strFolder, "\"))
' MsgBox ("folderpath = ") & folderpath
'Save this workbook in chosen file path & appropriate filename
        'File format .xlsm
    ThisWorkbook.SaveCopyAs Filename:=folderpath & Filename 'FileFormat:=xlOpenXMLWorkbookMacroEnabled
' MsgBox ("Filename = ") & Filename
End With

End Sub

 

[Wesj43]

Changed the Macro to Private, but no joy. Not sure why I made it Public.