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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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.
 
Upvote 0
Do you want to try just add one line

Application.EnableEvents = False on the top

And

Application.EnableEvents = True just before End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,888
Messages
6,145,017
Members
450,586
Latest member
hehehihi2007

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
Back
Top