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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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...
 

Forum statistics

Threads
1,141,587
Messages
5,707,258
Members
421,498
Latest member
matinebi

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