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: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

[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.ActiveDocument.Bookmarks("AssetID").Range.Text = _
txtAssestID.TextThere 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!!!!!!!! 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 Meworks. 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

[fumei]

Ooops. Sorry. I was rude. OK. Here is how to set up Option Explicit. In the Visual Basic Editor go Tools > Options. Check "Require Variable Declaration". It is under the Editor tab - which should be the default. That is it. All new code modules will have Option Explicit. This is a good thing. There are articles on this forum regarding the use of Option Explicit. In anycase, set the editor to have and then forget about it. You will have to declare your variables from now on.

Forget about my uncalled for rants re: design. Just use the code I posted. I woul dlike you to be able tomark this solved. The code works. Use the naming convention I suggested, and the code I posted. RE: the Y/N thing. Ah...forget it. I was out of line. if the design works for you, then fine..I should learn to keep my mouth shut.
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.If you use the names as I suggested, and the code, it WILL be reflected properly in the document fields. The chekcboxes match, the textboxes get the proper text, all from one button procedure. Here is the code again:Sub UpdateFields_Click()
Dim oControl As Control
On Error Resume Next
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
End SubThe added line On Error Resume Next gets rid of the run-time error caused by the frames. So you can keep them. Please try the code. Replace UpdateFields_Click with the above and try it.

[ANDREZ]

Hi again,

Thanks for that Fumei, we can move on now.

Your clarification re Option Exp really helps. Hopefully I can get into it tomorrow but Fridays have a way of springing unexpected things on me so it may not be till the weekend but I am looking forward to the process. Bear with me for a day or so and maybe another question or so about making it happen.

Talk soon.

Cheers,

Andrez

[ANDREZ]

OK, we have lift off! After a bit of fiddling and tweaking the data transfer from user form to document works. Thanks and much appreciated for your help Fumei.

I'll just ask you to bear with me a little longer as I would now like to address some of the interface design issues you raised but will need to find time over the weekend to make a list and see what I can research myself and what I need some help to clarify. I'm sure that they will be a snap to work thru after the checkbox saga.

Let me know if yr ever visiting OZ!

Kind Regards,

Andrez

:beerchug: :friends:

[fumei]

Whew! Glad you have some movement with this thing!

[ANDREZ]

Hi again,

Well here is the last element of my post for this issue. I've done done work on the form and cleaned it up so I think it looks better now (says me anyway). Ive added a macro button that is part of normal.dot so that users can run the form from a button on their toolbar. In doing this I've had time to think about what else would be useful to the form. So I've come up with this list which should wrap up this little exercise. I would like to know how to do the following:

1. In the Job number field, I'd like to have a job number entry that increments each time the form is used. I'd also like to be able to use a text prefix like say: AZ 1234, where AZ is the technicians id (or say the techs initials) and the numbers represent the job number.

2. I'd also like to include say a date showing when the user form was run. Not sure where the most logical place for that would be i.e. as something that gets entered when the fields are updated or say inserted as a footer in the word document?

3. The issue I havent been able to clarify though is how to make the form resizeable and moveable aqround the screen so that a user has the flexibility to get the form to suit his or her own screen. The model I am thinking of is the liquid table layouts that you can create in web pages using CSS techniques.

4. Color can be a useful tool to guide a users eye to key sections of a form. The color options that I can find have a very limited pallete. Can I just paste in say hex colour numbers and have that colour use? Or is there another way to access a wider colour pallete? Or is colour just a usless distraction in VBA?

5. Oh yes the idea of a multipage form was interesting. Is there a reference to a discussion or how to for that type of thing? Alternatively would it be a hard thing to convert say my one page form over to a multipage format?

Cheers, and thanks in advance for your help.

Andrez

[ANDREZ]

Hi again, one last question about this form. I have set the tab order using TabIndex starting with the first text box as number 0 and so on to the last box. I have also set the TabStop to True. Unfortunately when I run the form the tabbing doesnt work and instead jumps to a field much later in the form. That field is definitely not numbered 1 (i.e. the field that I expected the cursor to jump to). My reference books dont seem to be able to offer much here in terms of TabStop debugging options. Will upload the latest iteration of the form FYI.

Cheers,

Andrez

[fumei]

Hi Andrez, I am now back from the wild, and will take a look at this puppy. Give me a day or so, as it was a wild trip back.

However....

1. I can't remember. You ARE using this from a template, correct? A REAL template? The document is cloned from the source template? If so, the best thing if you really want to track this is have a simple little text file, a log file really. When a new document is cloned, the log file is opened and the last job number extracted. You could also use a document variable in the template that could be updated. The dis-advantage of that is the template file itself would have to be opened to up that variable. I do not recomment this. A simple log text file would be fine. You would need to determine what and how would would pick up the tech ID. Should not be difficult.

2. A current date for the creation of the document is easy and straightforward.

3. Moveable is easy - do not make the form modal. The user can move the form. Resizing? Ah, another issue. For critical design where the design visibility was crucial, and very dependent on screne resolution, well - I do it the hard way. I make three separate forms. Each one similar, but font size, and control placement derives directly from design in THAT resolution. I design for 800 x 600 (yes, a LOT of out staff still use it), 1024 x 768 (still the most common) and 1600 x 1200 (or whatever you decide). So yes, I actually make thre separate forms. Right off the top, when the template is getting ready to be cloned, I check current system screen resolution. From that it is easy.Select Case System.HorizontalResolution
Case 800
Call Run800
Case 1024
Call Run1024
Case 1600
Call Run1600
Case 640
Msgbox "640 x 480" & vbcrlf _
"Bleeech!!! Call your manager. Everybody is " _
"Supposed to be at least 800 x 600 by now! " _
"Come back when you have a decent monitor!"
Case Else
Msgbox "What the heck resolution are you using?" _
" Using 1024 x 768 as default size for UserForm."
Call Run1024
End SelectI am mostly kidding here. But you get my drift. I create forms that WILL be appropriate design for the current user's resolution.

YES, you can do dynamic resizing of forms. And you can work out complex, but effective algorithnms that will works across all the controls on a forms...but it is a pain in the ass.

If you are talking CSSm then you ARE talking Word web pages, and you will lose me right away. I never ever use Word to make web pages. Ever. If you wnt to go that route, I am sure there are some people here that may help. But not me.

4. Colour on VBA forms. Yah, you can mess around with images blah blah blah. Go ahead. If you want to do it, it can be sort of fun, but it does not interest me at all. Waste of time to me. Far better to have good design, than pretty colours.

5. Mulitp-pages are great. They can be a little tricky to use, but once you get how to both navigate and retrieve information from them, they can organize extensive user input WAAAY better than large single page forms.

I will take a peek at the TabStop issue you mention. I'm not sure I totally follow - but I will take a look.

PS - my lord, I fell in love with Sonoma and Napa counties in California all over again. I go almost every year, but this year was incredible! So green. They had late rains and the wine country simply exploded into green in a space of three days. I had a great time playing music gigs at various places, and doing some DELICIOUS tasting! Yum Yum! The drive up Hwy 1 along the coast was spectacular, as usual...but there was....standing water at the side of Hwy 1. For anyone who has ever driven the coast road in Northern California, this is VERY odd.

[ANDREZ]

Thanks for this info as well.

OK, yes I am using a document cloned from a template. The 3 versions of a form to suit different resolutions is really interresting. Its not that applicable right now because we are moving users to 1024 x 768 and some of our laptops run even higher res. I'll definitely file that trick away for future use.

OK so resizing of forms can be done but I think I'll give it a miss at my level and for this project. That was only something I picked up from one of your earlier comments. The CSS reference was only by way of illustration of what I was thinking about when asking the question.

I agree about the importance of good design. My thoughts about the use of color was only as an aid for scanning or readability. Something I factor in when doing web work. Needless to say I share your lack of enthusiasm for Microsoft products in any part of web development.

Yep still interested in the multipage idea. This refers back to my concern with readability and useability. Thanks for looking at the tabstop issue too.

That aside I dont know the west coast at all but have fond memories of hitchhiking from NY to Texas and back again in the mid 70's. Not sure I'd be too keen to repeat that now though. Not sure what kind of music you do but I'm a banjo picker and have at times fantasised going over to the states and doing as tour of some of the Bluegrass festivals over there. Dream on.... though, Bush and his mates Blair and Howard might still completely stuff up international travel before I can make that fantasy happen. If you like vast + open spaces + unspoilt beaches and surf + great wine Oz is the place to be though!

Cheers,

Andrez

[fumei]

Here is a sample of using a multipage.

Interesting, I can upload a .DOC file, but not a .DOT file.

Anyway, it is a ZIP of a .DOT (template) file. Put it where ever you can get templates, and then File > New to create a new document. The form opens with Document_New. I did not put many controls on it. Three checkboxes on one Page, and four textboxes on the other Page. Note that the default name of pages ("Page") has been changed to something that makes sense.

The code I have been posting is still the same. I just added textbox text.

Again, this is putting values into formfields (with the shading removed BTW). You may want to not have formfields. You can put the values (the text values anyway) into range bookmarks. A little more code if you want the functionality of redoing the inputs. However, from the sounds of it, that will not be needed.

The point of this is a more simple design of your form - rather than one big honking form.

Other note: if you use a multi-page, and you want to make SURE all pages have been looked at, and appropriate inputs made, you will need to tighten up error trapping. or actually DO some error trapping.

[ANDREZ]

Now that looks cool! Okey Dokey, I'll play around with that multipage concept now that I have something to look at and understand. I can really appreciate the economy and efficiency of the design. If its OK with you I'd be interested in just geting some final help re the Tab fields problem and I reckon we can call it quits on this thread. One way or another I've learnt things about VBA that I didnt even know I didnt know! Thanks a lot Fumei.

Cheers,

Andrez

[fumei]

You are welcome.

[fumei]

Oh...what was the Tab problem? Hmmm, maybe use another post.

[ANDREZ]

As per the previous post

<I have set the tab order using TabIndex starting with the first text box as number 0 and so on to the last box. I have also set the TabStop to True. Unfortunately when I run the form the tabbing doesnt work and instead jumps to a field much later in the form. That field is definitely not numbered 1 (i.e. the field that I expected the cursor to jump to). >

As an example, if I place the cursor in the field labelled: Client Name. The Tab Index is 0 and tabstop is True. Hitting the tab key I would expect the tab to move to the second field labelled: Dept Location. The Tab Index for this field is 1 and tabstop is true. Instead the tab moves the cursor directly to the field labelled: Misc Notes which is Tab Index 24 and tabstop true. The cursor then remains in this field regardless of how many times I touch the tab key. I have checked each of the other fields to ensure that each is numbered in correct sequence. It appears that this is the case but the tabbing isnt doing its expected thing.

Happy to flick up the form again if you cant access it from the earlier post on 5/4/06.

Cheers,

Andre

[ANDREZ]

Hi Fumei,

Just wondering if you are still around to help me resolve the tab order issue as detailed in my previous post. If you've been away thats no drama. I still havent been able to work out what is happening as the numbering of the other tab fields seems in order.

Cheers,

Andrez

PS Am working on the multipage version based on the ideas you showed me earlier. Its a slow process but looking good!

[fumei]

Hi. Yeah send me the file again. ZIP it please, and either post here, or send it direct to me.

my_handle at telus dot net

[ANDREZ]

Hi,

Thanks for getting back on this. Here is the file once again along with info as per previous posts.

<I have set the tab order using TabIndex starting with the first text box as number 0 and so on to the last box. I have also set the TabStop to True. Unfortunately when I run the form the tabbing doesnt work and instead jumps to a field much later in the form. That field is definitely not numbered 1 (i.e. the field that I expected the cursor to jump to). >

As an example, if I place the cursor in the field labelled: Client Name. The Tab Index is 0 and tabstop is True. Hitting the tab key I would expect the tab to move to the second field labelled: Dept Location. The Tab Index for this field is 1 and tabstop is true. Instead the tab moves the cursor directly to the field labelled: Misc Notes which is Tab Index 24 and tabstop true. The cursor then remains in this field regardless of how many times I touch the tab key. I have checked each of the other fields to ensure that each is numbered in correct sequence. It appears that this is the case but the tabbing isnt doing its expected thing.

Cheers,

Andrez

[fumei]

As best as I can see, the tabs move correctly. I can't see them all of course because your userform is so big I can't see the whole thing. Nor can I scroll to do so.

But the tab moved correctly for each textbox in the tab order. Client name, Dept or Location, Client Phone, Client Email etc etc until it disappears from my monitor view.

I can not see any problem with the tabs.