Help with saving ?

[TeacherEric]

I'm having a major problem with my code. I can't seem to figure out what is wrong.

When I run this 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

 Sheets("information").Range("k151")

is:
C:\Gradebooks\Chem1 AY06-07 Gradebook.xls

Thank you,
TeacherEric

 

[TeacherEric]

anyone have any clues about this?
bump

 

[jindon]

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]

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

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:

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]

Do you want to try just add one line

Application.EnableEvents = False on the top

And

Application.EnableEvents = True just before End Sub

 

[TeacherEric]

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!

Any suggestions on doing something different would be great.

Thanks again jindon,

TeacherEric

 

[jindon]

not sure if this helps

try add

Unload WhereToSaveBox

before SaveAs...