Macro not working on friend's computer

LesleyClark

Board Regular
Joined
Feb 15, 2010
Messages
122
Dear All

I sent a Workbook with the below macro (digitally signed) to a friend, who has Excel 2003, which he downloaded.

When he runs the macro button, and hits Yes at the message box point, it then proceeds to perform a "file conversion". Whenever he downloads my spreadsheets to his documents a "file conversion" takes place (he has 2003 and I have 2007). However, this has never occurred during the running of the macro.

Then nothing else happens. It neither saves a file copy as per the coding, nor deletes the values in the Database.

The macro works fine on my system and I have run the compatibility checker.

Can anyone help with this please?

Code:
Sub ArchiveWorkbook()

Dim NewWorkBook As String
Dim CurrentWorkBook As String
Dim TheDate As String
Dim sh As Worksheet
Dim Finalrow As Integer, Finalcolumn As Integer
Dim MyMsg As String
Dim MyTitle As String
Dim Response As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next

TheDate = Format(Date, "d-m-yyyy")
NewWorkBook = ActiveWorkbook.Path & "\" & "FG_Archived_On " & TheDate & ".xlsm"
CurrentWorkBook = "FG_Version_Current.xlsm"
MyTitle = "Year End Procedure"
MyMsg = "This action will clear all values from the Database.  Are you sure you wish to continue?"
Response = MsgBox(MyMsg, vbExclamation + vbYesNo, MyTitle)

Select Case Response
Case Is = vbNo
Exit Sub


Case Is = vbYes

ActiveWorkbook.SaveAs Filename:=NewWorkBook
Workbooks(NewWorkBook).Save
Workbooks(NewWorkBook).Close


Workbooks.Open (CurrentWorkBook)
Worksheets("Database").Open

Set sh = Workbooks(CurrentWorkBook).Worksheets("Database")
Finalrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
Finalcolumn = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column


sh.Cells(2, 2).Resize(Finalrow, Finalcolumn).Delete

Workbooks(CurrentWorkBook).Save


End Select
End Sub

Thanks in advance
Lesley
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
Would your friend have files with just the .xls file extention?
 

LesleyClark

Board Regular
Joined
Feb 15, 2010
Messages
122
Hi Alpha Frog

Thanks for reply.
I don't see how he could use .xls extension, as surely the macro wouldn't run at all. I have sent him lots of spreadsheets with macros in the past (which he downloads) and this has never occurred before.
He does get my message box display, so the macro starts to work.
This is a mystery, as I have just tested on my husband's computer (Excel 2007) and it works fine there. Could it be down to Excel 2003
Any other suggestions?
Lesley
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
Can you have your friend comment out the On Error Resume Next line and then run the macro to see what line of code it errors on?

I'll bet ya a $100 it has something to do with the file extension.
 

LesleyClark

Board Regular
Joined
Feb 15, 2010
Messages
122
Hi Alpha Frog
Thanks for replying again.
I will take up your suggestion and get my friend to do as you suggest.
I must admit the file extension hadn't occurred to me!

I guess I am still only just above novice on a good day!
Thanks for your help
Regards
Lesley
 

LesleyClark

Board Regular
Joined
Feb 15, 2010
Messages
122
Hi Alpha Frog

I just wanted to confirm you were right and so you are not down $100.
I was getting my xls and xlr extensions muddled up. I thought xls was a regular not macro enabled file extension
I am very grateful. Thanks very much
Lesley
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,979
Messages
5,508,535
Members
408,688
Latest member
Bhojraj

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