Required Field only if another cell is completed and auto date

[Guest]

Hi,
Thanks for all your help so far. I have been able to use the "On Close" for some of my required fields gather from your postings.
I work almost exclusively in Access, and the Excel Template Wizard was my only solution to getting a form filled out via email and the data to be passed automatically to Access after being approved. I need to know the code and where to put the code that will cause the approval date to auto insert in a cell when a checkbox selection is true. I can do it with a formula but the data will not update the record to my access database even if I do a range value prior to the update.
Also...
This is a Purchase Request and it has 7 rows where 7 different items can be requested, when you request an item you fill in Description, Amount, How many, Part No, etc. The problem is when they order something the Part No is required, which means I can't use the same code I used for the other required fields, because its only required when they use that line.
All of this I can do with ease in Access, but am doing some hair pulling with Excel.
Can you help, I have spent way to many hours on this already and my deadline for this project is next friday.
Below is the code I borrowed from one of you guys, it works great, thanks so much. I'm half way there.
~dawn

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "All Yellow Highlited Fields Must be Complete"
Cancel = True
End If
End Sub

 

[Dave Peterson]

Is this for one checkbox? And what kind of checkbox did you use? From the
Control toolbox toolbar or from the Forms Toolbar?

I'm guessing one checkbox from the control toolbox toolbar.

double click on that checkbox.

You'll see the VBE opening with code like this:

Private Sub CheckBox1_Click()

End Sub

Erase that and copy|paste this in:

Option Explicit
Private Sub CheckBox1_Change()
With Me.Range("a1")
If Me.CheckBox1.Value = True Then
.Value = Date
.NumberFormat = "mm/dd/yyyy"
Else
.ClearContents
End If
End With
End Sub

I used A1 as the cell that gets the date.

I think if I were worried about reusing one of those 7 part numbers, I'd fill in
all 7 part numbers and pre-populate quantities with 0. Lock those cells and
protect the worksheet. Then they don't have to type them at all.

If you want to something else, you may want to look at Data|Validation.

Debra Dalgleish has some techniques that hides any options that have been used
before.

http://www.contextures.com/xlDataVal03.html

 

[Guest]

I can't thank you enough. I did use the checkbox from the control toolbox toolbar, but there are 8 of them, one for each manager to approve. I just repeated the code for each one and it works great. Just what I needed =-

I guess I didn't explain the Part No section to well. The employee is required to fill in the Part No if he orders something on Line (Row) 1, so if he just wants one item he wouldn't be required to fill in the Part No on the next 6 lines (rows), etc
I would tie it to the Description field
If A1(Description) IsNotNull then require A2 (Part No
If B1(Description) IsNotNull then require B2 (Part No), etc

Also, Regarding the code that I borrowed for requiring data in the other fields, how do I get out of the template and leave these fields blank for the end user to fill in? Also what if the user changes his mind? How does he exit?
I really appreciate your help
~dawn