Results 1 to 2 of 2
Thread: Form-1st time
2001-01-28, 19:04 #1
- Join Date
- Jan 2001
- West Long Branch, New Jersey, USA
- Thanked 9 Times in 7 Posts
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"
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"
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"
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"
Private Sub goto_mortgage_form_Deactivate()
MsgBox "deloading goto mrtg form"
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.
2001-01-28, 21:57 #2
- Join Date
- Dec 2000
- Queanbeyan, New South Wales, Australia
- Thanked 0 Times in 0 Posts
Re: Form-1st time
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.
>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.