Show a form created with an addin in active window with 2 or more different sheets

[BennyBatt]

Hallo, everybody.
This problem is due to a quite unusual situation, and I think there are little chances to replicate easily.
Anyway, I hope someone could understand it, and suggest me a way to work in.

I have a worksheet where I transfer information from an outer source using an addin that is saved in the ususal folder C:\Users\[user]\AppData\Roaming\Microsoft\AddIns.
The addin creare a form with a button added in the toolbar with this code:

    Set IlToolbar = Application.CommandBars.Add(Name:="ImmerLab", Position:=msoBarFloating, MenuBar:=False, Temporary:=True)

    Set Button1 = IlToolbar.Controls.Add(Type:=msoControlButton)
    With Button1
        .BeginGroup = True
        .Style = MsoButtonStyle.msoButtonIconAndCaptionBelow
        .Caption = "Show/Hide"
        .FaceId = 256
        .OnAction = "ShowHideForm"
    End With
    
    IlToolbar.Protection = MsoBarProtection.msoBarNoCustomize + MsoBarProtection.msoBarNoMove
    IlToolbar.Visible = True

Then, the ShowHideForm macro let the form visible, with the normal code:

UserForm1.Show

Button and text boxes in the form works fine.
Everything is OK; but in these days I had to insert a new format for the aforesaid worksheet, so I had to write more code to differenziate some macro acted by the form buttons.
Morevoer, I had to insert a global variable to undersand which worksheet is open.
At the end, if I open only a workbook also with sheets of the 2 types, everything continues to works fine, but if I open 2 workbooks, the code no longer works, because when the UserForm1.Show command is implemented, the form always moves to the first open workbook, and never appears in the second, even by closing the first workbook (and closing the form itself, then it does not reopen).
Therefore, I cannot manage the addin with 2 workbooks open at the same time.
I thought about indicating to the form which is the active workbook, but I don't know how to do it, and then, even in that case, would the form's command macros act on the active workbook itself?

Is there someone that have a solution?

Thanks in advance.

 

[Micron]

Assuming I understand what you're asking for, try the accepted solution here

Redirecting

 

[BennyBatt]

Thanks for the answer.
Well, the problem is different. In this case, in addin folder there is a workbook containing all the procedure to create and manage the userform. This workbook is automatically opened from Excel in the background when another workbook is opened, too (but, of course just once in case of multiple workbooks: at the first opening); it's the rule of the Excel addins' managing.
To activate the userform, I just had put it as a button in the menu using the "addictional component" section in the Excel's settings.
The problem is that the userform is in the background, but get as reference the first workbook opened (the one that also acted the opening of the workbook in the addin folder). So, when I open another workbook and then I try to open the useform by pressing the aforesaid button, Excel just move move back to the window of the first workbook, and if I close this one, the useform never shows again.
Of course, the solution is to work only with a workbook at a time, but it is uncomfortable.

I know, this is a situation that rarely occurs...

 

[Micron]

All I can think of now is that if you downloaded the addin from somewhere, see if they can help you.

 

[BennyBatt]

This addin is not coming from any download; I coded on my own.
As I told before the code in the Private Sub workbook_open() only create a button and put it in the toolbar, and this operations obviously don't create problem. Inside there, the button has the following code:

    With Button1
        .BeginGroup = True
        .Style = MsoButtonStyle.msoButtonIconAndCaptionBelow
        .Caption = "Show/Hide controls"
        .FaceId = 256
        .OnAction = "ShowHideForm"
    End With

The problem is in the ShowHideForm sub(that is in a module).
This is the sub code:

' Show control form
Sub ShowHideForm()

On Error GoTo Errori
'
sMess = Trim(ActiveWorkbook.ActiveSheet.Range("Z1").Value)
If (sMess = "MODUL" Or sMess = "COMB" Or sMess = "TEST" _
Or sMess = "SHEET") Then
  If (UserForm1.Visible) Then
    UserForm1.Hide
  Else
    'set the document model
    model = IIf(Range("J1") = "PG_19_Q", 1, 2)
    sMess = IIf(model = 1, ActiveWorkbook.ActiveSheet.Range(STATIONCELL).Value, ActiveWorkbook.ActiveSheet.Range(STATIONCELL_NEW).Value)
    If sMess = vbNullString Then
        sMess = MsgBox("WRONG." & vbCrLf & vbCrLf, vbCritical + vbOKOnly)
        Exit Sub
    End If
    UserForm1.StationId.Value = CStr(sMess)
    UserForm1.StationSpinButton.Value = CStr(sMess)
    UserForm1.Show
  End If
Else
   MsgBox ("Function not available")
End If
'
Out:
Exit Sub
'
Errori:
    sMess = MsgBox( err.description & " - " & Err.Number)
    Resume Out
End Sub

So, what happen is that when I try to open the form in a workbook different from the first one opened, the UserForm1.Show move the form is that workbook exactly.
I tried changing ActiveWorkbook with ActiveWindow without having any changes.
Also simplyfing leaving only UserForm1.Show gives the same result...
I think it's a rule of addin, as before said.