Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform causing a slip for Worksheet activate?
I have a userform that works perfectly the first time it's used, but
the Worksheet Activate for the worksheet(s) isn't firing when the command button on the userform is used to return the user to the previous worksheet - regardless of which worksheet they chose first. Should I use a toggle button instead? I don't know how to code a toggle button to toggle between two worksheets. Should I have something in Deactivate for the Userform? Am I completely off target? REAL ESTATE INDEX WORKSHEET Private Sub Worksheet_Activate() BuildingsForm.Show vbModeless Application.ErrorCheckingOptions.BackgroundCheckin g = False With ActiveSheet If .Columns("d:e").Hidden = False Then .Columns("d:e").Hidden = True .Columns("f:f").Hidden = False End If End With End Sub USERFORM Private Sub UserForm_Activate() Call AddMinBox If ActiveSheet.Name = "Real Estate Index" Then cmdBldgTab.Visible = True togEditParty.Visible = True cmdRealEstateTab.Visible = False End If If ActiveSheet.Name = "Buildings" Then cmdBldgTab.Visible = False togEditParty.Visible = False cmdRealEstateTab.Visible = True End If End Sub COMMAND BUTTONS Private Sub cmdBldgTab_Click() 'Visible when Real Estate Index worksheet is active Sheets("Buildings").Select Range("A1").Select End Sub Private Sub cmdRealEstateTab_Click() 'Visible when Buildings worksheet is active Sheets("Real Estate Index").Select Range("B1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform causing a slip for Worksheet activate?
It is working for me but I had to comment out lines below because I didn't
have these objects ' Call AddMinBox If ActiveSheet.Name = "Real Estate Index" Then ' cmdBldgTab.Visible = True ' togEditParty.Visible = True ' cmdRealEstateTab.Visible = False Try commenting out the same lines and see if it works. It may be something in the AddMinBox routing that is causing the problem. " wrote: I have a userform that works perfectly the first time it's used, but the Worksheet Activate for the worksheet(s) isn't firing when the command button on the userform is used to return the user to the previous worksheet - regardless of which worksheet they chose first. Should I use a toggle button instead? I don't know how to code a toggle button to toggle between two worksheets. Should I have something in Deactivate for the Userform? Am I completely off target? REAL ESTATE INDEX WORKSHEET Private Sub Worksheet_Activate() BuildingsForm.Show vbModeless Application.ErrorCheckingOptions.BackgroundCheckin g = False With ActiveSheet If .Columns("d:e").Hidden = False Then .Columns("d:e").Hidden = True .Columns("f:f").Hidden = False End If End With End Sub USERFORM Private Sub UserForm_Activate() Call AddMinBox If ActiveSheet.Name = "Real Estate Index" Then cmdBldgTab.Visible = True togEditParty.Visible = True cmdRealEstateTab.Visible = False End If If ActiveSheet.Name = "Buildings" Then cmdBldgTab.Visible = False togEditParty.Visible = False cmdRealEstateTab.Visible = True End If End Sub COMMAND BUTTONS Private Sub cmdBldgTab_Click() 'Visible when Real Estate Index worksheet is active Sheets("Buildings").Select Range("A1").Select End Sub Private Sub cmdRealEstateTab_Click() 'Visible when Buildings worksheet is active Sheets("Real Estate Index").Select Range("B1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform causing a slip for Worksheet activate?
On Sep 12, 1:39*am, Joel wrote:
It is working for me but I had to comment out lines below because I didn't have these objects ' * * * * Call AddMinBox * * * * * * *If ActiveSheet.Name = "Real Estate Index" Then *' * * * cmdBldgTab.Visible = True *' * * * togEditParty.Visible = True *' * * * cmdRealEstateTab.Visible = False Try commenting out the same lines and see if it works. *It may be something in the AddMinBox routing that is causing the problem. " wrote: I have a userform that works perfectly the first time it's used, but the Worksheet Activate for the worksheet(s) isn't firing when the command button on the userform is used to return the user to the previous worksheet - regardless of which worksheet they chose first. Should I use atogglebutton instead? *I don't know how to code a togglebutton totogglebetween two worksheets. *Should I have something in Deactivate for the Userform? *Am I completely off target? REAL ESTATE INDEX WORKSHEET * *Private Sub Worksheet_Activate() * * * * BuildingsForm.Show vbModeless * * * * Application.ErrorCheckingOptions.BackgroundCheckin g = False * * * * * * *With ActiveSheet * * * * * *If .Columns("d:e").Hidden = False Then * * * * * * * * .Columns("d:e").Hidden = True * * * * * * * * .Columns("f:f").Hidden = False * * * * * * * * * End If * * * * * * * End With * *End Sub USERFORM * *Private Sub UserForm_Activate() * * * * Call AddMinBox * * * * * * If ActiveSheet.Name = "Real Estate Index" Then * * * * * *cmdBldgTab.Visible = True * * * * * *togEditParty.Visible = True * * * * * *cmdRealEstateTab.Visible = False * * * * * * End If * * * * * * *If ActiveSheet.Name = "Buildings" Then * * * * * *cmdBldgTab.Visible = False * * * * * *togEditParty.Visible = False * * * * * *cmdRealEstateTab.Visible = True * * * * * * *End If * *End Sub COMMAND BUTTONS * *Private Sub cmdBldgTab_Click() * *'Visible when Real Estate Index worksheet is active * * * * * Sheets("Buildings").Select * * * * * Range("A1").Select * *End Sub * *Private Sub cmdRealEstateTab_Click() * *'Visible when Buildings worksheet is active * * * * * Sheets("Real Estate Index").Select * * * * * Range("B1").Select * *End Sub- Hide quoted text - - Show quoted text - I still can't get it to work. I've even run the Clean Project code stripper on it. As much as I hate to, I think I'm going to resort back to having separate user forms on the two worksheets. Groan. Thanks for taking a look though. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform causing a slip for Worksheet activate?
I think you should give it one more shot. And I don't mean to take out a gun
and shoot your computer. I think something else in your workbook is causing a problem. why don't you do exactly what I did and start from scratch. Put the code that you posted in a new spreadsheet. Take the code from your posting so it is exactly the same as what I did. Comment out the same lines I did. Then run the code and see if it works. Then slowly add the rest of your code until it stops working. Another thing you can try is to add break point into your code. Put a break point at the SUB statement in each of your functions by clink with the mouse on the line and then pressing F9. The line will turn yellow red. Then run your code by pressing F5. When a break point is reached the code will stop and turn the line yellow. Then press F5 again to continue or F8 to step through the code. I suspect the workheet change is working but something else is failing. If you have any ON Error stements in your code comment them out during debugging or change the setting in VBA menu tools - Options - General - Error Trapping. the ON Error statements could be masking the real cause of the problem. " wrote: On Sep 12, 1:39 am, Joel wrote: It is working for me but I had to comment out lines below because I didn't have these objects ' Call AddMinBox If ActiveSheet.Name = "Real Estate Index" Then ' cmdBldgTab.Visible = True ' togEditParty.Visible = True ' cmdRealEstateTab.Visible = False Try commenting out the same lines and see if it works. It may be something in the AddMinBox routing that is causing the problem. " wrote: I have a userform that works perfectly the first time it's used, but the Worksheet Activate for the worksheet(s) isn't firing when the command button on the userform is used to return the user to the previous worksheet - regardless of which worksheet they chose first. Should I use atogglebutton instead? I don't know how to code a togglebutton totogglebetween two worksheets. Should I have something in Deactivate for the Userform? Am I completely off target? REAL ESTATE INDEX WORKSHEET Private Sub Worksheet_Activate() BuildingsForm.Show vbModeless Application.ErrorCheckingOptions.BackgroundCheckin g = False With ActiveSheet If .Columns("d:e").Hidden = False Then .Columns("d:e").Hidden = True .Columns("f:f").Hidden = False End If End With End Sub USERFORM Private Sub UserForm_Activate() Call AddMinBox If ActiveSheet.Name = "Real Estate Index" Then cmdBldgTab.Visible = True togEditParty.Visible = True cmdRealEstateTab.Visible = False End If If ActiveSheet.Name = "Buildings" Then cmdBldgTab.Visible = False togEditParty.Visible = False cmdRealEstateTab.Visible = True End If End Sub COMMAND BUTTONS Private Sub cmdBldgTab_Click() 'Visible when Real Estate Index worksheet is active Sheets("Buildings").Select Range("A1").Select End Sub Private Sub cmdRealEstateTab_Click() 'Visible when Buildings worksheet is active Sheets("Real Estate Index").Select Range("B1").Select End Sub- Hide quoted text - - Show quoted text - I still can't get it to work. I've even run the Clean Project code stripper on it. As much as I hate to, I think I'm going to resort back to having separate user forms on the two worksheets. Groan. Thanks for taking a look though. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform causing a slip for Worksheet activate?
On Sep 12, 2:35*pm, Joel wrote:
I think you should give it one more shot. *And I don't mean to take out a gun and shoot your computer. I think something else in your workbook is causing a problem. *why don't you do exactly what I did and start from scratch. *Put the code that you posted in a new spreadsheet. *Take the code from your posting so it is exactly the same as what I did. *Comment out the same lines I did. *Then run the code and see if it works. Then slowly add the rest of your code until it stops working. Another thing you can try is to add break point into your code. *Put a break point at the SUB statement in each of your functions by clink with the mouse on the line and then pressing F9. *The line will turn yellow red. *Then run your code by pressing F5. *When a break point is reached the code will stop and turn the line yellow. *Then press F5 again to continue or F8 to step through the code. I suspect the workheet change is working but something else is failing. *If you have any ON Error stements in your code comment them out during debugging or change the setting in VBA menu tools - Options - General - Error Trapping. *the ON Error statements could be masking the real cause of the problem.. " wrote: On Sep 12, 1:39 am, Joel wrote: It is working for me but I had to comment out lines below because I didn't have these objects ' * * * * Call AddMinBox * * * * * * *If ActiveSheet.Name = "Real Estate Index" Then *' * * * cmdBldgTab.Visible = True *' * * * togEditParty.Visible = True *' * * * cmdRealEstateTab.Visible = False Try commenting out the same lines and see if it works. *It may be something in the AddMinBox routing that is causing the problem. " wrote: I have a userform that works perfectly the first time it's used, but the Worksheet Activate for the worksheet(s) isn't firing when the command button on the userform is used to return the user to the previous worksheet - regardless of which worksheet they chose first.. Should I use atogglebutton instead? *I don't know how to code a togglebutton totogglebetween two worksheets. *Should I have something in Deactivate for the Userform? *Am I completely off target? REAL ESTATE INDEX WORKSHEET * *Private Sub Worksheet_Activate() * * * * BuildingsForm.Show vbModeless * * * * Application.ErrorCheckingOptions.BackgroundCheckin g = False * * * * * * *With ActiveSheet * * * * * *If .Columns("d:e").Hidden = False Then * * * * * * * * .Columns("d:e").Hidden = True * * * * * * * * .Columns("f:f").Hidden = False * * * * * * * * * End If * * * * * * * End With * *End Sub USERFORM * *Private Sub UserForm_Activate() * * * * Call AddMinBox * * * * * * If ActiveSheet.Name = "Real Estate Index" Then * * * * * *cmdBldgTab.Visible = True * * * * * *togEditParty.Visible = True * * * * * *cmdRealEstateTab.Visible = False * * * * * * End If * * * * * * *If ActiveSheet.Name = "Buildings" Then * * * * * *cmdBldgTab.Visible = False * * * * * *togEditParty.Visible = False * * * * * *cmdRealEstateTab.Visible = True * * * * * * *End If * *End Sub COMMAND BUTTONS * *Private Sub cmdBldgTab_Click() * *'Visible when Real Estate Index worksheet is active * * * * * Sheets("Buildings").Select * * * * * Range("A1").Select * *End Sub * *Private Sub cmdRealEstateTab_Click() * *'Visible when Buildings worksheet is active * * * * * Sheets("Real Estate Index").Select * * * * * Range("B1").Select * *End Sub- Hide quoted text - - Show quoted text - I still can't get it to work. *I've even run the Clean Project code stripper on it. *As much as I hate to, I think I'm going to resort back to having separate user forms on the two worksheets. *Groan. Thanks for taking a look though.- Hide quoted text - - Show quoted text - OMG. I am a total freaking idiot. I had .show in my deactivate worksheet instead of hide. I've spent hours reading it for what it should say instead of what it did say. Thank you thank you thank you for talking me off the ledge. Have a wonderful weekend. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password userform activate | Excel Discussion (Misc queries) | |||
How can I activate scrollbars in an own userform? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming | |||
button to activate userform | Excel Programming | |||
Userform.Activate | Excel Programming |