Check Boxes on a form

[Doctorjones_md]

I have a form with (2) check boxes (neither of which are required to be
checked) and a cmdOK button. I want to have a message box display if
neither are selected when the user clicks the "OK" button.

Here's what I have:

Private Sub cmdOK_Click()
If cbx6850.Value = False Then
MsgBox "You must make a selection before choosing the OK button!" _
, vbOKOnly + vbCritical
cbx6850.SetFocus
Exit Sub

End If


End Sub

I don't know how to modify it so that it requires at least one of the check
boxes to be checked -- any ideas (I'm sure there's a simple solution, but
I'm relatively new to VBA)

Thanks in Advance!

 

[Greg Maxey]

Something like this:
Private Sub CommandButton1_Click()
If Me.CheckBox1 = False And Me.CheckBox2 = False Then
MsgBox "You must check box A, B, or both"
End If
Me.Hide
End Sub

 

[Doctorjones_md]

Greg,

Thanks a bunch -- I'd forgotten about the Drop-Down provision for Me

Your suggestion works GREAT!

 

[Doctorjones_md]

Greg,

I'm not sure if this thread/post is being seen -- I'd gotten a response from
Doug Robbins a while back, but I'm having some difficuty with his
instruction. Can you point me in the right direction on this one please?

Previous Post/Thread from yesterday (10/24/06)
Doug -- sorry for the delay in my reply -- I've been spending some time
trying to see if I could make some headway on this project ...

Here's what I'm working with:

I have a Word.dot template with (1) form (frmMain)

1. The code on the Word.dot is:

Option Explicit

Private Sub Document_New()
frmMain.Show
End Sub
==================================
2. The code on frmMain is: (****See explanatory Notes****)

Option Explicit

Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub

'initialize form
Private Sub frmMain_Initialize()

txtCompany.Value = Null
txtProject.Value = Null
txtAddress1.Value = Null
txtAddress2.Value = Null
txtCity.Value = Null
txtState.Value = Null
txtZip.Value = Null
txtName.Value = Null
txtTitle.Value = Null
txtPhone.Value = Null
txtMobile.Value = Null
txtEmail.Value = Null
txtStateFull.Value = Null
cbxProcurement.Value = Null

End Sub

'set document population variables from userform input
Private Sub cmdOK_Click()

Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("CompanyCover").Range.Text = txtCompany.Value
.Bookmarks("ProjectCover").Range.Text = txtProject.Value
.Bookmarks("CompanySow").Range.Text = txtCompany.Value
.Bookmarks("ProjectSow").Range.Text = txtProject.Value
.Bookmarks("Address1Sow").Range.Text = txtAddress1.Value
.Bookmarks("Address2Sow").Range.Text = txtAddress2.Value
.Bookmarks("CitySow").Range.Text = txtCity.Value
.Bookmarks("StateSow").Range.Text = txtState.Value
.Bookmarks("ZipSow").Range.Text = txtZip.Value
.Bookmarks("NameSow").Range.Text = txtName.Value
.Bookmarks("PhoneSow").Range.Text = txtPhone.Value
.Bookmarks("MobileSow").Range.Text = txtMobile.Value
.Bookmarks("EmailSow").Range.Text = txtEmail.Value

End With

'Insert Procurement Ts & Cs (****This simply inserts the contes of an
entire file into the Active Document -- I need to be able to discriminate
between which rows in a table to insert into the Active Document****)

Dim sFilePath As String
If cbxProcurement = True Then
ActiveDocument.Bookmarks("Procurement").Select
sFilePath = ActiveDocument.AttachedTemplate.Path & "\Shane.doc"
Selection.InsertFile sFilePath, , False, False
Selection.InsertBreak Type:=wdSectionBreakNextPage
ActiveDocument.TablesOfContents(1).Update

'End Insert Procurement T's & C's

'These are the bookmarks on the page to be inserted into
coverpage/Proposal

Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("CompanyProc").Range.Text = txtCompany.Value
.Bookmarks("StateFullProc").Range.Text = txtStateFull.Value
.Bookmarks("Address1Proc").Range.Text = txtAddress1.Value
.Bookmarks("Address2Proc").Range.Text = txtAddress2.Value
.Bookmarks("CityProc").Range.Text = txtCity.Value
.Bookmarks("StateProc").Range.Text = txtState.Value
.Bookmarks("ZipProc").Range.Text = txtZip.Value
.Bookmarks("CompanyProc2").Range.Text = txtCompany.Value
.Bookmarks("Address1Proc2").Range.Text = txtAddress1.Value
.Bookmarks("Address2Proc2").Range.Text = txtAddress2.Value
.Bookmarks("CityProc2").Range.Text = txtCity.Value
.Bookmarks("StateProc2").Range.Text = txtState.Value
.Bookmarks("ZipProc2").Range.Text = txtZip.Value
.Bookmarks("CompanyProc3").Range.Text = txtCompany.Value

End With

End If

Application.ScreenUpdating = True

Unload Me

End Sub
====================
EXPLANATORY NOTES: The above codes works, but what I need for it to do is
this --

I've added several additional forms (accessed from frmMain) which have
checkboxes and radio buttons fo the user makes his or her selections. The
the data in the corresponding bookmarks should then be inserted into the
Word.dot when the OK button is clicked. Here are my questions:

1. I need for the SUBMIT button on frmMain to be disabled until the user
makes at least (1) selection from the subordinate forms -- what is the best
way to achieve this?

2. I need for these (10) forms to insert the corresponding bookmarks into a
Table (1) principal Word.dot -- What I did in the Word.dot was to take your
advice and bookmark the entire row (spanning 4 columns) -- I'm lost on how
to incorporate your code
(ActiveDocument.Bookmarks("bookmarkname").Range.Cells(1).Range).

I'm kinda wrapped around the axle here -- Could you give me an idea what I
need to do to incorportate this functionality -- for example:

Just a simple list of components needed ...

Word.dot - with sample code
A couple of forms showing code to allow for selected checkboxes/radio
buttons to populate bookmarks in the Word.dot (active document)

Question: Wouldn't it be much simplier to import the data from an Excel WB,
and if so, what is the best way to associate the
checkboxes/radio buttons on the forms to the Excel workbook (so that only
the selected items/data is inserted into the Word.dot)?

Much Thanks In Advance

Shane

 

[Greg Maxey]

Well that is a lot of material to absorb and without the document it
would be a lot of work to sort out here in the newsgroups. To tell you
the truth I am simply not up to it gratis at present.

For your question 1, "Best" is subjective. Here is one way.


Start your main form out with the sumbit button disabled:

Private Sub UserForm_Initialize()
Me.CmdSubmit.Enabled = False
End Sub

I assume the other forms are called by a command button on main form.
So something like this will show Form 1

Private Sub CmdShowForm1_Click()
UserForm2.Show
End Sub

Private Sub CmdSubmit_Click()
'Do whatever
Me.Hide
End Sub

Now set the OK or submit button on Form1 to be disabled also at
startup. Then when one of the checkbox values is True enable the OK
button and put code in the OK button that enables the Submit button on
the main form:

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = -1 Then Me.CmdForm1OK.Enabled = True
End Sub
Private Sub CheckBox2_Click() 'Repeat for the remaining checkboxes.
If Me.CheckBox1.Value = -1 Then Me.CmdForm1OK.Enabled = True
End Sub
Private Sub CmdForm1OK_Click()
UserForm1.CmdSubmit.Enabled = True
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Me.CmdForm1OK.Enabled = False
End Sub

 

[Doctorjones_md]

Greg,

Thank you for pointing me in the right direction -- this News Group is VERY
helpful, and I truely appreciate your guidance (and that of others) as I
work through these problems -- it's a GREAT medium to Learn & Share!

I'll try to limit the scope of my questions (in the future) -- I appreciate
the time you (and others) take to help the rest of us. Thanks again!

Shane
========================