Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Thanks to some generous help from users of this site, I, with no Visual Basic
knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Why not just have an input box that will populate the cell?
If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
If you put this code in the ThisWorkbook code module, it will clear the E59
cell for each user as the workbook opens. Private Sub Workbook_Open() Range("E59").ClearContents End Sub Open the VB editor and right click the ThisWorkbook listed in the project window. Click View Code and paste the above snippet into the code window. Then you can enter something in E59 to get it to save the file and when the workbook opens it will automatically clear that cell. If I recall your original post correctly, you only want the name there for print out which occurs after the save event. If I am correct then this addition should allow you to enter something in E59 to save your prototype file, which will be removed upon the next opening of the file. "Patrick Riley" wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Before you (as the developer, not the user) save the file, you can turn off
events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
JLGWhiz:
Thanks for your response. I tried your code, and got an error msg: Run-time error '1004': Cannot change part of a merged cell. E59 is a merged cell, consisting of cells originally E59 thru O59 inclusive. When I hit the Debug button within the err-msg window, the VB editor window opened, and line 2 of your code was highlighted. Also, it is my intent that the user name remain in the saved file, in addition to appearing on the hardcopy. "JLGWhiz" wrote: If you put this code in the ThisWorkbook code module, it will clear the E59 cell for each user as the workbook opens. Private Sub Workbook_Open() Range("E59").ClearContents End Sub Open the VB editor and right click the ThisWorkbook listed in the project window. Click View Code and paste the above snippet into the code window. Then you can enter something in E59 to get it to save the file and when the workbook opens it will automatically clear that cell. If I recall your original post correctly, you only want the name there for print out which occurs after the save event. If I am correct then this addition should allow you to enter something in E59 to save your prototype file, which will be removed upon the next opening of the file. "Patrick Riley" wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Paul:
Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Just to be sure, you are using:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Excellent suggestions for the OP (especially turning off events).
One additional one- extension of JLGWhiz's response; rather than always deleting the contents of the target cell during the workbook open event, check it for a specific string (such as "Patrick Riley" or "Erase Me") and only clear that cell during the workbook open event if the target cell contains your specified string. Best, Keith "Dave Peterson" wrote in message ... Before you (as the developer, not the user) save the file, you can turn off events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Just to be sure, I re-copied the code from this post. I made sure that E59
was blank (I clicked in the cell and hit the Delete key). When I tried to close the file, I got a syntax error. In the VB editor, Lines 1 and 2 (beginning w "Private" and "Cancel") were highlighted in yellow, and line 5 (beginning w "Range") was selected. Incidentally, when I first pasted the code into the right-hand pane (after selecting "ThisWorkbook" in left pane), lines 5 thru 7 appeared in red. Since I am not a VB programmer, I don't know the significance, if any, of the points I raise in this paragraph. I don't know how to turn off events, although now a later response to my post, from Dave Peterson, provides code to do so, coupled with a coding suggestion. I appreciate your taking the time to help! ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Dave:
I opened VBE, hit Ctrl-G, the Immediate Window opened, I cut and pasted the "application.enableevents = false" code (without the quote marks) into Immediate Window. When I tried to save the file, however, the beforesave code fired, producing the warning message to type in the name. Incidentally, the code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "Cell E59 of sheet 'Main' must be filled in before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Also, re your suggestion to check the username, under ToolsOptionsGeneral, my username is pjriley, so I presumed I should use "pjriley" instead of "Patrick Riley" in the code that you provided. I tried it, and IT LET ME SAVE THE FILE with the BLANK name field!! BUT...when I e-mailed the file to another user and tested it at his machine under his login and application name (making sure the VB code was still there and unchanged), it did NOT prompt me to fill in the name field. Under ToolsOptionsGeneral, his username was "acutti", as it should have been. What do you make of this? ---Pat "Dave Peterson" wrote: Before you (as the developer, not the user) save the file, you can turn off events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
When copying the code from the post, be careful of text wrapping.
When you see an underscore ( _ ) following a space in code, this usually indicates that the code is continued to the next line. Let's see if this causes a problem. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = _ InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub -- "Patrick Riley" wrote in message ... Just to be sure, I re-copied the code from this post. I made sure that E59 was blank (I clicked in the cell and hit the Delete key). When I tried to close the file, I got a syntax error. In the VB editor, Lines 1 and 2 (beginning w "Private" and "Cancel") were highlighted in yellow, and line 5 (beginning w "Range") was selected. Incidentally, when I first pasted the code into the right-hand pane (after selecting "ThisWorkbook" in left pane), lines 5 thru 7 appeared in red. Since I am not a VB programmer, I don't know the significance, if any, of the points I raise in this paragraph. I don't know how to turn off events, although now a later response to my post, from Dave Peterson, provides code to do so, coupled with a coding suggestion. I appreciate your taking the time to help! ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
After you type that "application.enableevents = false" into the immediate
window, you have to hit enter to run that command. If the event fires, then you didn't do it correctly. As for the second question, I'd guess that you did something wrong in the code. Patrick Riley wrote: Dave: I opened VBE, hit Ctrl-G, the Immediate Window opened, I cut and pasted the "application.enableevents = false" code (without the quote marks) into Immediate Window. When I tried to save the file, however, the beforesave code fired, producing the warning message to type in the name. Incidentally, the code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "Cell E59 of sheet 'Main' must be filled in before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Also, re your suggestion to check the username, under ToolsOptionsGeneral, my username is pjriley, so I presumed I should use "pjriley" instead of "Patrick Riley" in the code that you provided. I tried it, and IT LET ME SAVE THE FILE with the BLANK name field!! BUT...when I e-mailed the file to another user and tested it at his machine under his login and application name (making sure the VB code was still there and unchanged), it did NOT prompt me to fill in the name field. Under ToolsOptionsGeneral, his username was "acutti", as it should have been. What do you make of this? ---Pat "Dave Peterson" wrote: Before you (as the developer, not the user) save the file, you can turn off events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Paul:
The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Ok,
Let's try this. Be careful of wrap-around. With this code, you can close the sheet leaving E59 blank by typing a key word. In this case, the key word is "Admin". Of course you can change that. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Do While IsEmpty(Sheets("Main").Range("E59").Value) Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") If Range("E59").Value = "Admin" _ Then Range("E59").ClearContents Exit Sub Else End If Loop End Sub -- "Patrick Riley" wrote in message ... Paul: The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
In VB Editor, I widened the right-hand code pane so that the code did not
wrap, that it had exactly the same line arrangement that I received. I chose to keep "Admin" (with one cap and four lower-case) as the key word. I made sure that cell E59 was empty. When I ran the macro, I got an error: Run Time error '1004' Cannot change part of a merged cell The result was: cell E59 contained "Admin" (that is, E59 did not get emptied out). Cell E59 is a merger of cells that, during the development of the file, were originally cells E59 thru O59 inclusive. Can't understand why Excel is upset with a merged cell. ---Pat "PCLIVE" wrote: Ok, Let's try this. Be careful of wrap-around. With this code, you can close the sheet leaving E59 blank by typing a key word. In this case, the key word is "Admin". Of course you can change that. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Do While IsEmpty(Sheets("Main").Range("E59").Value) Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") If Range("E59").Value = "Admin" _ Then Range("E59").ClearContents Exit Sub Else End If Loop End Sub -- "Patrick Riley" wrote in message ... Paul: The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Ok. Just expand the range in the line that has ClearContents.
Range("E59:O59").ClearContents That should do it. -- "Patrick Riley" wrote in message ... In VB Editor, I widened the right-hand code pane so that the code did not wrap, that it had exactly the same line arrangement that I received. I chose to keep "Admin" (with one cap and four lower-case) as the key word. I made sure that cell E59 was empty. When I ran the macro, I got an error: Run Time error '1004' Cannot change part of a merged cell The result was: cell E59 contained "Admin" (that is, E59 did not get emptied out). Cell E59 is a merger of cells that, during the development of the file, were originally cells E59 thru O59 inclusive. Can't understand why Excel is upset with a merged cell. ---Pat "PCLIVE" wrote: Ok, Let's try this. Be careful of wrap-around. With this code, you can close the sheet leaving E59 blank by typing a key word. In this case, the key word is "Admin". Of course you can change that. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Do While IsEmpty(Sheets("Main").Range("E59").Value) Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") If Range("E59").Value = "Admin" _ Then Range("E59").ClearContents Exit Sub Else End If Loop End Sub -- "Patrick Riley" wrote in message ... Paul: The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
Paul:
YES! It worked!! Thanks so much for your patience throughout the 5 responses that you gave me. You were generous with your time, and I learned a lot. ---Pat -------------------- "PCLIVE" wrote: Ok. Just expand the range in the line that has ClearContents. Range("E59:O59").ClearContents That should do it. -- "Patrick Riley" wrote in message ... In VB Editor, I widened the right-hand code pane so that the code did not wrap, that it had exactly the same line arrangement that I received. I chose to keep "Admin" (with one cap and four lower-case) as the key word. I made sure that cell E59 was empty. When I ran the macro, I got an error: Run Time error '1004' Cannot change part of a merged cell The result was: cell E59 contained "Admin" (that is, E59 did not get emptied out). Cell E59 is a merger of cells that, during the development of the file, were originally cells E59 thru O59 inclusive. Can't understand why Excel is upset with a merged cell. ---Pat "PCLIVE" wrote: Ok, Let's try this. Be careful of wrap-around. With this code, you can close the sheet leaving E59 blank by typing a key word. In this case, the key word is "Admin". Of course you can change that. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Do While IsEmpty(Sheets("Main").Range("E59").Value) Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") If Range("E59").Value = "Admin" _ Then Range("E59").ClearContents Exit Sub Else End If Loop End Sub -- "Patrick Riley" wrote in message ... Paul: The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file
No Problem.
Thanks for the feedback. Paul -- "Patrick Riley" wrote in message ... Paul: YES! It worked!! Thanks so much for your patience throughout the 5 responses that you gave me. You were generous with your time, and I learned a lot. ---Pat -------------------- "PCLIVE" wrote: Ok. Just expand the range in the line that has ClearContents. Range("E59:O59").ClearContents That should do it. -- "Patrick Riley" wrote in message ... In VB Editor, I widened the right-hand code pane so that the code did not wrap, that it had exactly the same line arrangement that I received. I chose to keep "Admin" (with one cap and four lower-case) as the key word. I made sure that cell E59 was empty. When I ran the macro, I got an error: Run Time error '1004' Cannot change part of a merged cell The result was: cell E59 contained "Admin" (that is, E59 did not get emptied out). Cell E59 is a merger of cells that, during the development of the file, were originally cells E59 thru O59 inclusive. Can't understand why Excel is upset with a merged cell. ---Pat "PCLIVE" wrote: Ok, Let's try this. Be careful of wrap-around. With this code, you can close the sheet leaving E59 blank by typing a key word. In this case, the key word is "Admin". Of course you can change that. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Do While IsEmpty(Sheets("Main").Range("E59").Value) Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") If Range("E59").Value = "Admin" _ Then Range("E59").ClearContents Exit Sub Else End If Loop End Sub -- "Patrick Riley" wrote in message ... Paul: The Input-Box code worked like a charm, with one blip: When I click "Cancel" in the Input Box, the Input Box closes (which is expected, and OK), but then a Save-File window opens, allowing me to save the file without filling in the name field. (I made sure that the name field truly was blank.) Does this phenomenon occur when you run the code? ---Pat "PCLIVE" wrote: Just to be sure, you are using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If End Sub Are you sure E59 is currently empty. The code works when I attempt saving. But if there is a space or anything else in E59, no input box is displayed. Also, I saw in another post where you were to disable events. Make sure they are not currently disabled. Regards. -- "Patrick Riley" wrote in message ... Paul: Thanks for taking the time to respond. I deleted existing VB code, and used your code. After inserting your code, nothing happened: I could save file with name-field blank. I did not see an input-box appear. I saved file and re-opened, and got the same results: no input box, could still save file with name-field blank. "PCLIVE" wrote: Why not just have an input box that will populate the cell? If IsEmpty(Sheets("Main").Range("E59").Value) _ Then Range("E59").Value = InputBox("You must type in your name before " & _ "this file can be saved.", "Enter Name.") Else End If HTH, Paul -- "Patrick Riley" wrote in message ... Thanks to some generous help from users of this site, I, with no Visual Basic knowledge, am using some VB code that requires the user to type his/her name into a given cell before the file can be saved, since the name must appear on the printed version. The user-name field is intentionally left blank. Upon attempting to save, the code presents a message to type in the name, if the name-field is still blank. Well, CATCH-22. This very code is preventing me from leaving the name field blank when I try to save the file in final form, ready for enduser use. The code I am using is: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) If IsEmpty(Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If End Sub Any suggestions? ---Pat Riley |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file - Dave Peterson
Dave,
Your are a the MASTER! Thank you. I have been pulling my hair out for the last 6 hours because no one on here really either understood the initial question or just had no clue. The bottom line was, the code needed to be saved and that could not be done because the code was running. Thank you for providing the "immediate window" and "application.enableevents = false" command thus allowing me to save the file blank. Jason Dave Peterson wrote: CATCH-22 won't let me save the file 26-Mar-08 Before you (as the developer, not the user) save the file, you can turn off events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: -- Dave Peterson EggHeadCafe - Software Developer Portal of Choice ASP.NET Session State FAQs http://www.eggheadcafe.com/tutorials...state-faq.aspx |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
CATCH-22 won't let me save the file - Dave Peterson
And google is nice, too!!
Jason, Frederick wrote: Dave, Your are a the MASTER! Thank you. I have been pulling my hair out for the last 6 hours because no one on here really either understood the initial question or just had no clue. The bottom line was, the code needed to be saved and that could not be done because the code was running. Thank you for providing the "immediate window" and "application.enableevents = false" command thus allowing me to save the file blank. Jason Dave Peterson wrote: CATCH-22 won't let me save the file 26-Mar-08 Before you (as the developer, not the user) save the file, you can turn off events. Open the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then save your workbook--the _beforesave event won't fire. Then back to the VBE and toggle the setting in the immediate window: application.enableevents = true ==== You could also do other stuff, too. Maybe check the username? if lcase(application.username) = lcase("Patrick Riley") then 'do nothing, let it save... 'or clear that cell! me.sheets("Main").range("e59").value = "" else If IsEmpty(me.Sheets("Main").Range("E59").Value) Then MsgBox "You must type in your name before " & _ "this file can be saved.", 16, "ERROR" Cancel = True End If end if Patrick Riley wrote: -- Dave Peterson EggHeadCafe - Software Developer Portal of Choice ASP.NET Session State FAQs http://www.eggheadcafe.com/tutorials...state-faq.aspx -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to catch data from a EXCEL file | Excel Discussion (Misc queries) | |||
Excel Interop Catch Event for Saving a file as xls and as xml? | Excel Programming | |||
Excel marcos firing on file save as but not file save | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming | |||
Catch FileSave/Save As/etc... | Excel Programming |