Adding tabstrip and runtime

[Michael J. Malinsky]

I have the following code that adds a TabStrip and tabs at runtime. This is
the basic form, since the number of tabs will be determined by data on a
worksheet. What I'm having trouble with is how to refer to the tabs later
in the code. In my example, I end up with TabStrip1 with 11 tabs
(.Value=0-10) named Tab1 through Tab11.

Private Sub UserForm_Initialize()

Set MyCtrl = UserForm1.Controls.Add("Forms.TabStrip.1", "TabStrip1")

For x = 2 To 10
Set MyNewTab = MyCtrl.Add("Tab" & x + 1, "Tab" & x + 1, x)
Next x


End Sub

When I try to refer to the TabStrip or the tabs, I get an Object Required
error. What would be the proper way to refer to these items after created
at runtime?

TIA

 

[Richard Choate]

Is it possible for you to create the complete tab strip in advance and then
just change what is visible at runtime instead of building them at runtime?
Excel/VBA is going to assign names to the tabs when you do that and it won't
be all that clean the way you are doing it.
HTH
Richard Choate

I have the following code that adds a TabStrip and tabs at runtime. This is
the basic form, since the number of tabs will be determined by data on a
worksheet. What I'm having trouble with is how to refer to the tabs later
in the code. In my example, I end up with TabStrip1 with 11 tabs
(.Value=0-10) named Tab1 through Tab11.

Private Sub UserForm_Initialize()

Set MyCtrl = UserForm1.Controls.Add("Forms.TabStrip.1", "TabStrip1")

For x = 2 To 10
Set MyNewTab = MyCtrl.Add("Tab" & x + 1, "Tab" & x + 1, x)
Next x


End Sub

When I try to refer to the TabStrip or the tabs, I get an Object Required
error. What would be the proper way to refer to these items after created
at runtime?

TIA

 

[Michael J. Malinsky]

The number of tabs depends on the number of rows in a particular range. The
number of rows could change which is the reason for my previous question.
It sounds like your suggestion is to create a tabstrip with, say, 100 tabs
then only make a certain number visible based on my range, right?

 

[Richard Choate]

No, I wouldn't go to that extreme. The question was worth asking, though,
just in case. I use a tabstrip in a Word project, and there I refer to the
tabs with this syntax: i = TabStrip1.SelectedItem.Index
I find out what the index number is and then I do a select case with the
index number and call a different macro from a normal module depending on
what the index number is. When you refer to a tabstrip in a normal module,
you have to refer specifically to the userform first, like
userform1.tabstrip1.blah blah blah
HTH
Richard Choate

The number of tabs depends on the number of rows in a particular range. The
number of rows could change which is the reason for my previous question.
It sounds like your suggestion is to create a tabstrip with, say, 100 tabs
then only make a certain number visible based on my range, right?

 

[Richard Choate]

After reading my response to you, I thought I should clarify a little. In my
case, I tested to find which tab had been selected, and then I acted on it.
The index is the tab number, and in my opinion, the easiest way to refer to
the tabs, which was the basis of your question. In your situation, I realize
that you may not care about the selected tab and only care about how to
refer to the tabs. Therefore, I'll just say that I would refer to their
index numbers.
HTH
Richard Choate

No, I wouldn't go to that extreme. The question was worth asking, though,
just in case. I use a tabstrip in a Word project, and there I refer to the
tabs with this syntax: i = TabStrip1.SelectedItem.Index
I find out what the index number is and then I do a select case with the
index number and call a different macro from a normal module depending on
what the index number is. When you refer to a tabstrip in a normal module,
you have to refer specifically to the userform first, like
userform1.tabstrip1.blah blah blah
HTH
Richard Choate

The number of tabs depends on the number of rows in a particular range. The
number of rows could change which is the reason for my previous question.
It sounds like your suggestion is to create a tabstrip with, say, 100 tabs
then only make a certain number visible based on my range, right?