beforesave
 

I am working on a form for our salesmen. I have a series of checkboxes that
are available and when one is checked on or enabled the others are disabled.
What I want to do with two of these when the salesman tries to save the form
is have it check for these two checkboxes to see if one is on/enabled and if
so then check a cell to make sure it has been filled out. If not then you get
a message asking you to fill in the requirement. The code is on ThisWorkbook,
I would appreciate any help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
Attenuated, Super Sound Attenuated, and cell H33. If one is checked on and
H33 is blank then don't allow Save.

If cbSA.Enabled = True And Range("H33").Value = "" Then
Cancel = True
If cbSSA.Enabled = True And Range("H33").Value = "" Then
Canel = True

MsgBox("You have not specified a sound attenuation requirement.") &
("This sheet will not be saved until you furnish this information" vbOKOnly)
If a = vbOKOnly Then Cancel = True

End If
End If
End Sub

beforesave
 

Patrick,
I commented out what I had and copied and pasted your code. Initially I was
getting "Compile Error: Expected: list separator or )". I played with the
parenthesis and got that worked out by removing all of them. Now I am getting
"Run-time Error '424': Object required" and when I pick the debug button it
highlights the "If" line of code. I am also wondering if I don't have to set
the checkboxes.enabled to true or false? Thanks for you help!

"Patrick Molloy" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
' Attenuated, Super Sound Attenuated, and cell H33. If one is checked on and
'H33 is blank then don't allow Save.

If ( cbSA.Enabled OR cbSSA.Enabled ) AND Range("H33").Value = "" Then
Cancel = True
MsgBox "You have not specified a sound attenuation requirement.") & _
("This sheet will not be saved until you furnish this information" vbOKOnly

End If
End Sub

"Doug" wrote in message
...
I am working on a form for our salesmen. I have a series of checkboxes
that
are available and when one is checked on or enabled the others are
disabled.
What I want to do with two of these when the salesman tries to save the
form
is have it check for these two checkboxes to see if one is on/enabled and
if
so then check a cell to make sure it has been filled out. If not then you
get
a message asking you to fill in the requirement. The code is on
ThisWorkbook,
I would appreciate any help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
Attenuated, Super Sound Attenuated, and cell H33. If one is checked on and
H33 is blank then don't allow Save.

If cbSA.Enabled = True And Range("H33").Value = "" Then
Cancel = True
If cbSSA.Enabled = True And Range("H33").Value = "" Then
Canel = True

MsgBox("You have not specified a sound attenuation requirement.") &
("This sheet will not be saved until you furnish this information"
vbOKOnly)
If a = vbOKOnly Then Cancel = True

End If
End If
End Sub

beforesave
 

yes, i stuffed the parenthesis in the msgbox when i got rid of the IF
statement that was there originally. Apologies for that, me bad.

try preceding the cb objects with the sheet name eg
sheet1.cbSA


"Doug" wrote in message
...
Patrick,
I commented out what I had and copied and pasted your code. Initially I
was
getting "Compile Error: Expected: list separator or )". I played with the
parenthesis and got that worked out by removing all of them. Now I am
getting
"Run-time Error '424': Object required" and when I pick the debug button
it
highlights the "If" line of code. I am also wondering if I don't have to
set
the checkboxes.enabled to true or false? Thanks for you help!

"Patrick Molloy" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
' Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
'H33 is blank then don't allow Save.

If ( cbSA.Enabled OR cbSSA.Enabled ) AND Range("H33").Value = ""
Then
Cancel = True
MsgBox "You have not specified a sound attenuation requirement.")
& _
("This sheet will not be saved until you furnish this information"
vbOKOnly

End If
End Sub

"Doug" wrote in message
...
I am working on a form for our salesmen. I have a series of checkboxes
that
are available and when one is checked on or enabled the others are
disabled.
What I want to do with two of these when the salesman tries to save the
form
is have it check for these two checkboxes to see if one is on/enabled
and
if
so then check a cell to make sure it has been filled out. If not then
you
get
a message asking you to fill in the requirement. The code is on
ThisWorkbook,
I would appreciate any help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
H33 is blank then don't allow Save.

If cbSA.Enabled = True And Range("H33").Value = "" Then
Cancel = True
If cbSSA.Enabled = True And Range("H33").Value = "" Then
Canel = True

MsgBox("You have not specified a sound attenuation requirement.") &
("This sheet will not be saved until you furnish this information"
vbOKOnly)
If a = vbOKOnly Then Cancel = True

End If
End If
End Sub

beforesave
 

Patrick, you are the BOM. Thank you so much!

"Patrick Molloy" wrote:

yes, i stuffed the parenthesis in the msgbox when i got rid of the IF
statement that was there originally. Apologies for that, me bad.

try preceding the cb objects with the sheet name eg
sheet1.cbSA


"Doug" wrote in message
...
Patrick,
I commented out what I had and copied and pasted your code. Initially I
was
getting "Compile Error: Expected: list separator or )". I played with the
parenthesis and got that worked out by removing all of them. Now I am
getting
"Run-time Error '424': Object required" and when I pick the debug button
it
highlights the "If" line of code. I am also wondering if I don't have to
set
the checkboxes.enabled to true or false? Thanks for you help!

"Patrick Molloy" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
' Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
'H33 is blank then don't allow Save.

If ( cbSA.Enabled OR cbSSA.Enabled ) AND Range("H33").Value = ""
Then
Cancel = True
MsgBox "You have not specified a sound attenuation requirement.")
& _
("This sheet will not be saved until you furnish this information"
vbOKOnly

End If
End Sub

"Doug" wrote in message
...
I am working on a form for our salesmen. I have a series of checkboxes
that
are available and when one is checked on or enabled the others are
disabled.
What I want to do with two of these when the salesman tries to save the
form
is have it check for these two checkboxes to see if one is on/enabled
and
if
so then check a cell to make sure it has been filled out. If not then
you
get
a message asking you to fill in the requirement. The code is on
ThisWorkbook,
I would appreciate any help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
H33 is blank then don't allow Save.

If cbSA.Enabled = True And Range("H33").Value = "" Then
Cancel = True
If cbSSA.Enabled = True And Range("H33").Value = "" Then
Canel = True

MsgBox("You have not specified a sound attenuation requirement.") &
("This sheet will not be saved until you furnish this information"
vbOKOnly)
If a = vbOKOnly Then Cancel = True

End If
End If
End Sub

beforesave
 

I spoke a little to soon. If neither of the checkboxes are enabled then I
want to be able to save the workbook. I still get the message and I presume
it is not saving. Sorry. I know just enough of this to be dangerous but that
is about it. Thanks!

"Patrick Molloy" wrote:

yes, i stuffed the parenthesis in the msgbox when i got rid of the IF
statement that was there originally. Apologies for that, me bad.

try preceding the cb objects with the sheet name eg
sheet1.cbSA


"Doug" wrote in message
...
Patrick,
I commented out what I had and copied and pasted your code. Initially I
was
getting "Compile Error: Expected: list separator or )". I played with the
parenthesis and got that worked out by removing all of them. Now I am
getting
"Run-time Error '424': Object required" and when I pick the debug button
it
highlights the "If" line of code. I am also wondering if I don't have to
set
the checkboxes.enabled to true or false? Thanks for you help!

"Patrick Molloy" wrote:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
' Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
'H33 is blank then don't allow Save.

If ( cbSA.Enabled OR cbSSA.Enabled ) AND Range("H33").Value = ""
Then
Cancel = True
MsgBox "You have not specified a sound attenuation requirement.")
& _
("This sheet will not be saved until you furnish this information"
vbOKOnly

End If
End Sub

"Doug" wrote in message
...
I am working on a form for our salesmen. I have a series of checkboxes
that
are available and when one is checked on or enabled the others are
disabled.
What I want to do with two of these when the salesman tries to save the
form
is have it check for these two checkboxes to see if one is on/enabled
and
if
so then check a cell to make sure it has been filled out. If not then
you
get
a message asking you to fill in the requirement. The code is on
ThisWorkbook,
I would appreciate any help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'In the "Enclosure" section of the form, looks at checkboxes for Sound
Attenuated, Super Sound Attenuated, and cell H33. If one is checked on
and
H33 is blank then don't allow Save.

If cbSA.Enabled = True And Range("H33").Value = "" Then
Cancel = True
If cbSSA.Enabled = True And Range("H33").Value = "" Then
Canel = True

MsgBox("You have not specified a sound attenuation requirement.") &
("This sheet will not be saved until you furnish this information"
vbOKOnly)
If a = vbOKOnly Then Cancel = True

End If
End If
End Sub