Results 1 to 2 of 2

Thread: Form-1st time

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Form-1st time

    This is the first time I'm creating a user form in Excel. The form is only to be open when I'm on a particular sheet, whose name is "mtg schd". I want the form to open at the following times:
    1. when the workbook is opened if the active sheet="mtg schd"
    2. when switching to "mtg schd" from another sheet

    I want the form to close when
    3. the user was on sheetx and clicks another sheet
    4. clicks the form's X button
    5. clicks the form's close button which I added

    So I wrote the following subroutines:
    For 1. above, I wrote a workbook_open routine as:
    Private Sub Workbook_Open()
    If ActiveSheet.Name = "mtg schd" Then
    MsgBox "open at mtg schd sheet so need to activate form"
    Call forms.goto_mortgage_form_Activate
    End If
    End Sub

    For 2 above, I wrote a worksheet activate event associated with mtg_schd
    Private Sub Worksheet_Activate()
    MsgBox "activate mtg schd sheet so activate form"
    Call forms.goto_mortgage_form_Activate
    End Sub

    For 3., I wrote a worksheet deactivate event associated with the "mtg schd" code
    Private Sub Worksheet_Deactivate()
    MsgBox "deactivate mtg schd sheet so deactivate form"
    Call forms.goto_mortgage_form_Deactivate
    End Sub

    I don't think the code for 4 and 5 are needed to understand the problem I'm having so I'll leave them out for brevity.

    The code for activating and deactivating the user form is associated with the user form as follows:
    Private Sub goto_mortgage_form_Activate()
    MsgBox "loading goto mortg form"
    goto_mortgage_form.Show
    End Sub

    Private Sub goto_mortgage_form_Deactivate()
    MsgBox "deloading goto mrtg form"
    goto_mortgage_form.Hide
    End Sub

    When I run the form from the IDE, it seems to work OK. (More on that later.) But when I run in Excel (opening the workbook, switching back and forth between sheets), I get a run-time error in the routines for 1 and 2 above saying an "Object Required" and pointing to the call statements in 1 and 2 respectively. Likewise when I click to another sheet, I get the same error in routine 3.

    What object is VBA saying is required?

    Now as to what the form is trying to do.

    The "mtg schd" is a big form with lots of amortization tables. Rather than scrolling to find the one you want (bcs they can be in somewhat random order), I want the form to allow the user to click on a property and the sheet to scroll to that property's table (at least the first row of it).

    I've provided a list box on the form with the names based on a range where all names are listed in one place. I've provided a "go now" button which is supposed to "goto" the cell (there is a list of cells in another range right next to the property list). (The user can also invoke the goto action by double clicking the name in the list or selecting a name and hitting enter.)

    Next question: once I have the name that's selected (which I have seen in a Msgbox), how do I get the address from the corresponding cell in the list of cells.

    Last item: The form also has an X button (as mentioned) and a Close button in case the user wants to get the form out of the way even while on the "mtg schd" sheet. These work fine.

    Last Question: if the user closes the form, how can he/she reopen it without having to click to another sheet and click back on the "mtg schd" sheet? Do I need a button on the toolbar or maybe in the sheet which calls the activate sheet code?

    Thanks much in advance.

    Fred

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form-1st time

    Fred,

    You've got a big question there- forgive me if I can't answer everything.

    >I get a run-time error in the routines for 1 and 2 above saying an "Object Required" and pointing to the call statements in 1 and 2 respectively

    Sometimes the error being pointed at occurs within the routine. So, althought it's pointing at a line which says "call", it may be occurring within "forms.goto_mortgage_form_Activate". Do you know how to step through VBA code? If not,we can provide instructions.

    >once I have the name that's selected (which I have seen in a Msgbox), how do I get the address from the corresponding cell in the list of cells.

    try:
    msgbox Excel.Names("name").RefersTo

    >Do I need a button on the toolbar or maybe in the sheet which calls the activate sheet code?

    Either will work. It might be easier to have it in the toolbar where it's always visible. You can make it always visible in the worksheet too- you have to freeeze panes.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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