Solved: CheckBox Help

[neilholmes]

Hi all... (Back again). :hi:

I've had some great help from everyone on the forum regarding the reports I am building, but have come up against another problem that I was hoping someone would be able to help me with.

Previously, I have been using OptionButtons built in userforms to help the user add content to a template report. (See thread: http://www.vbaexpress.com/forum/showthread.php?t=37681). This works great for adding single options.

I have now met a situation where there are multiple options for the user.

For example.



From the following list, please select which statements you would like included in your report.

Statement 1
Statement 2
Statement 3
Statement 4


In this example the user will have the option to select 1 + 2 + 3 + 4, 1+4 or any conbination of the options.

So far (like the previous coding) I have looked to built this by importing from other documents. So each statement will have a corresponding .dot file, and there will be a bookmark in place within the template (BookmarkStatement1 for example) to receive the copied content.

The bookmarks (in the template) are stacked on top of each other as follows:



][
][
][
][


I have built some code based on the previous code that if, by using CheckBox's the CheckBox is 'True' the code will copy in the required content to the bookmark. If the CheckBox is 'False' it will copy in blank content.


Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""
If CheckBox1 = True Then
StrVar1 = "1"
ElseIf CheckBox2 = False Then
StrVar1 = "2"
End If

Set Doc1 = Documents.Open("C:\MY DOCUMENTS\STATEMENT" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("STATEMENT").Range.Paste
Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""


Is there a better way to handle this ? My biggest issues are that when content is pasted in (actual content and blanks), I end up with spaces and a lot of mess between content. For example:



]Statement 1 Text[

][

][

][

][


Any help would be great... And I hope my explanation is clear enough for people to understand.

- Neil

[gmaxey]

Neil,

Is there some reason you are not using AutoText entries or BuildingBlocks to define your "canned" statements?

Assuming that one or more checked statement go in the same point in the document then use a single bookmark target and build around it:

Option Explicit
Private Sub CommandButton1_Click()
Dim oRng As Word.Range
Dim oRngWorking As Word.Range
Set oRng = ActiveDocument.Bookmarks("CommentBM").Range
oRng.Text = ""
ActiveDocument.Bookmarks.Add "CommentBM", oRng
If Me.CheckBox1.Value = True Then
Set oRng = ActiveDocument.AttachedTemplate.AutoTextEntries("Commant A").Insert(oRng)
ActiveDocument.Bookmarks.Add "CommentBM", oRng
End If
If Me.CheckBox2.Value = True Then
Set oRng = ActiveDocument.Bookmarks("CommentBM").Range
Set oRngWorking = oRng.Duplicate
oRngWorking.Collapse wdCollapseEnd
Set oRngWorking = ActiveDocument.AttachedTemplate.AutoTextEntries("Comment B").Insert(oRngWorking)
oRng.End = oRngWorking.End
ActiveDocument.Bookmarks.Add "CommentBM", oRng
End If
If Me.CheckBox3.Value = True Then
Set oRng = ActiveDocument.Bookmarks("CommentBM").Range
Set oRngWorking = oRng.Duplicate
oRngWorking.Collapse wdCollapseEnd
Set oRngWorking = ActiveDocument.AttachedTemplate.AutoTextEntries("Comment C").Insert(oRngWorking)
oRng.End = oRngWorking.End
ActiveDocument.Bookmarks.Add "CommentBM", oRng
End If
If Me.CheckBox4.Value = True Then
Set oRng = ActiveDocument.Bookmarks("CommentBM").Range
Set oRngWorking = oRng.Duplicate
oRngWorking.Collapse wdCollapseEnd
Set oRngWorking = ActiveDocument.AttachedTemplate.AutoTextEntries("Comment D").Insert(oRngWorking)
oRng.End = oRngWorking.End
ActiveDocument.Bookmarks.Add "CommentBM", oRng
End If
Unload Me
End Sub

[neilholmes]

Thanks for the reply Greg. I haven't used AutoText or Building Blocks due to the formatting of the additional content. I have very specific formatting contained in the .dot files that are being added in that I sadly need to stick to. This includes tables, bullet points and .jpg images.

Based on my original code, is there a way that if the CheckBox is True then the content is copied in as normal, but if it is False then the code simply does nothing ?

- Neil

[gmaxey]

Neil,

Ok. But tables, bullet points, and images can be stored as AutoText or BuildingBlocks same as plain text.

The code I posted above should meet your criteria for "If the Checkbox is Ture or If it is False."

[neilholmes]

Thanks Greg,

I will give it a try and let you know how I get on.

- Neil

[neilholmes]

Hi Greg,

Tried it out and it works, sort of ! I saved 'Comment A' as AutoText in my .dot file. Run the code and select CheckBox1. Comment A will then paste to the bookmark as required.

After testing I then decided to add all my lengthy comments into AutoText. This is where I run into a problem...

When highling text in the body of my document, I select, Insert > Auto Text > New. I save my comment with reference 'Comment A'.

I run the code and get the error: 'The requested member of the collection does not exist'.

When Debugging, the following is highlighted:


Set oRng = ActiveDocument.AttachedTemplate.AutoTextEntries("Comment A").Insert(oRng)


I have worked out that if I go to Insert > AutoText > AutoText and add a new auto statement that I can set to 'Look in: (My current template.dot (template)) the code will work fine. But if I highlight text and add this to autotext this saves to 'Look in: All active templates', making the code error. I do not seem to be able to highlight text and specify which template this is to 'look in'.

Hope that makes sense.

Any advice ?

- Neil

[gmaxey]

Neil,

When you create the new autotext you shoud be able to use (instead of Alt+F3) Insert>AutoText>AutoText>Add. This diaglog lets you set the "Look in" (or better "store in") template.

One thing I left off in the code was the RichText:=true.

or:

").Insert(oRng, True)

I can't upload a template with code and autotext entries. Shoot me an e-mail and I will send you one if needed.

[neilholmes]

Hi Greg, thanks for the info. Sorry couldn't reply to the PM for some unknown reason !

The code works great, just what I need. One final question however. Just looking at 'user' error handling once again. Is there a way to have a message come up to prompt the user to select at least one of the check boxes should they accidently forget to make a choice ?

Or, (like in the previous code you have helped me on) have CommandButton1 False until at least one selection is made.

Thanks

- Neil

[neilholmes]

Oh ! Don't worry about that last question, I have worked it out. Based on the previous code I replace 'OptionButton' with 'CheckBox'. That way the user has to select at least one CheckBox to include for CommandButton1 to become active.

I think I (you) have this one finally sorted out !

:grouphug:

- Neil

[gmaxey]

Great.