Disappearing macro

[KatPrichett]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have written a macro which disappears when my colleague opens the excel document but it is still there when I reopen the document. When I initially created the document/macro, he had no problem opening and running the macro and then one day it just wasn't there anymore.<o></o>
<o></o>
I have checked his setting and Macro's are enabled - we are both running Office 2007 and the document is saved on a SharePoint site. The macro is very basic and just completes some format changes.<o></o>
<o></o>
Does anyone have any suggestions of what could be wrong please?<o></o>
<o></o>
Thanks<o></o>
Kat

 

[pboltonchina]

Did you save the macro in a module in your Personal workbook, or the actual workbook you run it on? Open the file on your pc and View Macros, select This workbook at the bottom and see if you can still it, if not it's in Personal and you need to move it into a workbook module.

 

[KatPrichett]

Thanks for quick response but I have checked and the macro is saved in the 'This Workbook' - any other ideas??

 

[pboltonchina]

Could you post the code you are using, and does the macro work for you?
Regards
Paul

 

[KatPrichett]

Yes, the macro does work for me. Code below as requested:

Sub Button1_Click()
Application.ScreenUpdating = False
Sheets("Page2").Select
Range("a1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("Page3 PH only").Select
Range("a1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("DATA1").Select
Range("a1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("Page2").Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.ColorIndex = xlAutomatic
.Bold = False
.Italic = False
.Underline = xlUnderlineStyleNone
End With
Columns("A").Select
With Selection
.ColumnWidth = 16
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("B").Select
With Selection
.ColumnWidth = 35
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("C").Select
With Selection
.ColumnWidth = 18
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
End With
Columns("D").Select
With Selection
.ColumnWidth = 30
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
End With
Columns("E").Select
With Selection
.ColumnWidth = 35
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("F").Select
With Selection
.ColumnWidth = 70
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("G").Select
With Selection
.ColumnWidth = 60
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("H").Select
With Selection
.ColumnWidth = 60
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
Columns("I").Select
With Selection
.ColumnWidth = 11
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
End With
Cells.Select
Cells.EntireRow.AutoFit
Rows("1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.RowHeight = 50
End With
ActiveWorkbook.Worksheets("Page2").ListObjects("Table_owssvr_12").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Page2").ListObjects("Table_owssvr_12").Sort. _
SortFields.Add Key:=Range("Table_owssvr_12[Status]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Project Item,Business Support Item,Horizon Item,Completed,Cancelled,Rejected" _
, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Page2").ListObjects("Table_owssvr_12").Sort. _
SortFields.Add Key:=Range("Table_owssvr_12[RAG Status]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:="Green,Amber,Red", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Page2").ListObjects("Table_owssvr_12").Sort. _
SortFields.Add Key:=Range("Table_owssvr_12[Implementation Date]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Page2").ListObjects("Table_owssvr_12").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Page4").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("C10:Q10").Select
Selection.AutoFill Destination:=Range("C10:Q64")
Cells.Select
Cells.EntireRow.AutoFit
Dim i As Integer
Dim RStart As Range
Dim REnd As Range
Set RStart = Range("C10")
Set REnd = Sheets("Page4").Range("C100").End(xlUp).Offset(0, 3)
Range(RStart, REnd).Select
On Error Resume Next
With Selection
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.CountBlank(.Rows(i)) = 4 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
Set RStart = Nothing
Set REnd = Nothing
Range("A1").Select
Sheets("Instructions").Select
Application.ScreenUpdating = True
MsgBox ("Finished!")
End Sub

 

[pboltonchina]

I've just pasted your code into a workbook module and it appears in the macro list for me, it must be something to do with his PC and it's settings. Get him to open the file on his PC and press alt+F11 to open the VBA window and see if it's there.

 

[KatPrichett]

Thanks, he has just finished for the day so will check this first thing tomorrow!

 

[Norie]

Have you checked that this isn't a SharePoint issue/setting?

 

[KatPrichett]

No I haven't checked if it's a SharePoint issue but I wouldn't know where to start looking. Any ideas?

 

[KatPrichett]

Ok, the code I have written is not there when he hits Alt + F11 - there is a module called ModHeadFoot however?

If I try to access using the Macro button on the Developer tab the name of the macro is there to select but if i try to step into it he gets a message advising need to reopen the workbook and enable macros. The option to enable macros is not available when first opening the document. I have double checked in the option and he def has macros enabled.