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