activate code upon save command
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 |
activate code upon save command
On May 22, 10:40*am, alex wrote:
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 |
activate code upon save command
On May 22, 11:17*am, JW wrote:
On May 22, 10:40*am, alex wrote: 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- 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? |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com