Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 16th, 2007, 05:59 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default multipage activate

Hi,

I am using a multipage on one of my forms because I think they allow good navigation. My multipage is composed of 3 pages, and based upon information provided on the first two pages, I want to show a default value on a text box in the third page.

Does anyone know of a way that makes calculations and updates this text box if the tab of the third page is clicked on? I was thinking of using Private Sub MultiPage1_Change(), but this will make my program very slow because that means there will be calculations everytime something is changed.

Thank you very much!


  #2 (permalink)  
Old February 23rd, 2007, 03:21 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

You'd have to check it's value when the multipage was changed to a different page. Note that values start at 0 so a value of 0 would be page 1, 1 would be page 2, 2 would be page 3, etc.

I used the on click routine of the multipage itself but there are other ways to trigger it. It can be limited by exiting the sub for any page clicked that isn't the page you want.

For instance:

--------------------------------------------------
Private Sub MultiPage1_Click(ByVal Index As Long)

  If MultiPage1.Value <> 2 Then exit sub
  MsgBox "Yes, I will calculate"

End Sub
--------------------------------------------------

Hope this helps you.

  #3 (permalink)  
Old March 2nd, 2007, 02:55 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you allenm, but now I get an error message that I don't understand.

Here are bits and pieces of my code:


'These constants help with multipage navigation
Const mpProjDim As Integer = 0
Const mpOptions As Integer = 1


Private Sub MultiPage1_Change()
 With MultiPage1
   If .Value = mpOptions Then
        Call myFunction
   End If
 End With
End Sub

Sometimes the code works, but other times I get: "Compile Error: Constant expression required" - there's a problem with the line " .Value = mpOptions ", but I am not sure what. I don't get the error message if I declare mpOptions as a Dim and assign the value when the userform is loaded, but I thought it's stylistically better to declare it as Const.

  #4 (permalink)  
Old March 5th, 2007, 07:11 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Hmmm.... from what you've listed, that should work...
Where are you declaring the constant at? At the very top with Option Explicit?

You may also try declaring it in a module. Don't see why you should have to though.

Is there more than one place where you're declaring this? Could be the interpreter is getting confused. I'd really need more to go on. I'll try it with constant declaration myself later when I get the chance and post if I find anything odd.

  #5 (permalink)  
Old March 6th, 2007, 09:53 AM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I declared my constants at the top, right after Option Explicit. I think it might be a run-time error since sometimes I don't get the message. Thank you for your help.

  #6 (permalink)  
Old March 7th, 2007, 02:03 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

I pasted your code exactly making a myfunction that notified me that it was called and tried several times including with other things opened and never received an error running it. Not sure why, given just the code above, you're getting that error.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Activate the current window Probleminfinity Excel VBA 1 August 29th, 2007 04:44 PM
activate sheet mike0123m Excel VBA 1 August 11th, 2007 01:19 PM
Form Activate help mohiddin52 Access VBA 2 January 5th, 2007 12:35 PM
Activate or Deactivate controls on forms chacquard Access VBA 3 November 27th, 2006 08:03 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.