capturing TabStrip events inside an MsForm

[Strobo]

Hi,

Im trying to display results from a database query in a sort of a 'form'.
I need it to be displayed on the actual worksheet, not as a separate window
as would be the case when using the UserForm...so Im using an MSFrame
(Microsoft Forms 2.0 Frame) directly on the worksheet and I'm putting all my
info in there.

Now,
In order to display different sets of data in different tabs Im trying to
use a TabStrip (the form looks the same for all the sets)...

The problem I am having is how to capture the TabStrip events when the
TabStrip is located inside an MsFrame...
If it was an UserForm it would be as simple as

Private Sub TabStrip1_Change()

but in the case of the Frame I cannot access the controls directly.
I generally access them using

Me.frame1.Controls("TabStrip1")

So, any ideas how to go about this?

Thanks!!!

ps. Using Excel 2003

 

[Chip Pearson]

I would use a Multipage control rather than a TabStrip control. You
can access the MultiPage's events normally, regardless of whether it
is in a frame.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Strobo]

I guess I can, but then I have to have a copy of the entire form for each
page...with tabstrip for each tab I could simply update the fields with the
new values.

Anyway, I'll look into this...

But I still welcome any other ideas!!!

Thanks Chip!

I would use a Multipage control rather than a TabStrip control. You
can access the MultiPage's events normally, regardless of whether it
is in a frame.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi,

Im trying to display results from a database query in a sort of a 'form'.
I need it to be displayed on the actual worksheet, not as a separate window
as would be the case when using the UserForm...so Im using an MSFrame
(Microsoft Forms 2.0 Frame) directly on the worksheet and I'm putting all my
info in there.

Now,
In order to display different sets of data in different tabs Im trying to
use a TabStrip (the form looks the same for all the sets)...

The problem I am having is how to capture the TabStrip events when the
TabStrip is located inside an MsFrame...
If it was an UserForm it would be as simple as

Private Sub TabStrip1_Change()

but in the case of the Frame I cannot access the controls directly.
I generally access them using

Me.frame1.Controls("TabStrip1")

So, any ideas how to go about this?

Thanks!!!

ps. Using Excel 2003

.

 

[Ed Ferrero]

Hi Strobo,

Any reason you are not using a pivot table for this?

Ed Ferrero
www.edferrero.com

 

[Peter T]

If you are really intent on putting your Tabstrip in the Frame you can trap
its events in a 'WithEvents' class
Eg, code in Class1 and a normal module as indicated, run StartEvents
(assumes TabStrip1 is *embedded* in Frame1)

''' code in Class1
Public WithEvents tabStrip As MSForms.tabStrip

Private Sub tabStrip_Change()
With tabStrip
MsgBox .Tabs(.Value).Caption
End With
End Sub

''' end Class1

'' code in a normal module
Private cls1 As Class1

Sub StartEvents()
Dim oleF As OLEObject

Set cls1 = New Class1
Set oleF = ActiveSheet.OLEObjects("Frame1")
Set cls1.tabStrip = oleF.Object.Controls("TabStrip1")

End Sub

Sub StopEvents()
Set cls1 = Nothing
End Sub

I would have thought though Chip Pearson's suggestion is simpler, or group
all controls within a drawing shape, formatted to look like a frame.

Regards,
Peter T