Disappearing macro

KatPrichett

New Member
Joined
Jul 26, 2010
Messages
10
Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Does anyone have any suggestions of what could be wrong please?<o:p></o:p>
<o:p></o:p>
Thanks<o:p></o:p>
Kat
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
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

New Member
Joined
Jul 26, 2010
Messages
10
Thanks for quick response but I have checked and the macro is saved in the 'This Workbook' - any other ideas??
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
Could you post the code you are using, and does the macro work for you?
Regards
Paul
 

KatPrichett

New Member
Joined
Jul 26, 2010
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
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

New Member
Joined
Jul 26, 2010
Messages
10

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
Have you checked that this isn't a SharePoint issue/setting?
 

KatPrichett

New Member
Joined
Jul 26, 2010
Messages
10
No I haven't checked if it's a SharePoint issue but I wouldn't know where to start looking. Any ideas?
 

KatPrichett

New Member
Joined
Jul 26, 2010
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,719
Messages
5,660,488
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top