BUG: MultiPage Controls on 2003
Sue brought this one to my attention. Changing the Value property of a MultiPage control in the Change event, causes the MultiPage to show incorrect information. To reproduce, follow these steps:
- Create a Userform
- Add a MultiPage control with 2 pages
- Add a TextBox to the first page of the MultiPage
- Add a CommandButton to the second page
- Put this code in the Change event
Private Sub MultiPage1_Change()
If Len(Me.TextBox1.Text) = 0 Then
Me.MultiPage1.Value = 0
End If
End Sub
Now run the form. This is what I get
Then switch pages without putting anything in the TextBox
It’s on Page1, but the contents of Page2 are showing. Strange. Here’s what I have for a workaround, but it’s not very pretty. In a standard module, put this
Public Sub chMultiVal()
UserForm1.MultiPage1.Value = 0
End Sub
and change the Change event to this
Private Sub MultiPage1_Change()
If Len(Me.TextBox1.Text) = 0 Then
Application.OnTime Now + TimeSerial(0, 0, 0), “ChMultiVal”
End If
End Sub
Andy Miller:
I’m not sure if this is a bug or not, but, in VBA, you can choose the Application.WorksheetFunctions.VLookup function. However, when trying to use it, it returns the Run-Time Error 4004 - Unable to get the VLookup property of the Worksheetfunction class.
20 July 2004, 10:44 amAndy Pope:
Assuming Sue is trying to prevent selection of Page 2 until the textbox on Page 1 contains some text you could try this alternative code.
Private Sub MultiPage1_Click(ByVal Index As Long)
20 July 2004, 4:20 pmIf Len(Me.TextBox1.Text) = 0 Then
Me.MultiPage1.Value = 0
End If
End Sub
Sue:
Dick, this works great and I appreciate it so much…thanks for taking the time out…I am going to send this link to others that I know are having the same problem…Sue
22 July 2004, 8:20 amJuan Pablo G:
Andrew,
I don’t think that’s a bug. But, if you use Application.Vlookup instead, it doesn’t create an error like that, it just returns the xlErrNa error.
22 July 2004, 9:20 amJuan Pablo G:
Sorry, I meant Andy, not Andrew…
22 July 2004, 9:23 amAndy Miller:
Speaking of bugs… I was just curious: since you’re an MVP, do you have the authority/status/ability to report bugs to Microsoft and have them actually pay attention?
26 July 2004, 3:42 pmDick:
Andy: We have a contact person (called a Lead) at Microsoft to whom we can report bugs. I don’t think we get any special treatment though. The only benefit that I’ve ever seen is that she reads the messages and frankly I wouldn’t know where else to submit them. I looked around microsoft.com for a bug submission page, but came up empty.
26 July 2004, 5:25 pmNitin:
I have been trying to solve this issue for the past week and this solution ROCKS. Thank you for posting the solution to this issue. Do you know the reason behind this issue or what causing it.
Thank you
13 September 2004, 4:32 pmDick:
Nitin: I don’t know the cause. Even if MS let me see the code for Excel, I guarantee I wouldn’t understand it.
14 September 2004, 9:42 pmDave:
You’re a friggin’ genius! I’ve been pulling out what little hair I have left for two days over this one. BTW, this wasn’t a bug in Excel 2000, but cropped up in XP and 2003.
Way to think outside the box!
Thank you, thank you, thank you.
4 May 2005, 8:47 amDarren:
This bug still seesm to be around in Excel 2007. I’m so grateful to have found this page.
2 March 2007, 8:35 pmChris:
I have been struggling with this problem for over a week now.
All of the code I wrote using Excel 2000 suddenly fell over when I “Up”graded to Excel 2003…
I used Andy Pope’s suggestion of using the click event instead of the change event and its all working again.
I can only guess that its an intentional fix in Excel 2003, preventing people from writing code which changes the multipage value, which is triggered by the multipage_change() event.
I suppose its conceivable that the code could fall into the chain of:
multipage_change() -> multipage.value = X -> multipage_change() -> multipage.value = X -> …
And maybe theres something in Excel 2003 preventing this from happening.
Anyway, my problem is fixed.
15 August 2007, 6:53 am