If field is emptie disable alle fields after that field

[Edwin Verwer]

I have a form with 8 rows of 5 objects.
Namely a combobox | textbox | combobox | combobox | textbox

These objects are named:
combobox (1 to 8) textbox (1 to 8) combobox (1 to 8) and so on.

All the rows except for the first row of objects are disabled.
What i want in this form is to force users to fill in the first row before
going to the next row. (this also coints for the rows that come after the
first one)
So if all the 5 fields in the first row are filled with data ( <> "" ) the
next row will be enabled.
But also if the data in one of the prefiouce fields (if more rows are filled
with data)
is deleted that all the rows that come after that empty field will have
their data put in a array, the field of those rows made empty and then have
those fields disabled until that emptie field is filled again.

If that empty field is filled again the fields have to be enabled again and
the data from the array has to be put back in the fields.

I know that this is possible with if statements but then i will get a LOT of
code.
What is the best (or better) way to solfe this?

 

[Graham Mayor]

If each row has the same type of fields, I think you might find it easier to
add rows as required. You can do that by running the following macro on exit
from the last form field in the row. It is also possible to validate each
form field for content - http://www.gmayor.com/formfieldmacros.htm - though
I have not yet explored the possibility of combining the two macros.

Sub AddARow()
'Run on exit from the last form field in
'the last row of the table
Dim oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim oCell As Range
Dim oLastCell As Range
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer
Dim CurRow As Integer
Dim i As Long
Set oDoc = ActiveDocument
With oDoc
.Unprotect Password:="" 'Unprotect document
Set oTable = .Tables(1) 'Select the appropriate table
iRow = oTable.Rows.Count 'Record the last row number
iCol = oTable.Columns.Count 'Record the last column number
Set oLastCell = oTable.Cell(iRow, iCol).Range 'Record the last cell
sResult = oLastCell.FormFields(1).Result 'Get the value in the last cell
Set oRng = oTable.Rows(iRow).Range 'Add the last row to a range
oRng.Copy 'Copy the row
oRng.Collapse wdCollapseEnd 'collapse the range to its end.
oRng.Select 'the end of the table
Selection.Paste 'Paste the row at the end of the table
CurRow = iRow + 1 'Record the new last row
For i = 1 To iCol 'Repeat for each column
Set oCell = oTable.Cell(CurRow, i).Range 'process each cell in the
row
oCell.FormFields(1).Select 'Select the first field in the cell
With Dialogs(wdDialogFormFieldOptions) 'and name it
.name = "Col" & i & "Row" & CurRow 'eg Col1Row2
.Execute 'apply the changes
End With
Next i
'Select the formfield in the last cell of the previous row
oLastCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Exit = "" 'and remove the exit macro
.Execute 'apply the changes
'but note that this clears the value from the cell
End With
oLastCell.FormFields(1).Result = sResult 'so restore the result of the
cell
.Protect NoReset:=True, Password:="", _
Type:=wdAllowOnlyFormFields 'Reprotect the form
.FormFields("Col1Row" & CurRow).Select 'and select the next field to be
completed
End With
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Edwin Verwer]

I have'nt created a form in a word document but in the vba.
It is a form that is loaded at the startup of the document.
I want users to fill in the form and then if ok is presst all the
information will be loaded/writen in the document self.
I'm pretty far with this but now i have this little problem.
Can you help me with this?

 

[fumei via OfficeKB.com]

I think you will have to write the actual logic to make this work. However,
you may be able to tighten up the code by replacing the LOTS of IF..Then
statements with careful use of Select Case.

 

[fumei via OfficeKB.com]

You may also want to consider redesigning the userform. BTW, it is always
helpful when posting to forums to be explicit about things. "forms" and
"form" is very vague unfortunately. You are talking about a userform,
therefore SAY userform.

You could - and this is just an example of a design - have a wee
commandbutton beside each row, with the caption "More" (or whatever).

Clicking it would test back across "its" row. If it validates OK, then it
makes the next row of controls available. If not, it states the row is
incorrect (and the next row is never seen).

Further, each commandbutton could also (since you seem to require such logic)
test back across all previous rows. Again, it a row does not validate
correctly, then boom!, the existing data in after rows can be stored, but
then those rows made to disappear.

if you do this, then obviously the "next" (or "More") commandbutton also
would have to check against any pre-existing data applicable to "its" row.

 

[fumei via OfficeKB.com]

BTW: if you did use such a "row control", I see no need to store the
previous entered data, as you are just putting it back in again. If a
previous control is changed to empty, then you just make the rows after
disappear. When the naughty control has data put back in, you make the after
rows appear again.

You may also want to consider redesigning the userform. BTW, it is always
helpful when posting to forums to be explicit about things. "forms" and
"form" is very vague unfortunately. You are talking about a userform,
therefore SAY userform.

You could - and this is just an example of a design - have a wee
commandbutton beside each row, with the caption "More" (or whatever).

Clicking it would test back across "its" row. If it validates OK, then it
makes the next row of controls available. If not, it states the row is
incorrect (and the next row is never seen).

Further, each commandbutton could also (since you seem to require such logic)
test back across all previous rows. Again, it a row does not validate
correctly, then boom!, the existing data in after rows can be stored, but
then those rows made to disappear.

if you do this, then obviously the "next" (or "More") commandbutton also
would have to check against any pre-existing data applicable to "its" row.

I think you will have to write the actual logic to make this work. However,
you may be able to tighten up the code by replacing the LOTS of IF..Then

[quoted text clipped - 6 lines]

 

[Edwin Verwer]

Sorry if i was i bit unclear (and wrongly using te term form and userform.)
I thought i was being clear enough.

I'll try and explain it again.

I have a userform.
On this userform i have several objects.
the first objects are combobox, textbox, combobox, combobox and a textbox
which are standing beside each other.
under these objects i have again a combobox ,textbox, combobox, combobox and
a textbox in the same way as the previouce 5 objects.
This is repeated 6 more times creating a total of 8 times 5 objects.
(Those 5 objects standing beside each other(combobox, textbox, combobox,
combobox and textbox) are the rows that i was refering to in my previouce
post. So no row controls are being used.)

The naming method i have used for these objects is simple.
The objectname + the number of the row thy stand in (No row control)
So thy are numberd 1 to 8 from top to bottem.

so the first row of 5 objects is enabled and the following 7 lines/rows of 5
objects are disabled.

This is something i have done so that users have to first fill in those 5
objects before the next line/row of 5 objects is enabled. This on it self is
easy but now i want to build in a check in case i have multiple lines/row
filled in with date and somewhere halfway or at the beginning one of the
objects (combobox or textbox) is made empty.

If this is the case then i want that 3 things are done.
1 have the content of the objects that come after the object that is made
empty put in a array. (objects from the same row/line as the object that is
made empty are excluded)
2 make the content of those objects from which the data has been put in a
array made empty ( me.combox.value = "" or textbox of course )
3 disabling these objects.

But it also has to have a check built in that checks if that field that was
made empty is filled again.

In this case i want those objects that where disabled to be enabled again.
And the data that was put in the array be put back in the place where thy
came from.

I hope this is a bit cleared then my previoce post.

And I hope that someone can help me with this.

Preferably with code because I have just started with VBA again and I want
to learn from how others do it.

Thanks in advance

 

[Doug Robbins - Word MVP on news.microsoft.com]

It might help to know what the purpose of the form would be.

It sounds like something where I would probably use only one set of the
combobox, textbox, combobox, combobox and textbox and in addition have a
listbox, and command button to add a record to the listbox containing the
information that was entered into the combobox, textbox, combobox, combobox
and textbox, another command button that could be used to edit the data for
a record that was selected in the listbox, another for deleting a selected
record in the listbox, etc.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Edwin Verwer]

The purpuse of this user form is the following.

The objects i was refering to in my previouce post was a part of a bigger
whole.
This userform is going to be used for a company that puts logo's on clotihing.

The userform first contains a Name/adres/place section where the info of a
customer is put. (Customer / name comany / phonenumber / e-mail / current
date / when it has to be ready and who filled in the userform)

Then there comes a section where product information (mainly clothing) can
be filled in to.
This is the section i was refering to.
In the first combobox you can select the type of product.
(trousers/ polos / sweaters and so on)
in the following textbox you can put a discription of the product.
in the following combobox you can select the size of the product (L XL XXL)
then the next combobox you can select in what colour it has to be and then
the last textbox you can put a number indicating how many of that product you
want.

After that there is a section where can notify if you want a logo on it.
Material/ colour / font / file name / where you want it.

And the last section is a big multirow textbox for extra
descriptions/remarkts or just some extra info.

And at the bottem there are three command buttons with the text.
"Remove all" (for making all the fields emtpy)
"Cancel" (for stopping without putting something in the document and closing
the active document.)
"OK" (for putting all the info that has be filled in the userform in to the
active document)

I hope this helps

 

[Doug Robbins - Word MVP on news.microsoft.com]

It sounds to me like my suggestion would be the way to do it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com