activate code upon save command

[alex]

Hello all,

I have the following code that's used to ask the user if he/she would
like to use a particular naming convention:

Sub xlsSaveAs()

Dim Why As Integer
Dim Answer As String

Why = MsgBox("Use Naming Convention?", vbYesNo, "FILE NAMING")

If Why = vbYes Then
UserForm1.Show
Answer = UserForm1.ComboBox1.Value & "_" &
UserForm1.ComboBox2.Value & "_" & UserForm1.TextBox3.Value & "_" &
UserForm1.TextBox4.Value
Application.Dialogs(xlDialogSaveAs).Show Answer

Else
Application.Dialogs(xlDialogSaveAs).Show

End If
End Sub

I would like this code to execute upon any Save command; e.g., Save |
Save As | etc. Any suggestions on how to do this? Also...

I would like this code to be stored in such a way that it's available/
activated whenever I open Excel. Is that as simple as storing the
code in the Workbook object of Book.xlt?

Any help is appreciated.
alex

 

[JW]

Hello all,

I have the following code that's used to ask the user if he/she would
like to use a particular naming convention:

Sub xlsSaveAs()

Dim Why As Integer
Dim Answer As String

Why = MsgBox("Use Naming Convention?", vbYesNo, "FILE NAMING")

            If Why = vbYes Then
                    UserForm1.Show
                    Answer = UserForm1.ComboBox1.Value & "_" &
UserForm1.ComboBox2.Value & "_" & UserForm1.TextBox3.Value & "_" &
UserForm1.TextBox4.Value
                    Application.Dialogs(xlDialogSaveAs).Show Answer

            Else
                    Application.Dialogs(xlDialogSaveAs).Show

    End If
End Sub

I would like this code to execute upon any Save command; e.g., Save |
Save As |  etc.  Any suggestions on how to do this?  Also...

I would like this code to be stored in such a way that it's available/
activated whenever I open Excel.  Is that as simple as storing the
code in the Workbook object of Book.xlt?

Any help is appreciated.
alex

Are you wanting to do this for just a particular workbook, or for any
workbook? If for a particular workbook, use the BeforeSave event of
the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'your code here
End Sub

If for any workbook, you will have to create an application level
Class Module. Have a look at Chip Pearson's site for info regarding
application events.
http://www.cpearson.com/excel/AppEvent.aspx

 

[alex]

Are you wanting to do this for just a particular workbook, or for any
workbook?  If for a particular workbook, use the BeforeSave event of
the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
   'your code here
End Sub

If for any workbook, you will have to create an application level
Class Module.  Have a look at Chip Pearson's site for info regarding
application events.http://www.cpearson.com/excel/AppEvent.aspx- Hide quoted text -

- Show quoted text -

JW ~ thanks for the help. I'd like to code it for any workbook...I'll
look at Chip's web page.

I cannot, however, even get the code to completely work at the
WorkBook module level.

I have this code:

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

Dim Why As Integer
Dim Answer As String

Why = MsgBox("Use Naming Convention?", vbYesNo, "FILE NAMING")

If Why = vbYes Then
UserForm1.Show
Why.Hide
Answer = UserForm1.ComboBox1.Value & "_" &
UserForm1.ComboBox2.Value & "_" & UserForm1.TextBox3.Value & "_" &
UserForm1.TextBox4.Value
Application.Dialogs(xlDialogSaveAs).Show Answer

Else
Application.Dialogs(xlDialogSaveAs).Show

End If
End Sub

The code works initially, but the message box appears twice, once
before the UserForm (correctly) and once after (incorrectly). It's
like the code wants to run twice! I tried to hide the msgbox after
the UserForm executes, but it didn't work. Any thoughts?