Excel 97, Windows 2k Pro

I have a custom toolbar with one button on it. Clicking the button executes
a macro. The macro calls a userform, and the userform does (err...will do)
a variety of things.

I am confused on where I need to put certain blocks of code. For instance,
when the form loads I would like it to do some basic things like declare
some variables that will be used by command buttons, load a list of column
headers into a one dimensional array, display a message box and establish
the range I will be working with. Here is an example of something I'd like
to happen when the form loads. As you can see the way it is now it happens
when I click on a command button:

Private Sub cmd3_typecheck_Click()

Dim Headers(100) as String

i = 0
For Each cell In Range("Database").Rows(1).Cells
i = i + 1
If i < 30 Then
Headers(i) = cell.Value
MsgBox "header " & i & ": " & cell.Value
End If
Next

End Sub

I can get all these things to work via command buttons, but i was hoping the
actual "loading" of the form was an event that could receive code. A
worksheet event maybe?

I also seem to be having a problem saving my work. Do forms and modules
need to be saved separately from the workbook? Does working with dbf files
in Excel present any special circumstances with regards to forms and macros?

All help appreciated, thanks!

-glenn-

In the VB editor if your right click on the user form,
select the menu option view code, you will see all the
userform events.

Lance
-----Original Message-----
Excel 97, Windows 2k Pro

I have a custom toolbar with one button on it. Clicking
the button executes
a macro. The macro calls a userform, and the userform
does (err...will do)
a variety of things.

I am confused on where I need to put certain blocks of
code. For instance,
when the form loads I would like it to do some basic
things like declare
some variables that will be used by command buttons,
load a list of column
headers into a one dimensional array, display a message
box and establish
the range I will be working with. Here is an example of
something I'd like
to happen when the form loads. As you can see the way
it is now it happens
when I click on a command button:

Private Sub cmd3_typecheck_Click()

Dim Headers(100) as String

i = 0
For Each cell In Range("Database").Rows(1).Cells
i = i + 1
If i < 30 Then
Headers(i) = cell.Value
MsgBox "header " & i & ": " & cell.Value
End If
Next

End Sub

I can get all these things to work via command buttons,
but i was hoping the
actual "loading" of the form was an event that could
receive code. A
worksheet event maybe?

I also seem to be having a problem saving my work. Do
forms and modules
need to be saved separately from the workbook? Does
working with dbf files
in Excel present any special circumstances with regards
to forms and macros?

All help appreciated, thanks!

-glenn-


.

The
Private Sub Userform_Initialize()

Event is used to perform initialization actions.

Private Sub Userform_Activate()

can be used in conjunction with initialize. If it doesn't work in
initialize, try using activate.

Intialize will only trigger when an instance of the form is created.
Activate could fire more frequently if the form is modeless, but if modal,
probably would not.

--
Regards,
Tom Ogilvy

Lance wrote in message
...
In the VB editor if your right click on the user form,
select the menu option view code, you will see all the
userform events.

Lance
-----Original Message-----
Excel 97, Windows 2k Pro

I have a custom toolbar with one button on it. Clicking
the button executes
a macro. The macro calls a userform, and the userform
does (err...will do)
a variety of things.

I am confused on where I need to put certain blocks of
code. For instance,
when the form loads I would like it to do some basic
things like declare
some variables that will be used by command buttons,
load a list of column
headers into a one dimensional array, display a message
box and establish
the range I will be working with. Here is an example of
something I'd like
to happen when the form loads. As you can see the way
it is now it happens
when I click on a command button:

Private Sub cmd3_typecheck_Click()

Dim Headers(100) as String

i = 0
For Each cell In Range("Database").Rows(1).Cells
i = i + 1
If i < 30 Then
Headers(i) = cell.Value
MsgBox "header " & i & ": " & cell.Value
End If
Next

End Sub

I can get all these things to work via command buttons,
but i was hoping the
actual "loading" of the form was an event that could
receive code. A
worksheet event maybe?

I also seem to be having a problem saving my work. Do
forms and modules
need to be saved separately from the workbook? Does
working with dbf files
in Excel present any special circumstances with regards
to forms and macros?

All help appreciated, thanks!

-glenn-


.

The UserForm_Initialize sub is the place to put much of this. Double click
the userform to go
to the code window. You'll be in the UserForm_Click event sub, so choose
Initialize from the drop down list above the code window on the right.

Use this section to establish rowsources for comboboxes and listboxes and
fill textboxes and set checkboxes and option boxes. These can often be
preset from the Properties Window at design time, but is a little easier to
maintain if everything is set at run time in the initialize sub.

As for your array of headers. If you need to use this array elsewhere in
the form
or in a sub in a module, you will need to extend the scope of this variable
by declaring
it as a Public variable. Do that at the top of the form's code if you are
using it in
buttons or listboxes or other controls on the form, like so:

Option Explicit
Public Headers(100) as String


If the Headers array needs to be processed in global subroutines in a Module
as well as in the form, put this at the top of the module code.

The Option Explicit is a common option that is often inserted automatically
by the Editor. It keeps you from using variables that haven't been properly
declared and helps reduce errors and bugs. Public variable declarations
come after options and before any code.

Thanks one and all! I had tried coding in the Userform Initialize section,
but it didn't seem to work. I will try again trying the Activate as Tom
suggested. Thanks!

-glenn-

"Bradley Dawson" wrote in message
...
The UserForm_Initialize sub is the place to put much of this. Double
click
the userform to go
to the code window. You'll be in the UserForm_Click event sub, so choose
Initialize from the drop down list above the code window on the right.

Use this section to establish rowsources for comboboxes and listboxes and
fill textboxes and set checkboxes and option boxes. These can often be
preset from the Properties Window at design time, but is a little easier
to
maintain if everything is set at run time in the initialize sub.

As for your array of headers. If you need to use this array elsewhere in
the form
or in a sub in a module, you will need to extend the scope of this
variable
by declaring
it as a Public variable. Do that at the top of the form's code if you are
using it in
buttons or listboxes or other controls on the form, like so:

Option Explicit
Public Headers(100) as String


If the Headers array needs to be processed in global subroutines in a
Module
as well as in the form, put this at the top of the module code.

The Option Explicit is a common option that is often inserted
automatically
by the Editor. It keeps you from using variables that haven't been
properly
declared and helps reduce errors and bugs. Public variable declarations
come after options and before any code.