Help with saving ?

TeacherEric

Board Regular
Joined
Mar 21, 2006
Messages
78
I'm having a major problem with my code. I can't seem to figure out what is wrong.

When I run this code:
Code:
If Dir("C:\Gradebooks", vbDirectory) = "" Then
        MkDir "C:\Gradebooks"
        End If
ActiveWorkbook.SaveAs Filename:=Sheets("information").Range("k151"), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

Instead of executing the saveas command, VB jumps to a combobox code from another worksheet then jumps to another, and another, until it errors out. Once I click the RESET button on the VB toolbar, it saves.

If I have "thisworkbook.save" it will save without a problem.

Only when I'm doing a saveas does it start this "code hopping".

Can someone guide me to an answer, or give me an idea what I'm doing wrong?


The code is accessed by clicking an autoshapebox.

By the way, the cell value of
Code:
 Sheets("information").Range("k151")
is:
C:\Gradebooks\Chem1 AY06-07 Gradebook.xls

Thank you,
TeacherEric
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure but

1) is any Worksheet_Calculate() event code on the sheet?
2) Calculation set to xlManula and set back to xlAutomatic before save?
 

TeacherEric

Board Regular
Joined
Mar 21, 2006
Messages
78
Not sure but

1) is any Worksheet_Calculate() event code on the sheet?
2) Calculation set to xlManula and set back to xlAutomatic before save?


No, nothing of the sort.

This is the full code that runs with the autoshape
Code:
Sub SaveAsAutomatic()
Dim SheetName As String
Dim sht As Object
SheetName = Sheets("information").Range("k151").Value



If Sheets("information").Range("k153").Value = "Enter Your Name" Then
GoTo enterdata
ElseIf Sheets("information").Range("k154").Value = "Enter Your Course Name" Then
GoTo enterdata
ElseIf Sheets("information").Range("k155").Value = "Please Enter Your Class" Then
GoTo enterdata
ElseIf Sheets("information").Range("k156").Value = "Enter The School Year" Then
GoTo enterdata
Else
WhereToSaveBox.Show

If Sheets("information").Range("n160").Value = "G:\" Then

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True
ThisWorkbook.SaveAs SheetName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
GoTo theend

ElseIf Sheets("information").Range("n160").Value = "C:\" Then

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True


If Dir("C:\Gradebooks", vbDirectory) = "" Then
        MkDir "C:\Gradebooks"
        End If

Application.Calculation = xlManual
'  Application.CalculateBeforeSave = True

ThisWorkbook.SaveAs SheetName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False


GoTo theend

theend:
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Macros Disabled").Visible = xlSheetVeryHidden
ThisWorkbook.Sheets("Expired").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
GoTo finished

enterdata:
MsgBox "Please enter your information so your file can be saved"
finished:
End If
End If
End Sub


The WhereToSaveBox code is:
Code:
Private Sub savetolaptop_Click()
Sheets("information").Range("N160").Value = "C:\"

Sheets("information").Range("K151").Value = Sheets("information").Range("K149").Value
Sheets("information").Range("O153").Value = Sheets("information").Range("P153").Value
Unload WhereToSaveBox

End Sub

Private Sub savetonetwork_Click()
If LocationBox.Value = "G Drive (Fileserver)" Then
Sheets("information").Range("N160").Value = "G:\"
' This will copy the value from K149 to K151 each time the name is selected.
Sheets("information").Range("K151").Value = Sheets("information").Range("K149").Value
Sheets("information").Range("O153").Value = Sheets("information").Range("P153").Value
Unload WhereToSaveBox

End Sub

Can you see why this would error every time?

When I run a simple Save macro, it saves just fine. Only on the SaveAs does it error.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Do you want to try just add one line

Application.EnableEvents = False on the top

And

Application.EnableEvents = True just before End Sub
 

TeacherEric

Board Regular
Joined
Mar 21, 2006
Messages
78
Do you want to try just add one line

Application.EnableEvents = False on the top

And

Application.EnableEvents = True just before End Sub


That didn't help :(

I think I tried that one before too.

Any other ideas of how to do a SaveAs?

I'm in the process of just rewriting the code.

This is driving me crazy! :cry:

Any suggestions on doing something different would be great.

Thanks again jindon,

TeacherEric
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
not sure if this helps

try add

Unload WhereToSaveBox

before SaveAs...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top