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
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.
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)
If Len(Me.TextBox1.Text) = 0 Then
Me.MultiPage1.Value = 0
End If
End Sub
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
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.
Sorry, I meant Andy, not Andrew…
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?
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.
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
Nitin: I don’t know the cause. Even if MS let me see the code for Excel, I guarantee I wouldn’t understand it.
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.
This bug still seesm to be around in Excel 2007. I’m so grateful to have found this page.
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.
Surfed in here today, and I appreciate the help very much. Great work around — thank you very much for a solution to my problem!! More proof that Microsoft does not always have all the answers!
Thank you again,
Alan
This tip is great and I can kind of re-work my user form to use this command and achievce what I need. Thankyou.
However, ideally I would like to set the second page of my form only to be enabled when a certain check box on the first page is checked. Have tried a few things but no sucess. I’m very new to VBA so possibly I am making an ameteuristic mistake.
Thanks in Advance for any help.