Required selection

[Guest]

Is it possible to make a field (check box) required before saving a document?

 

[Guest]

Yes, here's an example with a checkbox named Checkbox1
Put this code in the ThisWorkbook module


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

If Sheet1.CheckBox1.Value = False Then
Cancel = True
MsgBox "Save action cancelled" & vbCrLf & "You must check the
checkbox before saving"
End If


End Sub

If you don't know where the ThisWorkbook module is just reply to this post
and I'll tell you how to get to it.
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Guest]

Thank you so much Billy. I thought I knew where the module was, but I guess
I don't. Can you tell me? Thank you!

 

[Guest]

Press Alt+F11 and then on the left side of the screen should be the Project
Exlporer that says "Project -VBAProject" at the top. If you don't see it,
click view on the menu and select project explorer. There may be more than
one project. Make sure you select the project with the same name as your
workbook. If you click the plus sign to expand the project (it may already
be expanded) you will see "Sheet1", "Sheet2", "Sheet3", and "This Workbook".
Double Click where it says "This Workbook" and then past the code into the
code window to the right and click the save button.

Make sure actually have a checkbox on the worksheetand that it's named
Checkbox1


Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Guest]

I am not having any luck. It is letting me save the document with a false
value. The code I used:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheet1.CheckBox1.Value = False Then
Cancel = True
MsgBox "Save action cancelled" & vbCrLf & "You must check the checkbox
before saving"
End If

Is that right? Sorry to be a pain!

 

[Guest]

I GOT IT!! Just had to add "end sub" Thank you so much! This will help us
tremendously!

 

[Guest]

I've finally got the error message to appear on the document, but now I can't
save the document without the checkbox marked. How do I get around that???

 

[Gord Dibben]

And if users choose to disable macros when they open the workbook, what then?

A whole 'nother can of worm-like creatures will appear.


Gord Dibben MS Excel MVP

 

[Guest]

Nikki,

What exactly is it you are trying to do? Maybe i can help if I understand
what you are trying to do a little better. Do you want the workbook to open
with the checkbox unchecked? and then require that it be checked before it is
closed?
You could comment out the code to save it but then it won't be active when
you open it.

Why do they need to check this box before saving it? What does the
checkbox signify?



Gord,
"And if users choose to disable macros when they open the workbook, what
then?"

I don't what the point of that comment is. Totally stupid. Can't you say
this about any macro that someone writes? People are here for help and
suggestions. Tell her a better way if you know one. I just offered her the
limited help that I am able to with the small amount of experiece I have.

--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Gord Dibben]

Feedback is always appreciated.

Thanks, Gord

Nikki,

What exactly is it you are trying to do? Maybe i can help if I understand
what you are trying to do a little better. Do you want the workbook to open
with the checkbox unchecked? and then require that it be checked before it is
closed?
You could comment out the code to save it but then it won't be active when
you open it.

Why do they need to check this box before saving it? What does the
checkbox signify?



Gord,
"And if users choose to disable macros when they open the workbook, what
then?"

I don't what the point of that comment is. Totally stupid. Can't you say
this about any macro that someone writes? People are here for help and
suggestions. Tell her a better way if you know one. I just offered her the
limited help that I am able to with the small amount of experiece I have.

Gord Dibben MS Excel MVP