VBA Excel Help - Filename

[pkruti]

I am trying to do the following: I am using excel and I have a
checkbox and once that box is checked I want it to see if it has been
saved if not then i want the "Save as" method to show but if the file
has already been saved then I want it to just the save the changes and
not show the "Save As" dialog every time the check box is checked


The following is the code i am working: any ideas on what i may be
doing worng???


Sub CheckBox1_Click()
Dim amessage As Outlook.MailItem
Dim test As String
Dim filename As String


If Not ActiveWorkbook.Saved Then
filesavename = Excel.Application.GetSaveAsFil­ename( _
filefilter:="Excel Files (*.xls), *.xls")
ThisWorkbook.SaveAs filename:=filesavename


End If


If ThisWorkbook.Saved = True Then
test = inputbox("Please enter an email address:")
Set outsess = CreateObject("outlook.Applicat­ion")
Set amessage = outsess.CreateItem(olMailItem)
amessage.BodyFormat = olFormatHTML
amessage.To = test
'more code but didnt display it all
End if

 

[Harald Staff]

Hi

There are two kinds of "saved". One is "does it exist on the hard drive" and
one is "has it changed since it was opened". The latter is "save changes"
and that is the Saved property in VBA. Use Dir to see if the file exists at
all.

Sub test()
If Dir(ActiveWorkbook.FullName) = "" Then
MsgBox ActiveWorkbook.FullName & _
" is not saved to disk"
Else
If ActiveWorkbook.Saved = True Then
MsgBox ActiveWorkbook.FullName & _
" is unchanged"
Else
MsgBox ActiveWorkbook.FullName & _
" is changed by the user"
End If
End If
End Sub

Note also that you declared "filename As String" but use "filesavename" in
your code. Put Option Explicit on top of your modules to avoid those things.

HTH. Best wishes Harald


<[email protected]> skrev i melding
I am trying to do the following: I am using excel and I have a
checkbox and once that box is checked I want it to see if it has been
saved if not then i want the "Save as" method to show but if the file
has already been saved then I want it to just the save the changes and
not show the "Save As" dialog every time the check box is checked


The following is the code i am working: any ideas on what i may be
doing worng???


Sub CheckBox1_Click()
Dim amessage As Outlook.MailItem
Dim test As String
Dim filename As String


If Not ActiveWorkbook.Saved Then
filesavename = Excel.Application.GetSaveAsFil­ename( _
filefilter:="Excel Files (*.xls), *.xls")
ThisWorkbook.SaveAs filename:=filesavename


End If


If ThisWorkbook.Saved = True Then
test = inputbox("Please enter an email address:")
Set outsess = CreateObject("outlook.Applicat­ion")
Set amessage = outsess.CreateItem(olMailItem)
amessage.BodyFormat = olFormatHTML
amessage.To = test
'more code but didnt display it all
End if