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" <WillRn@discussions.microsoft.com> wrote in message
news:BD1ABE3D-AE5C-43C8-8E2E-8CC82014C7E0@microsoft.com...
>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" <WillRn@discussions.microsoft.com> wrote in message
>> news:D7C35240-E265-4277-A7BD-FD0B1D5A08BB@microsoft.com...
>> > 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
>>
>>
>>