Dynamically switching tabs in a tabstrib from a command button

[Annodomini2]

Continuing from my previous post, I have been attempting to add buttons to a Tabstrip which will switch the current tab to the next tab or back to the previous tab.

I have tried the following without much success:

TabStrip1.Tabs(i).SetFocus (where i is the next TabIndex Value)

Or

TabStrip1.TabIndex = i

Neither work, can anyone shed any light on where I am going wrong?

TIA

Anno

[Annodomini2]

Figured it out,

TabStrip1.Value = i, Seems so obvious when you know the answer!!! LOL

[Norie]

Try using the Value property.

For example this will cycle through the tabs.

TabStrip1.Value = (TabStrip1.Value + 1) Mod TabStrip1.Tabs.Count

[Annodomini2]

Thanks for the reply, so that will return to the start if you were to use just a next button?

Maybe I missed something in the definition of a TabStrip or the implementation in my window, I assumed that the Tabs' structure would be consistent, i.e. have the same type of controls present on each tab, but the data present on each tab would be unique, but I seem to be having a problem of the data being modified on a tab is modifiying it on all tabs.

Do I need to link the controls to the tab sheet or apply a setting which differentiates the data from one tab to the next?

[Norie]

Are the controls really on/in the tabs?

How did you actually add them?

Perhaps you would be better off with a MultiPage control?

This is from Help.



In contrast to a Page, a Tab does not contain any controls. Controls that appear within the region bounded by a TabStrip are contained on the form, as is the TabStrip.

[Annodomini2]

I didn't want to use a multipage control as the layout of each page is unique, I wanted a copy of the original page so that I just had to effectively draw (in the form editor) and then make a copy of the original tab, but from your post I am guessing that this control does not retain the data present in the boxes and I would need to use an array of global data to store the information and change the boxes content according to the tab switch.

Unless you know of an easy way to copy a page of a multipage including the controls? As far as I can tell, you need to add the page and recreate the controls I was looking to avoid this, if possible.

I am attempting to dynamically add and remove pages/tabs as and when required for the document.

Its word 2000 btw.

[Norie]

A Tab doesn't actually contain any controls.

At least that's what Help appears to be saying.

[Annodomini2]

Yes, I understand that now, however I have one of 2 options, use multipage, but I need to know how to copy all the controls from the 1st page to a new page. When dynamically creating new pages.

Or use Tabstrip, with an array (same size as the number of tabs) and keep the data in the array, updating the display depending on the current tab. I currently don't know how to declare global vairables in VBA, excluding arrays.

[Annodomini2]

Yes, I understand that now, however I have one of 2 options, use multipage, but I need to know how to copy all the controls from the 1st page to a new page. When dynamically creating new pages.

Or use Tabstrip, with an array (same size as the number of tabs) and keep the data in the array, updating the display depending on the current tab. I currently don't know how to declare global vairables in VBA, excluding arrays.

[Annodomini2]

Yes, I understand that now, however I have one of 2 options, use multipage, but I need to know how to copy all the controls from the 1st page to a new page. When dynamically creating new pages.

Or use Tabstrip, with an array (same size as the number of tabs) and keep the data in the array, updating the display depending on the current tab. I currently don't know how to declare global vairables in VBA, before we get to arrays.

[Norie]

Do you actually need to 'copy' the controls?

Can't you just create new controls when you create the new tab?

Of course it will be more complicated to code.

Perhaps you could create the new controls by referring to the properties
of the existing ones.

[Annodomini2]

Do you actually need to 'copy' the controls?

Can't you just create new controls when you create the new tab?

Of course it will be more complicated to code.

Perhaps you could create the new controls by referring to the properties
of the existing ones.

I would like the layout to be the same for the user.

Adding controls I would believe i not too difficult (haven't got that far yet apart from adding tabs to a tabstrip)

How would I aquire the properties of the control and then apply them to the newly created one? i.e. size position etc.

While I could quite happily code this in C++ (probably be quicker at this rate ;)) I am still a bit of a noob when it comes to VBA.

[Norie]

Any chance you could add an example workbook to play with?:)

It's been a while since I've looked at something like this and I don't
really have time to mock up an example.

[Norie]

Check this code. I created in Excel, but I think the principle should be the same in Word.


Dim pgNew As MSForms.Page
Dim ctl As MSForms.Control
Dim ctlNew As MSForms.Control
Set pgNew = MultiPage1.Pages.Add

For Each ctl In MultiPage1.Pages(pgNew.Index - 1).Controls
Set ctlNew = pgNew.Controls.Add("Forms." & TypeName(ctl) & ".1")
With ctlNew
.Height = ctl.Height
.Left = ctl.Left
.Top = ctl.Top
.Width = ctl.Width
End With
Next
Yep, just tested, it works in word too.

[Annodomini2]

Practicing with Word XP at home, got it copying controls, Thanks :)

However its not copying the captions for the labels, any idea how to aquire this property?

Works with BackColor property even though its not listed but not caption. Any idea how we could Identify the control and copy this specific property?

See if we can write a generic function for people to use? Setup a library?

Also for Pictures

[Annodomini2]

Typename should give us the type of object if so, we could add special conditions for particular type i.e. labels pictures etc.

[Norie]

So what code are you actually using?

[Annodomini2]

typename isn't returning the object type, need to find a function that will

[Annodomini2]

So what code are you actually using?

Straight copy of yours

[Annodomini2]

typename isn't returning the object type, need to find a function that will

Sorry incorrect, rechecking....

[Norie]

Well in the test I did I used just textboxes and a combobox and it worked fine.

Any chance you can attach a file with what you are working on?

Also, we've not seen any of your code yet.:)

[Annodomini2]

Got it... :) I think!!!

Private Sub CommandButton1_Click()
Dim pgNew As MSForms.Page
Dim ctl As MSForms.Control
Dim ctlNew As MSForms.Control
Dim lbl As MSForms.Label
Dim lblNew As MSForms.Label
Dim type1 As String
Set pgNew = MultiPage1.Pages.Add


For Each ctl In MultiPage1.Pages(pgNew.Index - 1).Controls
Set ctlNew = pgNew.Controls.Add("Forms." & TypeName(ctl) & ".1", ctl.Name, True)

With ctlNew
.Name = ctl.Name
.BackColor = ctl.BackColor
.Height = ctl.Height
.Left = ctl.Left
.Top = ctl.Top
.Width = ctl.Width
End With

'type1 = TypeName(ctl) 'Debug

If TypeName(ctl) = "Label" Or TypeName(ctl) = "OptionButton" Or TypeName(ctl) = "CheckBox" Then MultiPage1.Pages(pgNew.Index).Controls(ctl.Name).Caption = MultiPage1.Pages(0).Controls(ctl.Name).Caption
If TypeName(ctl) = "Image" Then MultiPage1.Pages(pgNew.Index).Controls(ctlNew.Name).Picture = MultiPage1.Pages(0).Controls(ctl.Name).Picture
Next
End Sub

[Annodomini2]

How to get a headache doesn't appear to be copying the names and giving each control a different name, GRR! So the functions I am declaring for a given control are not applicable on the next page.

Will continue my investigation as to why, but what a pain in the backside! :(

[Annodomini2]

Well according to the name property in object on the copied page it has the name defined as with the one copied from the previous page, however, VBA is not referencing macros for that control when that action takes place.

e.g. I have a ComboBox control on the Multipage, which is copied fine, If you check the name (using the type1 debug section, but change the input to ctlNew.Name, I have also checked this by number index on the form from a different control) they appear to be the same. However I have a cboFunction_change macro defined. This works fine for the 1st page but all subsequent pages ignore this operation. Any ideas where I may be going wrong?

Thinking about this is it possible to dynamically aquire events? e.g. change

[Norie]

Could you please attach an example of what you are doing?

If you have event code for existing controls it will not be triggered by
new controls.

[Annodomini2]

Sorry for double post

[Annodomini2]

I would attach the document but its too big!! how I created it:

I have an effectively blank document containing the following objects:

1. A Bookmark named ADDEDTEXT
2. UserForm named UserForm1
3. CommandButton named cmdAdd (caption add)
4. CommandButton named cmdOk (caption OK)
5. Multipage named MultiPage1
Containing:
1. Combobox named cboBox
2. Label named Label 1 (bit irrelevent)
3. TextBox named TextBox1


Using the following code:
Private Sub cboBox_Change()
Dim i As Integer

i = MultiPage1.Value
With MultiPage1.Pages(i)

If .Controls("cboBox").Value = "Not assigned" Then Exit Sub

If .Controls("cboBox").Value = "Custom" Then .Controls("TextBox1").Enabled = True

If .Controls("cboBox").Value = "Assign1" Then
.Controls("TextBox1").Enabled = True
.Controls("TextBox1").Value = "Inserted Text"
.Controls("TextBox1").Enabled = False
End If
End With
End Sub
Private Sub cmdAdd_Click()
Dim pgNew As MSForms.Page
Dim ctl As MSForms.Control
Dim ctlNew As MSForms.Control
Dim lbl As MSForms.Label
Dim lblNew As MSForms.Label
Dim type1 As String

Set pgNew = MultiPage1.Pages.Add("Page" & (MultiPage1.Pages.Count + 1))

For Each ctl In MultiPage1.Pages(pgNew.Index - 1).Controls
Set ctlNew = pgNew.Controls.Add("Forms." & TypeName(ctl) & ".1", ctl.Name, True)

With ctlNew
.Name = ctl.Name
.BackColor = ctl.BackColor
.Height = ctl.Height
.Left = ctl.Left
.Top = ctl.Top
.Width = ctl.Width
End With

'type1 = TypeName(ctl) 'Debug

If TypeName(ctl) = "Label" Or TypeName(ctl) = "OptionButton" Or TypeName(ctl) = "CheckBox" Or TypeName(ctl) = "CommandButton" Or TypeName(ctl) = "Frame" _
Then MultiPage1.Pages(pgNew.Index).Controls(ctl.Name).Caption = MultiPage1.Pages(0).Controls(ctl.Name).Caption
If TypeName(ctl) = "Image" Then MultiPage1.Pages(pgNew.Index).Controls(ctlNew.Name).Picture = MultiPage1.Pages(0).Controls(ctl.Name).Picture
Next

With MultiPage1.Pages(pgNew.Index).Controls("cboBox")
.AddItem "Not assigned"
.AddItem "Custom"
.AddItem "Assign1"
.Value = "Not assigned"
End With
MultiPage1.Pages(pgNew.Index).Controls("TextBox1").Enabled = False
End Sub
Private Sub cmdOk_Click()
Dim i As Integer

For i = 0 To MultiPage1.Pages.Count - 1
With MultiPage1.Pages(i)
If .Controls("cboBox").Value = "Not assigned" Then
MsgBox "Form incomplete", , "Not all text added"
Exit Sub
End If

If .Controls("TextBox1").Value = Null Or .Controls("TextBox1").Value = "" Then
MsgBox "Text Box on Page" & (i + 1) & " incomplete", , "Not all text added"
Exit Sub
End If

ActiveDocument.Bookmarks("ADDEDTEXT").Range.Text = .Controls("TextBox1").Value
End With
Next i
Unload Me
End Sub
Private Sub UserForm_Initialize()
With MultiPage1.Pages(0).Controls("cboBox")
.AddItem "Not assigned"
.AddItem "Custom"
.AddItem "Assign1"
.Value = "Not assigned"
End With
End Sub

[Norie]

This code is not 'copying' controls, it's creating new controls and setting thier properties based on the existing controls.

[Annodomini2]

This code is not 'copying' controls, it's creating new controls and setting thier properties based on the existing controls.

Yes I understand this, therefore is it possible to dynamically assign events? :)

E.g. while creating a ComboBox, somehow you define the event change to point to a given function?

[Norie]

Yes, but it would be rather complicated and would involve either directly writing code with code or using class modules.

Would it only be the change event for the combobox?

Can you not WinZip the file and attach it?

In fact why not just strip everything apart from the userform out of the file and attach that.

[Annodomini2]

Yes, but it would be rather complicated and would involve either directly writing code with code or using class modules.

Do you have an example?


Would it only be the change event for the combobox?

No, my full document has other controls which would require a change event, if it was a standard substitute, the Assign1 option, a modify tick box is present to allow the user to change the standard text. Plus others.


Can you not WinZip the file and attach it?

Ahh, better, see attached.

[Norie]

See the attached.

I've added a class module and other code to deal with the change event for each combobox.

[Annodomini2]

Thank you very much, if you're ever in Cheltenham, I'll buy you a pint!! :)

I will have a look at this tonight and check that I understand it correctly, I will attempt one for the check box and post it back here if I get it working or not.

[Annodomini2]

Apologies for the late reply, other commitments kept me away.

Anyway here is my modified file: See attached.

I have a few further questions:

1. I understand that the following statement ties the function declaration to the event handler, but what is the purpose of 'Option explicit'?

Option Explicit
Public WithEvents CheckBoxGroup As MSForms.CheckBox

2. With the following code, am I right in assuming that the Set command assigns the specified control to the event handler controls which have been defined?

3. Additionally with the following code, what is the purpose of the preserve operation? Am right in assuming it makes this property read-only once set?

ReDim Preserve ComboBoxes(0)
Set ComboBoxes(0).ComboGroup = MultiPage1.Pages(0).Controls("cboBox")


4. If I were to have several instances of a control on a form or several, forms, pages etc, which were intended to have a specified actions for particular events. I would define the action in the module specified as per example, now my question, if i were to define a different specific event function for a different event on a specific control, would it still work? Or would I need to define a seperate event handler for that one instance?

[Norie]

Option Explicit forces variable declaration, which is good programming practice.
Yes.
You use Preserve to keep the existing elements of the array when you redimension it. In that actual piece of code it's actually redundant because the array is empty.
Not quite sure what you mean, can you illustrate with an example

[Annodomini2]

4. Not quite sure what you mean, can you illustrate with an example



Was just a question out of curiosity to help me understand the operation of the event handler, but here's an example:

Let's say you have a form with command buttons and for all the command buttons when they are clicked you get a pop-up box saying 'are you sure?' type of thing. Linked through an event handler to save repeated code.

Now for some of those buttons if you for example double-clicked them (if it lets you I know, but its an example), it ignored the pop-up, but you defined this operation invidually for each specific command button to support this functionality.

i.e.

Private Sub CommandButton1_DblClick()
PerformAction()
End Sub


(I know my syntax may be wrong).

Now would the system goto this function on this action or the event handler linked to it at initialisation and ignore the local operation or can the system distinguish between the two operations and use the local event operation?

[Norie]

Right, so what you mean is if an individual control has it's own event procedure, what
happens?

I don't actually know.:)

I'll look into it.

Generally I would think that you either have code in the class module that distinguishes
between controls, by name perhaps, and acts accordingly.

Or don't add all the controls to the array in the first place.:)

[Annodomini2]

Thanks for all the great help, don't worry about it too much! :)

[Annodomini2]

Almost there,

I was trying to add dynamic update of the combo box (copying the values) on the copy multipage macro, I have the following:

Case "ComboBox"
For I = 0 To ctl.ListCount - 1
With ctlNew
.AddItem ctl.List(I, 0)
.List(I, 1) = ctl.List(I, 1)
End With
Next I


Now the AddItem operation works fine, but I have another column, the
ctl.List(I,1) returns the value, but I don't appear to be able to assign it to the new control, I have also tried the following with no success:

Case "ComboBox"
For I = 0 To ctl.ListCount - 1
With ctlNew
.AddItem ctl.List(I, 0)
.Column(1, I) = ctl.List(I, 1)
End With
Next I


Any Ideas?

[Annodomini2]

Figured it out, it was the column count property not being updated.

[Annodomini2]

Nope spoke too soon, getting invalid index property, will investigate further.

[Annodomini2]

Sorted it, it didn't like having null values in the 2nd column.

Resulting Code:
Case "ComboBox"
'Update ColumnCount
ctlNew.ColumnCount = ctl.ColumnCount

'Copy Members
For I = 0 To ctl.ListCount - 1
With ctlNew
'Remove Already Declared value
If ctl.List(I, 0) <> ctl.Value Then
.AddItem ctl.List(I)
If ctl.List(I, 1) <> Null Then .List(I, 1) = ctl.List(I, 1)
End If
End With
Next I


Also added check to prevent used values appearing in new pages.

[Annodomini2]

May have found a bug here, sometimes it works, sometimes not, but as an interpreted language (I believe) I would have thought that 'compilation' would not be an issue?!

It was treating the result of all ctl.List(I, 1) all as null when I could see that there was a value.

Then when I removed the if statement and ensured that all values were initialised it proceeded to give me an array index error on the .List(I, 1) assignment. When with identical code it was working before.

Can anyone provide some insight as to if I am doing something wrong?

[Annodomini2]

Figured it out its the index offset provided by my exclusion, if I don't assign a value in the checkbox it works as the if statement is ignored (no value is initialised), but the index gets screwed up if it is!

[Annodomini2]

Updated code:

'Copy Members
j = 0
For I = 0 To ctl.ListCount - 1
With ctlNew
'Remove Already Declared value
If ctl.List(I, 0) <> ctl.Value Then
.AddItem ctl.List(I)
.List(I - j, 1) = ctl.List(I, 1)
Else
j = 1
End If
End With
Next I