Macro assignment to button changing

[Cotton9]

This is a problem that appeared this morning on my work log workbook. On one but not both of my laptops.

Sub InsertNewWeeklyStatTab_Click()
    ResetControls
	InsertWeeklyTab
End Sub

The macro is on the same 'Menu' sheet as the control button. There are six other control buttons on the sheet. When the sheets load all the buttons macro assignments change to commandbutton1, commandbutton2, ...etc.
If I edit the button to re-assign the correct macro it generates an error: "Ambiguous name detected: InsertNewWeeklyStatTab".

When in developer/Design Mode the correct macro name appears in the 'Name Box' on the formula bar but if I edit the control properties it shows 'commandbuttonxx'


At the same time on a our time sheet log failed on my PC giving an error when attempting to select the 'Menu' from the Workbook_Open() macro.

Private Sub Workbook_Open()
	'--- from ThisWorkBook sheet
    Application.StatusBar = False
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DefaultFilePath = ActiveWorkbook.Path
	' generates -- Run-time error '32809':  Application-defined or object-defined error
    ActiveWorkbook.Worksheets("Menu").Select
    ChDrive (ActiveWorkbook.FullName)
    
    FixStyleDateBackToGen               ' Set workbook 'Normal' Style back to General cell format

End Sub

The workaround for this is:

Dim oMenu as WorkSheet
Set oMenu = ThisWorkBook.WorkSheets("Menu")
oMenu.Activate
Set oMenu = Nothing


Windows 7 Enterprise, Service Pack 1, fully patched and locked down, I do not have an admin account.
32 bit
4GB Ram
McAfee Enterprise v8.8 ant-virus installed/updated
MS Office Excel 2007
240G free disk space

Any ideas?

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[combo]

If you intend to use path and "Menu" sheet from the workbook where the code is, use ThisWorkbook instead of ActiveWorkbook.

combo

 

[Cotton9]

Combo,

Tested the ThisWorkBook on both the timehseet and the worklog workbook to no avail.

any suggestion on the control button problem? I open another workbook this morning with multiple buttons and the all seem to work? This seems to indicate the macro assignment for a control button is workbook specific which might be a macro security option that has gotton changed somewhere but where and how to change back????

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Cotton9]

Not so simple workaround.

Converted contol buttons to Active-X buttons. Not pretty but works.

32809 error workabound.

Every place a worksheet is specified create an object referance first then use the object

Dim oWkBk   As WorkBook
Dim oWkSh   As Worksheet

Set oWkBk = workbooks("test")
...some code....
Set oWkSh = oWkbk.Worksheets("Log Data")

'With Workbooks("test").Worksheets("Log Data")
'  changed to
With oWkSh
    ....some code...
End with

Set oWkSh = Nothing

Not pretty but it is working for us.

Still checking for better solution if someone would share


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future