+ Reply to Thread
Results 1 to 5 of 5

Automatically Display a data input form when active sheet changes

  1. #1
    Duncan
    Guest

    Automatically Display a data input form when active sheet changes

    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

  2. #2
    WillRn
    Guest

    RE: Automatically Display a data input form when active sheet changes

    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


  3. #3
    Duncan Edment
    Guest

    Re: Automatically Display a data input form when active sheet changes

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  4. #4
    WillRn
    Guest

    Re: Automatically Display a data input form when active sheet chan

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  5. #5
    Duncan Edment
    Guest

    Re: Automatically Display a data input form when active sheet chan

    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" <[email protected]> wrote in message
    news:[email protected]...
    >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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1