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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
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,176,447
Messages
5,903,157
Members
435,012
Latest member
Excel for life

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