PDA

View Full Version : Solved: Accessing form buttons from sheets



hiflier
12-31-2008, 04:11 PM
I have a project with one sheet for each month, plus a Recap sheet. On each month sheet there are several areas, for Electric bills, Water bills, Deposits, etc. I am using a popup form for a menu with buttons to move to various areas on a given sheet. I want to provide a button to go, say, from the Deposit area on a Month sheet (say, APR) directly to the Recap sheet, then return to the Electric area of the APR sheet when they hit the Electric button. The problem is that the variable I am trying to use to save the current Month sheet when they hit the Recap button comes back empty when it goes from the form to the sheet and back. Hopefully that's clear. Thanks for your help.

lucas
12-31-2008, 05:44 PM
Clean up a copy of your file......take all personal info etc. out and attach it to a post so we can see what you are doing.

hit post reply at the bottom left of the last post and when the page loads add your message and then scrool down and look for a button that says "manage attachments"

I'm wondering why a floating nav menu or something like that wouldn't work better. Are you open to suggestions or locked in on what you are doing?

hiflier
12-31-2008, 06:48 PM
Here is a downsized version. You call the form by double-clicking on any cell. The form has buttons for moving around. It works fine on any given sheet, but this is the problem: if you start on Feb looking at Bills, for example, then go to the Annual sheet, then go back to Bills, I want it to go back to Feb, but it goes to the Jan sheet instead. The Bills button code looks like this (and don't worry about my ego - I'm here to learn):

Sub btnBills_Click()
MyMonth = ActiveSheet.Name ' This is the sheet the cursor is on
If MyMonth = "Deposit Slip" Or MyMonth = "Annual" Then ' if on a non-month sheet going to a month sheet,
' use MyOldMonth to hold name of month to return to
If MyOldMonth = "" Then MyOldMonth = "Jan" ' If you started on a non-month sheet, use Jan for return
Sheets(MyOldMonth).Select

End If

MyOldMonth = ActiveSheet.Name

Range("A1").Select
End Sub

lucas
12-31-2008, 07:48 PM
I'm going to post an example for you to check out. It might be just what you are looking for after looking at your file.

Let me know if you like it and if you have trouble setting it up.

It's just a suggestion so if you would rather stick with buttons.......well.

hiflier
01-01-2009, 02:00 AM
Lucas - I like your nav menu, but I can't wrap my head around how I could use it. My issue is that I have 12 monthly sheets, each of which has 6 areas of focus, from billing for that month, to monthly deposits, monthly electric meter readings, etc. I also have 5 other sheets, for loan amortization, annual reconciliation, etc. If I read your menu correctly, I'd need 6 entries for each of the monthly sheets, or 72 entries on the listbox just for the monthly sheets. The buttons all work, except that when I go from a monthly sheet to the reconciliation sheet and then want to go back, it can't find the monthly sheet it came from. I believe it's a matter of using correct references for moving from a form to a sheet and back again. My form is called UserForm1, and the monthly sheets are Jan, Feb, etc. If it's not doable, I'll go with your nav menu. And thanks for providing that. I learned a lot from it.

lucas
01-01-2009, 06:51 AM
The buttons all work, except that when I go from a monthly sheet to the reconciliation sheet and then want to go back, it can't find the monthly sheet it came from.

I guess I'm confused because I don't see a reconciliation sheet.

can you give the steps that cause your problem from opening the sheet until you run into trouble?

hiflier
01-01-2009, 11:21 AM
Thanks for your patience. Here's the test. Open the Oakwood file. Change the Tab to Feb. Double click anywhere on the sheet to get the menu to come up. Hit Deposits. That moves you to the deposits area (actually in this case you're already there, but this is for purpose of this test) and it sets the variable MyOldMonth = "Feb", the sheet you're on. Now hit the "Annual" button, which moves the tab to Annual. Now hit Deposits again. It goes to the deposits area as directed, but now it's on the Jan tab instead of returning to the Feb tab where I want it.

I've chased this down, and when it goes from Deposits to Annual, MyOldMonth (which had just been = "Feb" when it left Deposits), now is empty. Do you have to dimension buttons as public or global to get the variable contents to move with you? Thanks again for your help with this.

lucas
01-01-2009, 12:03 PM
Try this:

go to sheet feb.. double click to open the userform.....select annual and then after you get annual click on the deposit button. It should go back to feb...

close the userform and selec sheet jan and do the same steps...it should go back to jan.

If this seems like what you are trying to do then we have a start in the right direction.


Remember.....there is no other error checking, etc. in this so far.....just trying to get it to do what you require, then we can clean it up.

hiflier
01-01-2009, 12:21 PM
Lucas - You probably won't believe this, but I thought I could declare public variables on the This Workbook sheet. When I moved the declaration Public MyOldSheet to a module, it worked perfectly. So my problem is solved.

I am intrigued by your coding which you sent earlier. Is there a reference for vba that you would recommend? I haven't seen Intersect, CurrentRange, and other items on your nav form before. Thanks again for all of your help. Dave

lucas
01-01-2009, 12:29 PM
That's funny Dave, I have to beg hangover lenience on this one. I added code to your thisworkbook module and noticed the declarations but it didn't click........I should have spotted that.

As for your last question, just use the search at the very top of the page....the google one and select vba express to search. You might add union to your list.

glad you got it sorted. Another quick question, you select range A1 and then scroll to get where you want. Why not just use the correct range?

hiflier
01-01-2009, 12:59 PM
<Another quick question, you select range A1 and then scroll to get where you want. Why not just use the correct range?>

I didn't know how to do that. I found this code, and still don't know exactly how it works, but it does.

I tried to select the upper left cell, thinking that would define the visual area, but it doesn't.

If you have any hints on this, I'd like to know about it. Thanks again.

lucas
01-01-2009, 01:13 PM
Try this:
Sub btnDeposits_Click()
MyMonth = ActiveSheet.Name
If MyMonth = "Deposit Slip" Or MyMonth = " Recon" Or MyMonth = "Tenants" Or MyMonth = "Calc" Or MyMonth = "Annual" Then
If MyOldMonth = "" Then MyOldMonth = "Jan"
Sheets(MyOldMonth).Select
End If
MyOldMonth = ActiveSheet.Name
Range("A45").Select
End Sub

hiflier
01-01-2009, 02:07 PM
When I do that, A45 ends up in the middle of the left side. The upper left corner is A31. I want A45 to be the upper left corner cell. The scrolling code at the bottom of the subroutine works to do that, but if there is an easier way...

lucas
01-01-2009, 02:29 PM
This should do it.
Sub btnDeposits_Click()
MyMonth = ActiveSheet.Name
If MyMonth = "Deposit Slip" Or MyMonth = " Recon" Or MyMonth = "Tenants" Or MyMonth = "Calc" Or MyMonth = "Annual" Then

If MyOldMonth = "" Then MyOldMonth = "Jan"
Sheets(MyOldMonth).Select

End If

MyOldMonth = ActiveSheet.Name
Application.Goto Reference:=Range("A45"), _
Scroll:=True
End Sub

hiflier
01-01-2009, 02:38 PM
Thanks, Lucas. That works like a charm. Thanks for all of your help.
Dave