Reference vb6 form form excel worksheet

[JimJames]

I have an app that has Form1, Multipage1 with named Pages and several other controls.

Is that a way to reference the Form1 from ThisWorkbook Code?

So that when I click a named worksheet tab it selects a Multipage Page tab with
the same name?


Thank You

Jim

 

[RoryA]

Is it actually a VB6 form in a separate executable, or a userform in the same workbook?

 

[JimJames]

Hi RoryA,

The App I built is in VB6 and is separate from the Excel Workbook.
I am using Visual Basic Professional and Office 2007.
The App has a Form(Form1), a Multipage Control, three CommandButtons(Open Excel, Close Excel, Add Sheets),
two Labels(Last Name, First Name) and two TextBoxes used to enter Names.

When I run the App and click the Open Excel CommandButton an Excel Workbook opens
after putting names into the TextBoxes and clicking the Add Sheet CommandButton,
Worksheets are added and renamed, also new Page Tabs are added to the
Multipage Control and named.

When I click on the Multipage Page Tabs a Worksheet with the same name is activated.
That part of the App works fine, but when I click on the Worksheet tab to activates the
Multipage Page Tab, I get a Run-time error '424' Object Required.

Another MrExcel sent the code below, I put it into the code section of the Workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim i As Long
    With Form1.MultiPage1
        For i = 0 To .Pages.Count - 1
            If .Pages(i).Caption = Sh.Name Then
                .Value = i
                Exit For
            End If
        Next i
    End With
End Sub
[Code\]

I put this code into a Module 
[Code]
Sub ShowForm()
Form1.MultiPage1.Show 0
End S
[Code\]

I was told that the error pointed to a missing reference and that I needed to declare Form1 as a form.
I cannot figure-out how to do that!
Do I need to declare the form from within the Excel workbook or in the App?
Really at a loss!

Thanks For Your Help

Jim

 

[RoryA]

Unless you have a reference set in the VBA project of the workbook to your VB6 app, that won't work. It would be much simpler I think to declare a workbook variable WithEvents in your VB6 app to trap the SheetActivate event of the workbook you create.