Check box values to text

wpryan · #1 10-14-2013, 06:03 AM

Hi All,
I'm trying to automate a training certificate by adding a user form in VBA to populate the variables in the document. In it, I have check boxes indicating which course(s) were taken. I'd like to include some code which loops through the items to populate the document. Output is going to a referenced bookmark in the document. Not sure how to approach it. Any help is appreciated.

gmaxey · #2 10-14-2013, 07:01 AM

Code:

Private Sub CommandButton1_Click()
Dim oCtr As Control
Dim oRng As Word.Range
  For Each oCtr In Me.Controls
    Select Case TypeName(oCtr)
      Case "CheckBox"
        Select Case oCtr.Name
          Case "CheckBox1"
            If Controls(oCtr.Name).Value = True Then
              Set oRng = ActiveDocument.Bookmarks("bmCB1").Range
              oRng.Text = "CheckBox1 is Checked"
              ActiveDocument.Bookmarks.Add "bmCB1", oRng
            End If
        End Select
      Case "TextBox"
        
      Case "ComboBox"
        
      Case "ListBox"
        
    End Select
  Next oCtr
  Unload Me
End Sub

wpryan · #3 10-14-2013, 10:04 AM

Thanks for the response, but I guess I'm not getting it. I copied and pasted into the Click command, and changed some of the terminology to match what's in my document. Not working. Here's what I changed (in red):

Code:

Dim oCtr As Control
Dim oRng As Word.Range
  For Each oCtr In Me.Controls
    Select Case TypeName(oCtr)
      Case "Checkbox"
        Select Case oCtr.Name
          Case "ckLasik"
            If Controls(oCtr.Name).Value = True Then
              Set oRng = ActiveDocument.Bookmarks("bmCourse").Range
              oRng.Text = "CheckBox1 is Checked"
              ActiveDocument.Bookmarks.Add "bmCourse", oRng
            End If
        End Select
        
    End Select
  Next oCtr

Of course I have 8 other "cases", for which I should repeat the code, right?:

Code:

Case "Checkbox"
        Select Case oCtr.Name
          Case "ckLasik"
            If Controls(oCtr.Name).Value = True Then
              Set oRng = ActiveDocument.Bookmarks("bmCourse").Range
              oRng.Text = "CheckBox1 is Checked"
              ActiveDocument.Bookmarks.Add "bmCourse", oRng
            End If

gmaxey · #4 10-14-2013, 10:15 AM

Is your checkbox named ckLasik and is your bookmark named bmCourse? Put a stop statement in the code and execute it. When you reach the stop, step through using the F8 key and see if you can determine what is not working:

Code:

Dim oCtr As Control
Dim oRng As Word.Range
  For Each oCtr In Me.Controls
    Select Case TypeName(oCtr)
      Stop 'Added stop statement.
      Case "Checkbox"
        Select Case oCtr.Name
          Case "ckLasik"
            If Controls(oCtr.Name).Value = True Then
              Set oRng = ActiveDocument.Bookmarks("bmCourse").Range
              oRng.Text = "CheckBox1 is Checked"
              ActiveDocument.Bookmarks.Add "bmCourse", oRng
            End If
        End Select
        
    End Select
  Next oCtr

wpryan · #5 10-14-2013, 10:37 AM

If I scroll through the code, hovering my mouse over the statement

Quote:

If Controls(oCtr.Name).Value

I see that the different elements are being identified by the code, however there is no transfer of text as I would expect.
The items ckLasik is a check box, and bmCourse is a bookmark.

gmaxey · #6 10-14-2013, 10:42 AM

When you "step" through the code using the F8 key, is the:
oRng.Text = "Checkbox 1 is checked"
line of code being executed?

wpryan · #7 10-14-2013, 11:14 AM

No, the oRng.Text line is not firing.

gmaxey · #8 10-14-2013, 11:31 AM

You will need to figure out what lines are firing and what lines aren't then figure out why. For example, if that line isn't firing then the value of the checkbox my be False (e.g., unchecked).

wpryan · #9 10-14-2013, 11:56 AM

Got it, it was a syntax error on my part. This gets me about halfway there... What I wanted, in the end, is for every check box selected, then the book mark would have a concatenated value of those items (e.g, "CheckBox 1 is Checked, Checkbox2 is Checked" etc.)

gmaxey · #10 10-14-2013, 12:05 PM

Code:

Private Sub CommandButton1_Click()
Dim oCtr As Control
Dim strComposite As String
  For Each oCtr In Me.Controls
    Select Case TypeName(oCtr)
      Case "CheckBox"
        If oCtr.Value = True Then
          If strComposite = vbNullString Then
            strComposite = oCtr.Name & " is checked"
          Else
            strComposite = strComposite & ", " & oCtr.Name & " is checked"
          End If
        End If
      End Select
  Next oCtr
  'Replace this statement with code that writes the string variable to your bookmark.
  MsgBox strComposite
End Sub

wpryan · #11 10-14-2013, 01:13 PM

Thanks a lot, you're amazing. With some massaging I got it working. Last question... if I have the document open, and the bookmark is already populated, is it possible to clear the existing data? I tried this code, but it's not working:

Code:

If strComposite <> "" Then
ActiveDocument.Bookmarks("Course").Range.Text = strComposite
Else
ActiveDocument.Bookmarks("Course").Range.Text = ""
End If

gmaxey · #12 10-14-2013, 01:23 PM

I'm not sure what you are trying to do exactly, however if you use the following method you will always write the string to the bookmark regardless if the string is null or not. Doing so always destroys and recreates the bookmark:

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Dim strSomething As String
  Set oRng = ActiveDocument.Bookmarks("bmBookmarkName").Range
  oRng.Text = strSomething
  ActiveDocument.Bookmarks.Add "bmBookmarkName", oRng
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Generate chart from list of text values	knownunknown	Excel	6	04-24-2013 01:56 AM
Check box and text box in same document ? cant edit / enter text	KennyG	Word	6	04-08-2013 10:59 AM
Assigning Values to content control checkboxes and calculating results	creative cathy	Word Tables	13	10-07-2012 08:52 PM
Handle Text / Numeric values in SSRS while Export To Excel	achuki	Excel	5	02-07-2012 02:14 PM
How Do I Put Text Values Into A CSV That Excel Will Leave Along?	eBob.com	Excel	2	05-04-2011 07:01 AM