coding userforms with previous and next command buttons

[Kevin]

I'm developing a template that will present several userforms prompting users to input data. I'd like to provide users with the ability to move back and forth between the userforms, e.g., using command buttons labeled "previous" and "next." Additionally, if a userform was previously visited, and data entered, I'd like the userform to "remember" and set the textboxes, checkboxes, etc. accordingly

Thanks

Kevin

 

[Jonathan West]

I'm developing a template that will present several userforms prompting

users to input data. I'd like to provide users with the ability to move
back and forth between the userforms, e.g., using command buttons labeled
"previous" and "next." Additionally, if a userform was previously visited,
and data entered, I'd like the userform to "remember" and set the textboxes,
checkboxes, etc. accordingly.


Hi Kevin,

I would do this with a single userForm, but with the UserForm having a
MultiPage control with multiple pages on it, each disaplaying its own set of
controls.

Then your Next and Previous buttons can increment or decrement the Value
property of the Multipage, displaying the appropriate set of controls. A bit
of additional code would be necessary to disable the Previous button when
the Value property reaches 0, and disable the Next button when the value
property reaches Pages.Count-1.

The advantage of doing this rather than using multiple userforms is that the
contents of the controls are automatically preserved when you move from one
page of the multipage to the next.

There is just one limitation you should be aware of, if you have a very
large number of items to fill in. There is a hard limit of the number of
controls you can put on a UserForm. This limit is 411 controls of all types.
If you have a lot of pages, it is just about possible that you might hit
that limit.

 

[Kevin]

Thanks Jonathan. This is exactly what I needed to hear. I'm relatively new to VBA and I have yet to use the MultiPage or TabStrip contol. So, knowing that the contents of embedded controls (e.g., labels, textboxes, etc.) can be preseved automatically is a great relief. Although I expect I'll need no more than 30 pages (thanks for letting me know about the limit of 411), this automation will save me from having to handle tons of logic

Thanks also to Peter Hewett. His post on May 5th has helped me see that the TabStrip control is more flexible than the MultiPage control. I will be using the TabStrip control

Kevi

----- Jonathan West wrote: ----

I'm developing a template that will present several userforms promptin

users to input data. I'd like to provide users with the ability to mov
back and forth between the userforms, e.g., using command buttons labele
"previous" and "next." Additionally, if a userform was previously visited
and data entered, I'd like the userform to "remember" and set the textboxes
checkboxes, etc. accordingly


Hi Kevin

I would do this with a single userForm, but with the UserForm having
MultiPage control with multiple pages on it, each disaplaying its own set o
controls

Then your Next and Previous buttons can increment or decrement the Valu
property of the Multipage, displaying the appropriate set of controls. A bi
of additional code would be necessary to disable the Previous button whe
the Value property reaches 0, and disable the Next button when the valu
property reaches Pages.Count-1

The advantage of doing this rather than using multiple userforms is that th
contents of the controls are automatically preserved when you move from on
page of the multipage to the next

There is just one limitation you should be aware of, if you have a ver
large number of items to fill in. There is a hard limit of the number o
controls you can put on a UserForm. This limit is 411 controls of all types
If you have a lot of pages, it is just about possible that you might hi
that limit

 

[Jerry Bodoff]

Hi,

I have a question about Jonathan's response. If you have
a single UserForm with multipage and a lot of controls
wouldn't the code supporting the form tend to get "un-
manageable"? Also is there a limt to the amount of code
behind a form?

Jerry B.

 

[Jonathan West]

Hi,

I have a question about Jonathan's response. If you have
a single UserForm with multipage and a lot of controls
wouldn't the code supporting the form tend to get "un-
manageable"? Also is there a limt to the amount of code
behind a form?

There's no particular reason the code should get unmanageable. Generally,
with data capture forms like this, there are only a relatively small number
of controls that have event routines. They would be something like this.

The Click event of the Next button would increment the Value property of the
MultiPage

The Click event of the Previous button would decrement the Value property of
the MultiPage

The Change event of the Multipage would enable or disable the Next &
Previous buttons, depending on the current content of the Value property.

The UserForm_Initialize event would populate any listboxes & comboxes
present and so any other kind of initialization required.

There would probably be a Cancel button that unloads the form without doing
anything.

There would probably be an "OK" Button which would take the contents of all
the controls and write them to the appropriate locations in the document,
and then unload the form. That might be quite a long piece of code, but it
can be radically shortened by iterating though the Controls collection of
the Userform. If the target for the contents of each control is a bookmark,
then the bookmark name could be set to be the same as the name of the
control, or alternatively the bookmark name could be placed in the Tag
property of the control. In the latter case, you could use a loop like this

Dim oControl as Control
For Each oControl in Me.Controls
If Len(oControl.Tag) > 0 Then
ActiveDocument.Bookmarks(oControl.Tag).Range.InsertAfter oControl.Text
End If
Next oControl

The nice thing about that bit of code is that it ignores any control (such
as labels) where you have left the Tag property empty.



If there is a lot of code, then it may well be that there is duplication in
places, and some of the code can be taken out into a separate module and
called from the event procedures of the UserForm.

 

[Jerry Bodoff]

Hi Janathan,

Thanks for the reply. I was just wondering. I am
working on something now that involves a pile of "ini"
data files and I used multiple forms rather than
multipage. Each form processes a logical "ini" group. I
do see that for a relatively simple form multipage is the
way to go.

Jerry B.

-----Original Message-----



There's no particular reason the code should get unmanageable. Generally,
with data capture forms like this, there are only a relatively small number
of controls that have event routines. They would be something like this.

The Click event of the Next button would increment the Value property of the
MultiPage

The Click event of the Previous button would decrement the Value property of
the MultiPage

The Change event of the Multipage would enable or disable the Next &
Previous buttons, depending on the current content of the Value property.

The UserForm_Initialize event would populate any listboxes & comboxes
present and so any other kind of initialization required.

There would probably be a Cancel button that unloads the form without doing
anything.

There would probably be an "OK" Button which would take the contents of all
the controls and write them to the appropriate locations in the document,
and then unload the form. That might be quite a long piece of code, but it
can be radically shortened by iterating though the Controls collection of
the Userform. If the target for the contents of each control is a bookmark,
then the bookmark name could be set to be the same as the name of the
control, or alternatively the bookmark name could be placed in the Tag
property of the control. In the latter case, you could use a loop like this

Dim oControl as Control
For Each oControl in Me.Controls
If Len(oControl.Tag) > 0 Then
ActiveDocument.Bookmarks

(oControl.Tag).Range.InsertAfter oControl.Text

 

[Jonathan West]

Hi Janathan,

Thanks for the reply. I was just wondering. I am
working on something now that involves a pile of "ini"
data files and I used multiple forms rather than
multipage. Each form processes a logical "ini" group. I
do see that for a relatively simple form multipage is the
way to go.

A workable approach for that would be for the Tag property of a control to
code the ini file name, section name, and key name in three comma-separated
strings, and use the Split function to get the individual items.

 

[Jerry Bodoff]

Hi Jonathan,

I use your approach of control Tag for some of the
similar ini sections. I use a specific Form for the
others and a few sub procedures in a module.
The sub procedures accept the control name, data array
and size as arguments. Usually the control is a combo
box set up as a dropdown list. I can use the sub
procedure for any form. I have been programming a number
of years, usually mainframes, and have gotten into the
habit of using a lot of sub procs. That way the main
code is realtively simple and straight forward and the
logic is "self evident".

Jerry B.