Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Rng As Range
Dim ws As Worksheet

For Each ws In Worksheets
Set Rng = ws.Range("A1")

If ws.Range > 0 Then
Cancel = True
lReply = MsgBox("Please correct...", vbOKOnly)

End If
End Sub

Something however is not OK and it gives errors. Furthermore, the disabling of close command is not covered.

Please help me update it.

Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Dim Rng As Range
Dim ws As Worksheet
 
For Each ws In Worksheets
Set Rng = ws.Range("A1")
 
If ws.Range > 0 Then
Cancel = True
lReply = MsgBox("Please correct...", vbOKOnly)
 
End If
End Sub

Something however is not OK and it gives errors. Furthermore, the disabling of close command is not covered.

Please help me update it.

Hi There,

I have a pretty extensive excel worksheet and I have set up buttons to view the worksheet in different ways. Hiding rows or columns or both in a few different ways. I have set up buttons with names and individual macros to display in multiple ways.

The problem I am having is that when I save as, and change the file name, all of the macros disable.

I want people to be able to name it what ever they want and have the macro's continue to work. I have attached the file.

If someone could please let me know how to fix this, that would be great.

Candace

I have Office Excel 2003 installed on my XP 2002 SP2 machine. (It's been
installed for probably *at least* 2 years now.)

Some time...I'd say in the last 1-2 months, although I cannot nail down the
time-frame with 100% confidence...the 'Save As' feature (in Excel) stopped
working. So, if I open an existing XLS spreadsheet or an existing
tab-delimited text file within Excel, and do nothing but immediately attempt
to 'Save As', Excel basically becomes useless -- I have to terminate Excel
using Task Manager!

Any thoughts as to why this is happening?
I've made sure to do 'Windows Updates' and also the more specific 'Microsoft
Office' updates....but still 'Save As' renders Excel useless.

I have definitely used this feature successfully up until some time fairly
recently...again, I think in the last 1-2 months.

thanks in advance for your help!
later,
Ben

Heres what im trying to do. And here at the bottom is the text from an old post that might help whomever decides to help me out!


i have a master workbook. it gets used daily as a template and saved as another file name. the file name is generated by 5 cells that each contain different data (date, street, town, state). is it possible when i hit the save or save as button/icon, for a file name to be automatically generated from the cells i have data in??

thanx for any help everyone! as always this board is beyond a lifesaver.





PostPosted: Wed Oct 15, 2003 5:12 pm Post subject: change default saveas name for new file w/o saving
Okay, so you want to create a workbook via code, prompt the user for a SaveAs name, then save this workbook?

If this is correct, then here is a structure that should work for you...

Sub CreateAndSave()
Dim wb As Workbook
Dim SaveFileName As Variant

Set wb = Workbooks.Add

ChDrive "C"
ChDir "C:\VBA Code"

SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _
"Microsoft Excel Workbook (*.xls),*.xls")

On Error Resume Next
If SaveFileName False Then wb.SaveAs FileName:=SaveFileName

If Err.Number 0 Then _
MsgBox "There was an error saving the file."

Set wb = Nothing
End Sub
DRJ
PostPosted: Wed Oct 15, 2003 5:06 pm Post subject: change default saveas name for new file w/o saving
That would work fine for thisworkbook, but what about a new workbook.

some macros will add a new workbook and these new workbooks are the ones that I want to mess with. And since the before save is in this workbook it wouldnt trigger with another workbook saving. Maybe with a class module, but this is a minor issue, and I dont need a complicated solution. I was just hoping there was a simple solution like:

Workbooks.add
activeworkbook.saveasname = "New Name"
TommyGun
PostPosted: Wed Oct 15, 2003 4:41 pm Post subject: change default saveas name for new file w/o saving
Okay, so you basically want to overwrite the SaveAs command in XL? To do this you will need to code the BeforeSave event. The following should do what you need...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Dim SaveFileName As Variant

Application.EnableEvents = False

Cancel = True

ChDrive "C"
ChDir "C:\VBA Code"

SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _
"Microsoft Excel Workbook (*.xls),*.xls")

If SaveFileName False Then
Application.DisplayAlerts = False

If Dir(SaveFileName) = vbNullString Then
Me.SaveAs FileName:=SaveFileName
Else
Dim ans As Variant

ans = MsgBox("A file named '" & SaveFileName & "' already exists in this location." & _
" Do you want to overwrite this file?", vbYesNoCancel + vbInformation)

If ans = vbYes Then Me.SaveAs FileName:=SaveFileName
End If

Application.DisplayAlerts = True
End If

Application.EnableEvents = True
End If
End Sub

Warning NOTE: This must be put into the ThisWorkbook module
DRJ
PostPosted: Wed Oct 15, 2003 3:28 pm Post subject: change default saveas name for new file w/o saving
I dont know if this is possible, but here is what I am looking for. When a new workbook is made lets say excel names it book10 when you press save that name "book10" is the default name. Is there a way to rename a new workbook when it is made with out actually saving it?

What I have is a program that creates several workbooks. I can save them automatically with whatever name I want no problem, but I want to let the user be able to select the name. If I dont save them the default names are just book1, book2, book3 etc.

I can suggest a path fine with the chdir command, but can a set a default name as well.

thanks

Jacob

Hi there

i have a template that many users will use, i would like to create a button where when a user clicks it a copy of the template is saved, that bit is straight forward

however the tricky bit is i would like the file name to be predetermined

so for example i have a couple of cells titled

CSR
TL
Score

Ideally a user would click a "save button" and what would happen is a save file as window would open with a pre generated file name dependant on what is entered into the above cells

e.g: (csr)mike_(TL)john_(todays date )15.12.2010_ (score )93

therefore file name would look like this

mike_john_15.12.2010_93

I am looking for code that will prompt a user to save a copy of a worksheet that a user form populates when closing out of a particular workbook.

I also am looking for an exit Macro that completely closes the workbook and triggers the prompt for save as.

here is my Accept Button Click code that causes the population of my worksheet:

Code:

 
Private Sub CMNDACCEPT_Click()
Dim RowCount As Long
Dim ctl As Control
    If Me.TXTUSRNM.Value = "" Then
        MsgBox "Please Enter Your First and Last Name.", vbExclamation, "Entry Tracker"
        Me.TXTUSRNM.SetFocus
        Exit Sub
    End If
    If Me.TXTREPMNTH.Value = "" Then
        MsgBox "Please Enter Reporting Month.", vbExclamation, "Entry Tracker"
        Me.TXTREPMNTH.SetFocus
        Exit Sub
    End If
    If Me.TXTREPYR.Value = "" Then
        MsgBox "Please Enter Reporting Year.", vbExclamation, "Entry Tracker"
        Me.TXTREPYR.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(Me.TXTREPYR.Value) Then
        MsgBox "THIS ENTRY MUST BE A FOUR DIGIT YEAR!", vbExclamation, "Entry Tracker"
        Me.TXTREPYR.SetFocus
        Exit Sub
    End If
    If Me.CBXSELSBU.Value = "" Then
        MsgBox "Please Select Appropriate Business Unit.", vbExclamation, "Entry Tracker"
        Me.CBXSELSBU.SetFocus
        Exit Sub
    End If
    If Me.CBXSELDIST.Value = "" Then
        MsgBox "Please Select Appropriate Distributorship.", vbExclamation, "Entry Tracker"
        Me.CBXSELDIST.SetFocus
        Exit Sub
    End If
    If Me.TXTSTADONE.Value = "" Then
        MsgBox "Please Enter Distributorships Address.", vbExclamation, "Entry Tracker"
        Me.TXTSTADONE.SetFocus
        Exit Sub
    End If
    If Me.TXTCITY.Value = "" Then
        MsgBox "Please Enter Distributorships City.", vbExclamation, "Entry Tracker"
        Me.TXTCITY.SetFocus
        Exit Sub
    End If
    If Me.CBXSTATE.Value = "" Then
        MsgBox "Please Select Distributorships State.", vbExclamation, "Entry Tracker"
        Me.CBXSTATE.SetFocus
        Exit Sub
    End If
    If Me.TXTZIP.Value = "" Then
        MsgBox "Please Enter Distributorships Zip Code.", vbExclamation, "Entry Tracker"
        Me.TXTZIP.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(Me.TXTZIP.Value) Then
        MsgBox "THIS ENTRY MUST BE A FIVE DIGIT ZIP CODE!", vbExclamation, "Entry Tracker"
        Me.TXTZIP.SetFocus
        Exit Sub
    End If
    If Me.TXTSDT.Value = "" Then
        MsgBox "Please Enter Date Spend Occured.", vbExclamation, "Entry Tracker"
        Me.TXTSDT.SetFocus
        Exit Sub
    End If
    If Not IsDate(Me.TXTSDT.Value) Then
        MsgBox "This Box Must Contain a Date in the Following Format: mm/dd/yyyy.", vbExclamation, "Entry Tracker"
        Me.TXTSDT.SetFocus
        Exit Sub
    End If
    If Me.CBXRSN.Value = "" Then
        MsgBox "Please Select Reason for Spend.", vbExclamation, "Entry Tracker"
        Me.CBXRSN.SetFocus
        Exit Sub
    End If
    If Me.TXTREPINV.Value = "" Then
        MsgBox "Please Enter Rep Involved.", vbExclamation, "Entry Tracker"
        Me.TXTREPINV.SetFocus
        Exit Sub
    End If
    If Me.TXTHCP.Value = "" Then
        MsgBox "Please Enter HCP Involved.", vbExclamation, "Entry Tracker"
        Me.TXTHCP.SetFocus
        Exit Sub
    End If
    If Me.TXTHCPCTY.Value = "" Then
        MsgBox "Please Enter HCP City.", vbExclamation, "Entry Tracker"
        Me.TXTHCPCTY.SetFocus
        Exit Sub
    End If
    If Me.CBXHCPST.Value = "" Then
        MsgBox "Please Select HCP City.", vbExclamation, "Entry Tracker"
        Me.CBXHCPST.SetFocus
        Exit Sub
    End If
    If Me.TXTAFF.Value = "" Then
        MsgBox "Please Enter HCP's Affiliated Hospital or Private Practice Group.", vbExclamation, "Entry Tracker"
        Me.TXTAFF.SetFocus
        Exit Sub
    End If
    If Me.CBXSPTP.Value = "" Then
        MsgBox "Please Select Type of Spend.", vbExclamation, "Entry Tracker"
        Me.CBXSPTP.SetFocus
        Exit Sub
    End If
    If Me.TXTDOLL.Value = "" Then
        MsgBox "Please Enter Amount of Spend.", vbExclamation, "Entry Tracker"
        Me.TXTDOLL.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(Me.TXTDOLL.Value) Then
        MsgBox "THIS ENTRY MUST BE A NUMERIC ENTRY", vbExclamation, "Entry Tracker"
        Me.TXTDOLL.SetFocus
        Exit Sub
    End If
    RowCount = Worksheets("Report").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Report").Range("A1")
        .Offset(RowCount, 0).Value = Me.TXTUSRNM.Value
        .Offset(RowCount, 1).Value = Me.TXTREPMNTH.Value
        .Offset(RowCount, 2).Value = Me.TXTREPYR.Value
        .Offset(RowCount, 3).Value = Me.CBXSELSBU.Value
        .Offset(RowCount, 4).Value = Me.CBXSELDIST.Value
        .Offset(RowCount, 5).Value = Me.TXTSTADONE.Value
        .Offset(RowCount, 6).Value = Me.TXTSTADTWO.Value
        .Offset(RowCount, 7).Value = Me.TXTCITY.Value
        .Offset(RowCount, 8).Value = Me.CBXSTATE.Value
        .Offset(RowCount, 9).Value = Me.TXTZIP.Value
        .Offset(RowCount, 10).Value = Me.TXTSDT.Value
        .Offset(RowCount, 11).Value = Me.CBXRSN.Value
        .Offset(RowCount, 12).Value = Me.TXTREPINV.Value
        .Offset(RowCount, 13).Value = Me.TXTHCP.Value
        .Offset(RowCount, 14).Value = Me.TXTHCPCTY.Value
        .Offset(RowCount, 15).Value = Me.CBXHCPST.Value
        .Offset(RowCount, 16).Value = Me.TXTAFF.Value
        .Offset(RowCount, 17).Value = Me.CBXSPTP.Value
        .Offset(RowCount, 18).Value = Me.TXTDOLL.Value
        .Offset(RowCount, 19).Value = Me.TXTOTHEXP1.Value
        .Offset(RowCount, 20).Value = Me.TXTOTHEXP2.Value
        .Offset(RowCount, 21).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
    End With
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub
Private Sub CMNDCLEAR_Click()
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl
End Sub

I hope someone can help me here.

Hello Everyone,

I would like to disable the 'Save' feature in the Menu Bar, disable just the 'Save' icon in the Toolbar (not sure if this is possible, but if I've learned anything about Excel, it must be possible) and disable the 'Ctrl & S' feature of the keyboard. I still want to have the 'Save As...' feature enabled, so users can save their work, but not overwrite the template I've created.

I've located the code for the Menu Bar, but I cannot find code to disable the remaining two. Would anyone happen to know how to accomplish this?

So at this point, this is what I have:

Private Sub Workbook_Open()
CommandBars("File").Controls.Item("Save").Enabled = False
End Sub

Thanks for your time!

TJ

I am attempting to save a copy of my workbook.
I am getting a warning dialog box stating the following:
Quote:

Save 'Master.xls' with references to unsaved documents?

Any idea of what this means or what is occurring?

thanks

I do a lot of exporting to Excel, but most of the time I don't want to save the export steps, but it prompts me to save everytime. How can I turn this feature off?

Steve