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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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
75,732
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,135
Messages
5,509,364
Members
408,730
Latest member
Kayslover

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top