I have a spreadsheet that contains four separate sheets, with each sheet
having in excess of 29 fields. Rather than the user inputing information
into a field, and then pressing TAB to move to the next field, and so on, I
am attempting to utilise the Date | Form option. My hope was to display an
input form listing all fields, so that the user could simply enter / view
records in this manner, rather than using the sheet. However, all four
sheets within the spreadsheet, contain different headings.

So, my question is as follows:

Is it possible, via a macro or VB code, to display a form listing the sheets
fields, whenever the focus changes to the form.

i.e. When sheet 1 is displayed, show a data entry form for entering the
information that is contained on Sheet one only. Then, if the user changes
to sheet 4, the data entry form would change to show only those fields on
sheet.


Hope someone can help.

Many thanks

Iassume you are talking about the "Data Form" Option under the data menu. If
you want the option to pop up each time the sheet is activated attach this
code to the
"WorkBook _SheetActivate" Event:

ActiveSheet.ShowDataForm

And the Data Form will show each time a different sheet is selected. Your
user will have to close the dialog box first however.

Hope this helps,

WillRn

"Duncan" wrote:

I have a spreadsheet that contains four separate sheets, with each sheet
having in excess of 29 fields. Rather than the user inputing information
into a field, and then pressing TAB to move to the next field, and so on, I
am attempting to utilise the Date | Form option. My hope was to display an
input form listing all fields, so that the user could simply enter / view
records in this manner, rather than using the sheet. However, all four
sheets within the spreadsheet, contain different headings.

So, my question is as follows:

Is it possible, via a macro or VB code, to display a form listing the sheets
fields, whenever the focus changes to the form.

i.e. When sheet 1 is displayed, show a data entry form for entering the
information that is contained on Sheet one only. Then, if the user changes
to sheet 4, the data entry form would change to show only those fields on
sheet.


Hope someone can help.

Many thanks

WillRn,

Yes, that is what I want to happen. Unfortunately, using your
instructions, I can't get it to work.

I can't find an entry for the "WorkBook _SheetActivate" event, nor can I
work out where to place any VBA code. In the VBA view, I have the
following tree:

VBAProject
Microsoft Excel Objects
Sheet 1
Sheet 2
ThisWorkbook

As I cannot find an entry specific to the event in question, I am unsure
as to where to place the event code. I have tried in so far in all
three locations under Microsoft Excel Objects, but so far, none have
worked.

Can you give me more info?

Many thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove NO_SPAM.
"WillRn" wrote in message
...
Iassume you are talking about the "Data Form" Option under the data
menu. If
you want the option to pop up each time the sheet is activated attach
this
code to the
"WorkBook _SheetActivate" Event:

ActiveSheet.ShowDataForm

And the Data Form will show each time a different sheet is selected.
Your
user will have to close the dialog box first however.

Hope this helps,

WillRn

"Duncan" wrote:

I have a spreadsheet that contains four separate sheets, with each
sheet
having in excess of 29 fields. Rather than the user inputing
information
into a field, and then pressing TAB to move to the next field, and so
on, I
am attempting to utilise the Date | Form option. My hope was to
display an
input form listing all fields, so that the user could simply enter /
view
records in this manner, rather than using the sheet. However, all
four
sheets within the spreadsheet, contain different headings.

So, my question is as follows:

Is it possible, via a macro or VB code, to display a form listing the
sheets
fields, whenever the focus changes to the form.

i.e. When sheet 1 is displayed, show a data entry form for entering
the
information that is contained on Sheet one only. Then, if the user
changes
to sheet 4, the data entry form would change to show only those
fields on
sheet.


Hope someone can help.

Many thanks

I understand, I'll try to do it step by step. Forgive me if you already know
a lot of this but I will start at the very beginning and walk through it.
here it is:

1. Open the Excel workbook that you wish to work on.
2. Ensure the Visual Basic Menu is displayed by going to "View" then
"Toolbars" and ensure the Visual Basic Tool Bar option has a check mark in
front of it.
3. On the Visual Basic Menu click the "Visual Basic Editor" button and this
will bring up Visual Basic.
4. In the window that is titled "Project - VBA Project" double click on the
words "ThisWorkbook."
5. In the right hand side a window will appear with two drop down boxes. The
one on the left side says "(General)" and the one on the right side says
"(Declarations)".
Click the drop down box on the "(General)" side and select "WorkBook." The
following text will appear below:

Private Sub Workbook_Open()

End Sub

And Right hand window will now display "Open"

6. On this right hand window click the drop down box and select
"SheetActivate." The window below the two drop down boxes will look like this:

Private Sub Workbook_Open()

End Sub
----------------------------------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

7. Just under the "Private Sub Workbook_SheetActivate(ByVal Sh As Object)"
line type the following:

ActiveSheet.ShowDataForm

Your window below the drop down boxes should look like this now:

Private Sub Workbook_Open()

End Sub
------------------------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.ShowDataForm
End Sub

8. Now go back to the Excel Workbook and click on one of the worksheet tabs
at the bottom. The Data Form box should appear. Keep in mind that the Data
Form will only work up to a total of

9. Save your project.

Keep the following in mind:

A. Before you can use a data form to add a record to a new range or list,
the range or list must have labels at the top of each column. Microsoft Excel
uses these labels to create fields on the form.

B. Data forms can display a maximum of 32 fields at one time.

C. While you are adding or changing a record, you can undo changes by
clicking Restore as long as the record is the active record in the data form.

D. If you change a record that contains a formula, the formula is not
calculated until you press ENTER or click Close to update the record.

Let me know if it worked,

WillRn

"Duncan Edment" wrote:

WillRn,

Yes, that is what I want to happen. Unfortunately, using your
instructions, I can't get it to work.

I can't find an entry for the "WorkBook _SheetActivate" event, nor can I
work out where to place any VBA code. In the VBA view, I have the
following tree:

VBAProject
Microsoft Excel Objects
Sheet 1
Sheet 2
ThisWorkbook

As I cannot find an entry specific to the event in question, I am unsure
as to where to place the event code. I have tried in so far in all
three locations under Microsoft Excel Objects, but so far, none have
worked.

Can you give me more info?

Many thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove NO_SPAM.
"WillRn" wrote in message
...
Iassume you are talking about the "Data Form" Option under the data
menu. If
you want the option to pop up each time the sheet is activated attach
this
code to the
"WorkBook _SheetActivate" Event:

ActiveSheet.ShowDataForm

And the Data Form will show each time a different sheet is selected.
Your
user will have to close the dialog box first however.

Hope this helps,

WillRn

"Duncan" wrote:

I have a spreadsheet that contains four separate sheets, with each
sheet
having in excess of 29 fields. Rather than the user inputing
information
into a field, and then pressing TAB to move to the next field, and so
on, I
am attempting to utilise the Date | Form option. My hope was to
display an
input form listing all fields, so that the user could simply enter /
view
records in this manner, rather than using the sheet. However, all
four
sheets within the spreadsheet, contain different headings.

So, my question is as follows:

Is it possible, via a macro or VB code, to display a form listing the
sheets
fields, whenever the focus changes to the form.

i.e. When sheet 1 is displayed, show a data entry form for entering
the
information that is contained on Sheet one only. Then, if the user
changes
to sheet 4, the data entry form would change to show only those
fields on
sheet.


Hope someone can help.

Many thanks

WillRn,

My apologies for the delay in getting back to you. After following your
detailed instructions, it did work. However, I have found that the
command does not always work when the workbook is opened, and that I
manually have to set the cursor to below the headings, and issue the
Data | Form command.

After playing around with it though, I managed to get things to work,
slightly better, albeit not how I wanted it.

Many thanks for your help and detailed reply.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove NO_SPAM.
"WillRn" wrote in message
...
I understand, I'll try to do it step by step. Forgive me if you already
know
a lot of this but I will start at the very beginning and walk through
it.
here it is:

1. Open the Excel workbook that you wish to work on.
2. Ensure the Visual Basic Menu is displayed by going to "View" then
"Toolbars" and ensure the Visual Basic Tool Bar option has a check
mark in
front of it.
3. On the Visual Basic Menu click the "Visual Basic Editor" button and
this
will bring up Visual Basic.
4. In the window that is titled "Project - VBA Project" double click
on the
words "ThisWorkbook."
5. In the right hand side a window will appear with two drop down
boxes. The
one on the left side says "(General)" and the one on the right side
says
"(Declarations)".
Click the drop down box on the "(General)" side and select "WorkBook."
The
following text will appear below:

Private Sub Workbook_Open()

End Sub

And Right hand window will now display "Open"

6. On this right hand window click the drop down box and select
"SheetActivate." The window below the two drop down boxes will look
like this:

Private Sub Workbook_Open()

End Sub
----------------------------------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

7. Just under the "Private Sub Workbook_SheetActivate(ByVal Sh As
Object)"
line type the following:

ActiveSheet.ShowDataForm

Your window below the drop down boxes should look like this now:

Private Sub Workbook_Open()

End Sub
------------------------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.ShowDataForm
End Sub

8. Now go back to the Excel Workbook and click on one of the worksheet
tabs
at the bottom. The Data Form box should appear. Keep in mind that the
Data
Form will only work up to a total of

9. Save your project.

Keep the following in mind:

A. Before you can use a data form to add a record to a new range or
list,
the range or list must have labels at the top of each column.
Microsoft Excel
uses these labels to create fields on the form.

B. Data forms can display a maximum of 32 fields at one time.

C. While you are adding or changing a record, you can undo changes by
clicking Restore as long as the record is the active record in the
data form.

D. If you change a record that contains a formula, the formula is not
calculated until you press ENTER or click Close to update the record.

Let me know if it worked,

WillRn

"Duncan Edment" wrote:

WillRn,

Yes, that is what I want to happen. Unfortunately, using your
instructions, I can't get it to work.

I can't find an entry for the "WorkBook _SheetActivate" event, nor
can I
work out where to place any VBA code. In the VBA view, I have the
following tree:

VBAProject
Microsoft Excel Objects
Sheet 1
Sheet 2
ThisWorkbook

As I cannot find an entry specific to the event in question, I am
unsure
as to where to place the event code. I have tried in so far in all
three locations under Microsoft Excel Objects, but so far, none have
worked.

Can you give me more info?

Many thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove NO_SPAM.
"WillRn" wrote in message
...
Iassume you are talking about the "Data Form" Option under the data
menu. If
you want the option to pop up each time the sheet is activated
attach
this
code to the
"WorkBook _SheetActivate" Event:

ActiveSheet.ShowDataForm

And the Data Form will show each time a different sheet is
selected.
Your
user will have to close the dialog box first however.

Hope this helps,

WillRn

"Duncan" wrote:

I have a spreadsheet that contains four separate sheets, with each
sheet
having in excess of 29 fields. Rather than the user inputing
information
into a field, and then pressing TAB to move to the next field, and
so
on, I
am attempting to utilise the Date | Form option. My hope was to
display an
input form listing all fields, so that the user could simply enter
/
view
records in this manner, rather than using the sheet. However, all
four
sheets within the spreadsheet, contain different headings.

So, my question is as follows:

Is it possible, via a macro or VB code, to display a form listing
the
sheets
fields, whenever the focus changes to the form.

i.e. When sheet 1 is displayed, show a data entry form for
entering
the
information that is contained on Sheet one only. Then, if the
user
changes
to sheet 4, the data entry form would change to show only those
fields on
sheet.


Hope someone can help.

Many thanks