Private Sub Checkbox_Click VBA Query?

[ANDREZ]

Hi, I am fairly new to VBA in Word (Office 2004 v 11.2) but I have been building a simple data entry form to cover logging of some basic IT help desk support functions. This is just as a learning exercise. The checklist part of the form is just a list of standard procedures that a support person can "tick off" as having been done.

I have got the form to the point where I can enter data into the form text fields and all will be transferred into the form, saved and then printed. I can then exit the form interface and return to the IT Support log that has been produced.

My problem is that I have about 18 check boxes set up for a simple Yes/No response. When I click on one of these it is highlighted appropriately on the data entry form but when I run the Update Fields command nothing changes in the actual template document that I am tryin to populate with data.

The template document I have set up has checkbox form fields inserted but after I run the update fields command there is no cross or other check to indicate that one of the items has been selected from the data entry screen. When I click on the checkbox in the form editor I get the following code template but I dont know how to populate it with code to make it do what I need it to do. A search over 2 days of the Internet has turned up lots of other fascinating stuff on VBA but not the answer to what ought to be a simple problem......... for someone who knows more than me :-)

Private Sub CheckBox1_Click()
???????????????????
End Sub

I am sure there are lots of other ways of doing this but I would like some help with the current problem if possible.

Any help advice info would be much appreciated.

Cheers,

Andrez

[fumei]

OK, I am sure you can get the help you need. Let's walk it through a bit more.

1. You have a userform. How is it called? What opens the userform?

2. The userform has checkboxes.

3. the userform also has textboxes.

4. Here is where it starts to get confusing.... You ALSO have checkboxes IN the document. Correct?

5. "When I click on the checkbox in the form editor I get the following code template but I dont know how to populate it with code to make it do what I need it to do." First of...what do you need it to do? Describe EXACTLY, step-by-step, what you need to happen.

I will tell you immediately, that I would not be surprised if you can design what you have better. We can help.

Describe exactly what you want to happen.

Checkbox1_Click() is a userform procedure. It is what happens when the checkbox ITSELF is clicked. This may, or may not, be specifically the cursor touching the checkbox white area. It will fire if you click (touch) the text of the checkbox. It also makes the boxed "checked". It is a toggle. Click once, the check is True. Click again, the check is False.

What you want, I think, is to gather the information on whether the checkboxes are True, or False. In which case you need a procedure that looks at each checkbox and tests if it is true, or false. Again, Checkbox1_Click() makes it true, or false, which is not the same as looking to see if it is true, or false.

So tell us what you want to happen. Never mind what you think it will take to do that. What are the results you are looking for?

[ANDREZ]

Ah some sense of clarity in the confusion! Please bear with me for an hour or so as I have to pick up various family members from work, school etc just now.

In brief though the point about gathering the info on whether the checkboxes are true or not is they key. I will reply with the rest of the requested info in the next post.

Once again, thank you for taking the time with my query.

Kind Regards,

Andrez

[ANDREZ]

Hi again Fumei,

Family things have a way of taking up infinitely more time than planned sigh so much for the theory of get back to you in an hour or so. Anyway down to business. Answers to your query are as follows:

1. I access the user form by going into Tools Macros and selecting the macro called: EnterByForm. This calls up the userform entitled: frmkhrdetails. (where khr simply represents an acronym for the organisation). At the end of the project, I was looking at placing a macrobutton on the toolbar to make the whole thing easier for users to access.

2. Yes the user form has checkboxes. Please see the attached screenshot.

3. Yes the user form has text boxes for free data entry i.e. a descriptive summary of a users PC support issue, a summary of issues addressed, details of consultations etc.

4. Yes I also have check boxes in the document where I was wanting to have a visual representation of what had been ticked off in the userform. I set these up as form fields labelled Check1.....to Check 18. The layout is a 3 col x 6 row grid with the numbering going down the 1st col and uo to the next one etc. The default values of these fields is: not checked.

Yes looking at your summary of the key issue:

"What you want, I think, is to gather the information on whether the checkboxes are True, or False. In which case you need a procedure that looks at each checkbox and tests if it is true, or false".

This is exactly what I want. In my words, I want to gather this information i.e. which checkboxes have been marked, and I want the info to appear in the template document so that the checkboxes there are marked as well.

I can see the logic in what you have said below:

"Again, Checkbox1_Click() makes it true, or false, which is not the same as looking to see if it is true, or false."

I think that is the crux of the issue. To date I hadnt been able to find anything in ref books or on the net to help with the "looking" issue and so do appreciate your offer to assist.

Hope this helps.

Cheers,

Andre

[ANDREZ]

Hi again,

Is there any chance we can progress this issue? If not would it be possible to get pointed to a helpful link on this issue.

Kind Regards,

Andre

[fumei]

Here is one way...there are others. Sorry, I am away and while I did make a demo file for you to see, this computer can not ZIP. I can't upload the file. So....

On the Userform have the checkboxes named: chkVScan, chkSpyScan, chkRegClean etc.

In the document have checkbox formfields named: VScan, SpyScan, RegClean etc.

Have a commandbutton on the userform. Its click event would be:[vba]Private Sub cmdOK_Click()
Dim oControl As Control
For Each oControl In Me.Controls
If Left(oControl.Name, 3) = "chk" And _
oControl = True Then
ActiveDocument.FormFields(Right(oControl.Name, _
Len(oControl.Name) - 3)).CheckBox.Value = True
End If
Next
Unload Me
End Sub[/vba]

[ANDREZ]

Hi Fumei,

Thanks for your reply snd suggestion. Its 8.00 pm here in Aus right now and its been a long day. I will run through your suggestion first thing in the morning and let you know how I went later in the day. I'd be happy to receive a direct mail to see your demo or flick you a freeware zip .exe file so you can zip things. Assuming I can get things working I'd also like to get some sense of how/why it works so I can use the info as part or other projects.

Kind Regards,

Andre

[TonyJollans]

What version of Windows ae you using, Gerry?

Also, I thought the board had been changed to allow .doc files.

[geekgirlau]

Welcome Andrez - always nice to see another Aussie!

[ANDREZ]

Hi again folks,

Well I found that I had deleted the wrong form and the macros went west. I spent time rebuilding things and have a working form again. I have labelled the chekboxes in the userform and the document as suggested. I am just wondering about the command button part of the solution.

At present I have a command button that pulls the text out of the other fields and inserts that info into the form. Ideally I would like to be able to have the fields to update using just one command button not two. The code I have for the existing command button is as follows ( no doubt there are more efficient ways of doing this as well, but hey, it works!

Private Sub cmdUpdate_Click()
'Selects text in data form and copies to corresponding bookmarked fields in document template

Selection.GoTo What:=wdGoToBookmark, Name:="ClientName"
Selection.TypeText Text:=txtClientName

Selection.GoTo What:=wdGoToBookmark, Name:="DeptLocation"
Selection.TypeText Text:=txtDeptLocation

Selection.GoTo What:=wdGoToBookmark, Name:="ClientPhone"
Selection.TypeText Text:=txtClientPhone

Selection.GoTo What:=wdGoToBookmark, Name:="ClientEmail"
Selection.TypeText Text:=txtClientEmail

Selection.GoTo What:=wdGoToBookmark, Name:="AssetID"
Selection.TypeText Text:=txtAssetID

Selection.GoTo What:=wdGoToBookmark, Name:="SerialNo"
Selection.TypeText Text:=txtSerialNo

Selection.GoTo What:=wdGoToBookmark, Name:="HardwareItem"
Selection.TypeText Text:=txtHardwareItem

Selection.GoTo What:=wdGoToBookmark, Name:="ModelType"
Selection.TypeText Text:=txtModelType

Selection.GoTo What:=wdGoToBookmark, Name:="TechNameA"
Selection.TypeText Text:=txtTechNameA

Selection.GoTo What:=wdGoToBookmark, Name:="ProblemDesc"
Selection.TypeText Text:=txtProblemDesc

Selection.GoTo What:=wdGoToBookmark, Name:="IssuesIdent"
Selection.TypeText Text:=txtIssuesIdent

Selection.GoTo What:=wdGoToBookmark, Name:="ITManagerName"
Selection.TypeText Text:=txtITManagerName

Selection.GoTo What:=wdGoToBookmark, Name:="ConsultDate"
Selection.TypeText Text:=txtConsultDate

Selection.GoTo What:=wdGoToBookmark, Name:="OtherIT"
Selection.TypeText Text:=txtOtherIT

Selection.GoTo What:=wdGoToBookmark, Name:="OthITConsultDate"
Selection.TypeText Text:=txtOthITConsultDate

Selection.GoTo What:=wdGoToBookmark, Name:="OthITConsultTime"
Selection.TypeText Text:=txtOthITConsultTime

Selection.GoTo What:=wdGoToBookmark, Name:="FollowUpdetails"
Selection.TypeText Text:=txtFollowUpDetails

Selection.GoTo What:=wdGoToBookmark, Name:="JobEndDate"
Selection.TypeText Text:=txtJobEndDate

Selection.GoTo What:=wdGoToBookmark, Name:="JobEndTime"
Selection.TypeText Text:=txtJobEndTime

Selection.GoTo What:=wdGoToBookmark, Name:="ClientFeed"
Selection.TypeText Text:=txtClientFeed

Selection.GoTo What:=wdGoToBookmark, Name:="ClientFeedDate"
Selection.TypeText Text:=txtClientFeedDate

Selection.GoTo What:=wdGoToBookmark, Name:="ClientFeedTime"
Selection.TypeText Text:=txtClientFeedTime

Selection.GoTo What:=wdGoToBookmark, Name:="MiscNotes"
Selection.TypeText Text:=txtClientMiscNotes


End Sub

So my question is can you show me how to incorporate your suggestions below into my command button code above?

Private Sub cmdOK_Click()
Dim oControl As Control
For Each oControl In Me.Controls
If Left(oControl.Name, 3) = "chk" And _
oControl = True Then
ActiveDocument.FormFields(Right(oControl.Name, _
Len(oControl.Name) - 3)).CheckBox.Value = True
End If
Next
Unload Me
End Sub

Thanks for your help and patience,

Andrez

PS: Always nice to hear from a fellow Aussie too, geekgirl!

[fumei]

Hmmmm. Seems to bge cross purposes here. OK.l Your original post was talking about CHECKBOXES. That is, checkboxes on the Userform, and checkboxes in the document. That is what I did.

The checkcboxes on the userform are named as I named them.
The checkboxes in the document use the same name, but with the added "ff_" to indicate that this named object is a formfield.

Got it? So the code runs through the checkboxes on the userform, and if it IS checked, checks the matching checkobox in the document. One button, one piece of code.

Now, you seem to be not using checkboxes on the userform, but textboxes. Also, now, you are putting the text of the userform textboxes into bookmarks. Not having matching checks.

Please describe again what you want to happen.

[fumei]

Oh, and using Selection to insert text into bookmarks is not an efficient way of doing things. If you are possibly going to want to have the ability to change what the user does, this is not so good.

[ANDREZ]

Thanks again for your time.

1. I dont want to be at cross purposes and so will include a screen shot of the user form. Note that I have used a copy of the form in process so you can see how say the checkboxes might be used.

2. The purpose of the form is to enable a help desk person to log a help desk process in a simple manner. Therefore the form has the usual summary data about the caller , the tech and the problem. These are entered into the text fields. The check boxes are there to enable the tech to check off particular support tasks that might have been relevant to the job.

So summarising, the text fields are on the form and are working OK at that basic level. I'm sure there are more efficient ways to do this and would like to look at another way of doing things. Thats what learning is about.

The check boxes remain the current issue though. It is this data that does not get transferred when I hit the Update fields button. The text fields all transfer OK. I would like both sets of data from text and checkboxes to be updated at the same time when I hit the update button. Sorry if that wasnt clear. I hope it is clearer now.

3. re this comment: <The checkboxes in the document use the same name, but with the added "ff_" to indicate that this named object is a formfield>.

I have named the fields in the user form as: chkPhys etc. In the document I have simply named the field as: Phys, in the Field settings (BookMark) section of the Checkbox form field options. from your note above does this mean I need to label the field as: ff_Phys etc?

Or is this completely off the mark as well?

4. By the way I'd be happy to flick over the actual form if there is a way to do so. Can I just upload it here or email direct?

Kind Regards,

Andrez

PS: Please bear with me though I am really new to this area even though I have a long background in other aspects of IT support.

[ANDREZ]

Hi again,

Just a quick note to add that the Command button underneath the Model/Type fields was just a test button to use to see if the command button suggestions would work by them selves. I tried but the data didnt transfer to the document and so I drew a blank there. That was when I realised I wanted this to all be part of one command when the Update fields button was hit. I would normally want the button there but as I am working on functionality and not aesthetics at the moment thats OK for now.

Cheers,

Andrez

[fumei]

RE: other stuff, ZIP etc etc. I am off in the wilds of California at an eccentric friends place. He does not want anything installed. So that is that. I will be back at my normal abode (ie. home) in a week.

OK. Yes. Make the checkbox names in the document match the checkbox names on the userform. Th euserform names with "chk" at the start of the name, and the exact same name after. Userform: chkVirusScan; Document: VirusScan.

Sorry the ff_ thing was from another thread....

OK. As for the textboxes matching, you can do this easier.[vba]ActiveDocument.Bookmarks("AssetID").Range.Text = _
txtAssestID.Text[/vba]There is no need to select (goto) the bookmark and typr in the text.

I must point out that there is no error checking here. Blank textboxes will be put in as blank. Gibberish will go in as glibberish.

You can post a document here if you like. I can look at it for you if you want.

[ANDREZ]

Hi Fumei,

I'm glad that ff_ thing is cleared up. Anyway the userform and document fields are set up in the suggested format. It might make things easier to upload the form so that the whole thing can be looked at so I'll attach it below as a zip file due to the site file limits.

For what its worth though as the form is only for local/internal use so if a Tech inserts gibberish then the form is less than useful to him or her. On the other hand if the boss looks at the hard copies and finds junk inserted then that Tech can have the job of explaining the paperwork and not me as I find support logs quite useful when following up a persistent user issue.

I guess as a learning exercise its just was useful to work back from a finished item and being able to see what was done and then building on those insights. Its probably just too easy if you know how already (sigh!).

Cheers,

Andrez

[fumei]

well **** me off. I can log on as fumei, I SHOW as logged on, but when I try to send replies it says I am NOT logged on and totally erases my answer. I have to go right back out and log on from the beginning again. Harumph.

OK, this is my fourth attempt at writing a reply. You may be pleased that it is likely to be shorter than the earlier ones.

1. Look at the document.
2. Got not get full functuionality of the displayed userform. It is too big and I can not get it to scroll. It is WAAY too big. So I could not test the checkboxes at the bottom.
3. Why are you using frames? They are not really needed. They also messed up my loop code for the controls - which could have been fixed...but I removed the frames instead.
4. Hmmmmmmm. If YOU saw a checkbox with Y/N beside it...would you assume checked means Y? If so...why have the N. There is no explanation. This is not good. You should have some explanatory text. "Check the boxes for processes / activities that HAVE been actioned." O rsomething like that. Checkboxes mean On or Off. True or False You may think that extrapolates to Yes/No...but it does not.
5. I redesigned the form, and the document, rewrote the code for the form. It works.
6. Get rid of all those procedures that you are not using, or will use.
7. USE OPTION EXPLICIT!!!!!!!![vba] Dim oControl As Control
For Each oControl In Me.Controls
If Left(oControl.Name, 3) = "chk" And _
oControl = True Then
ActiveDocument.FormFields(Right(oControl.Name, _
Len(oControl.Name) - 3)).CheckBox.Value = True
End If
Next

With ActiveDocument
.Bookmarks("ClientName").Range.Text = txtClientName.Text
.Bookmarks("DeptLocation").Range.Text = txtDeptLocation.Text
.Bookmarks("ClientPhone").Range.Text = txtClientPhone.Text
.Bookmarks("ClientEmail").Range.Text = txtClientEmail.Text
' etc etc etc
End With
Unload Me[/vba]works. It moves all the info from the textboxes (that I adjusted as Bookmark.Range.Text), and the values for the checkboxes over to the document. This is pretty much as I have previously posted. I am not uploading the fixed document.

[fumei]

I tried to edit some spelling in the above post...and it logged me back out again.

Hmmmm.

[fumei]

I really would think about doing a bit of a redesign. You may want to consider using a multipage on the userform.

[ANDREZ]

OK after some thought about this I'll respond as follows:

1. First, as I have stated several times, I am a beginner at all this so telling me to use code: "USE OPTION EXPLICIT" leaves me with nowhere to go.

I looked up the expression in VBA help but at my level it makes little sense.
The "explanation" from VBA help is as follows:

<Syntax
<Option Explicit
<Remarks
<If used, the Option Explicit statement must appear in a module before any <procedures.
<When Option Explicit appears in a module, you must explicitly declare all <variablesusing the Dim, Private, Public, ReDim, or Static statements. If <you attempt to use an undeclared variable name, an error occurs at <compile time.
<If you don't use the Option Explicit statement, all undeclared variables are <of Variant type unless the default type is otherwise specified with a <Deftype
<statement.
<Note Use Option Explicit to avoid incorrectly typing the name of an <existing variable or to avoid confusion in code where the scope of the <variable is not clear.

A link or kb article reference would have been useful here.

Yes I can go and learn about dim statements etc from a textbook and thats what I will have do when I have the luxury of a little more time because I dont know how to apply the advice provided to date. So thanks at least for showing me where to start looking.

I really do appreciate your time and assistance to date but it doesnt help me to work things out at all if you simply throw code at me (with emphasis !!!!!!!) and dont tell me where to put it and how to use it.

2. Frames werent needed I just wanted to see what if anything they had from a visual point of view and probably would have been removed at the end of the development process.

3. The document works on my screen and the issue of differing screen sizes didnt occur to me when I uploaded the document. I said I was working with functionality and not aesthetics at this stage.

4. Thanks for the discourse on the Y/N labels on the checkboxes. I can see what you mean at the deeper level of coding. However for the purpose and context of this form it was just about reinforcing for the user that they had an option to mark the checkbox. Clearly if a tech had employed a particular procedure they would be expected to check off that item. In that case I would want the fact that they had used that procedure to be reflected in the equivalent document field when the fields are updated.

5. Its nice to know the form works after the code and the interface was fixed.

At this stage I think I'll leave the issue as unresolved for the time being as I dont have the skills to take the project any further and I cant look at a working example of my form to understand either the coding concepts you have recommended or their implementation in the revised example.

Again I thank you for your time.

Cheers,

Andrez