Can't exit template with required fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm a Newb
I have used code (see below) I found at this site, which requires certain fields to be completed before you are allowed to exit the form. The code works fantastic except...
I can't exit the template and leave the fields blank for the end user to complete. Also what if the end user has changed his mind and just wants to exit
Would appreciate any and all help
Private Sub Workbook_BeforeClose(Cancel As Boolean
Dim myrange As Rang
Set myrange = Worksheets("Sheet1").Range("A1:A6,B10,D12,G1:G3"
If Application.WorksheetFunction.CountA(myrange) <
myrange.Cells.Count The
MsgBox "All Yellow Highlited Fields Must be Completed
Cancel = Tru
End I
End Su
~dawn
 
Hi Dawn
try the following
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
dim ret_msg
Set myrange = Worksheets("Sheet1").Range("A1:A6,B10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
ret_msg = MsgBox "All Yellow Highlited Fields Must be
Completed - Do you want to close nethertheless?"
if ret_value <>6 then
Cancel = True
end if
End If
End Sub
 
Hi
change the code, forgot something..
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
dim ret_msg
Set myrange = Worksheets("Sheet1").Range("A1:A6,B10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
ret_msg = MsgBox ("All Yellow Highlited Fields Must be
Completed - Do you want to close nethertheless?", vbYesNo)
if ret_value <>6 then
Cancel = True
end if
End If
End Sub
 
Hi
one idea:
the lines
ret_msg = MsgBox ("All Yellow Highlited Fields Must be
Completed - Do you want to close nethertheless?", vbYesNo)

are actually only ONE single line
 
Frank
I don't really know how to tell you how much I appreciate your sharing your knowledge with all of us strugglers out here
The code works perfect, but it really needs to go into the "before save". After changing it to "before save", my message still appears("All Yellow Highlited Fields Must be Completed - Do you still wish to close?" ), when you click yes, it allows you to save with the required fields empty.
Help again and many thanks in advance
~daw
 
Hi Dawn
it's probably simply too late for me but I have lost you :-)
What do you ant to achieve: Being able to save if the user clicks 'yes'
or what should happen in this case?

--
Regards
Frank Kabel
Frankfurt, Germany

Dawn said:
Frank,
I don't really know how to tell you how much I appreciate your
Click to expand...
sharing your knowledge with all of us strugglers out here.
The code works perfect, but it really needs to go into the "before
Click to expand...
save". After changing it to "before save", my message still
appears("All Yellow Highlited Fields Must be Completed - Do you still
wish to close?" ), when you click yes, it allows you to save with the
required fields empty.
 
Hi Dawn
try the following code
Private Sub Workbook_Beforesave(Cancel As Boolean)
Dim myrange As Range
dim ret_msg
Set myrange = Worksheets("Sheet1").Range("A1:A6,B10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
ret_msg = MsgBox ("All Yellow Highlited Fields Must be
Completed - Do you want to continue?", vbYesNo)
if ret_value <>6 then
Cancel = True
else
ThisWorkbook.Saved = True
cancel = True
end if
End If
End Sub
 
Perhaps it is getting way to late for me now as well, and thanks so very much for not abandoning me =-)
I tried this code and I get a Compile error " Procedure declaration does not match description of event or procedure having the same name" Because of my Access background I spent several hours looking everywhere for an object/anything that was named the same and so wished this whole thing was in Access, at least I understand it's ambigious error messages and can read between the lines. The only change I made was changing ret_value to ret_msg
~daw

----- Frank Kabel wrote: ----

Hi Daw
try the following cod
Private Sub Workbook_Beforesave(Cancel As Boolean
Dim myrange As Rang
dim ret_ms
Set myrange = Worksheets("Sheet1").Range("A1:A6,B10,D12,G1:G3"
If Application.WorksheetFunction.CountA(myrange) <
myrange.Cells.Count The
ret_msg = MsgBox ("All Yellow Highlited Fields Must b
Completed - Do you want to continue?", vbYesNo
if ret_value <>6 the
Cancel = Tru
els
ThisWorkbook.Saved = Tru
cancel = Tru
end i
End I
End Su

-
Regard
Frank Kabe
Frankfurt, German
 
Hi
sorry my fault. Change the procedure declaration to
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
.....

--
Regards
Frank Kabel
Frankfurt, Germany

Dawn said:
Perhaps it is getting way to late for me now as well, and thanks so
Click to expand...
very much for not abandoning me =-)
I tried this code and I get a Compile error " Procedure declaration
Click to expand...
does not match description of event or procedure having the same name"
Because of my Access background I spent several hours looking
everywhere for an object/anything that was named the same and so wished
this whole thing was in Access, at least I understand it's ambigious
error messages and can read between the lines. The only change I made
was changing ret_value to ret_msg.
 
Frank
Don't you ever sleep =-) It's 9:00 am here and I've been back at work for a couple hours. I work at the Kennedy Space Center in Florida on the ELV program (That's Expendable Launch Vehicles) and your help has enabled our group to work more efficiently, save time, errors and alot of frustrating paperwork
Thank you, Thank you, Thank you, it works exactly right, perfect
I only have (hopefully) one last problem. How do "I" get out of the template leaving the fields blank for the end user to complete
~dawn
 
Hi Dawn
thanks for the feedback :-)
you have encountered a nice problem though. One way: use your username
as condition. e.g. try the following


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim myrange As Range
Dim ret_msg
If Application.UserName <> "Frank Kabel" Then
Set myrange = Worksheets("1").Range("A1:A6,B10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.count Then
ret_msg = MsgBox("All Yellow Highlited Fields Must be
Completed - Do you want to continue?", vbYesNo)
If ret_value <> 6 Then
Cancel = True
Else
ThisWorkbook.Saved = True
Cancel = True
End If
End If
End If
End Sub


Just replace my name with your username. You get this name if you enter
the following in the Immediate window of the VBA editor:
?application.username

Not bullet proof but it should do
 
Frank
Feel as if you are an old friend. Just wanted you to know that everything is working and let you know how very much I appreciate all your time and patience with me.
I have decided to place an acknowledgement on the main splash screens of the Access Program that this Excel spreadsheet is to supply data to, thanking you for your contribution. Actually this was my first collaberation. I'm 56 years old and started programming about 14 years ago. The company I worked for had a Lotus spreadsheet that took 23 hours to complete some necessary calculations and would normally "freeze" at least once halfway through. Knew there had to be a better way and introduced myself to Paradox. It took me about a month but was able to write a stable program that did the calculations in about 7 minutes and haven't stopped using databases since.
Thank you so much My Friend
~daw
ps:( My husbands family came from Frankfurt. Their last name was Janke, which they changed to Yankee when they came to the states
 
Hi
Dawn
thanks for your feedback. Glad to be of some help!
Just post back if you have more questions :-)

P.S.: nice name change by the way....



--
Regards
Frank Kabel
Frankfurt, Germany

Dawn said:
Frank,
Feel as if you are an old friend. Just wanted you to know that
Click to expand...
everything is working and let you know how very much I appreciate all
your time and patience with me.
I have decided to place an acknowledgement on the main splash screens
Click to expand...
of the Access Program that this Excel spreadsheet is to supply data to,
thanking you for your contribution. Actually this was my first
collaberation. I'm 56 years old and started programming about 14 years
ago. The company I worked for had a Lotus spreadsheet that took 23
hours to complete some necessary calculations and would normally
"freeze" at least once halfway through. Knew there had to be a better
way and introduced myself to Paradox. It took me about a month but was
able to write a stable program that did the calculations in about 7
minutes and haven't stopped using databases since.
Thank you so much My Friend,
~dawn
ps:( My husbands family came from Frankfurt. Their last name was
Click to expand...
Janke, which they changed to Yankee when they came to the states)
 
Back
Top