Open userform in another worksheet

[ipbr21054]

To make this clear i am working in one workbook BUT i wish to open a Userform in a different workbook.

Basically in my workbook the activecell value needs to be found in the newly opened workbook of which it dose fine.
The code finds the value it was searching for & the cell is selected / active.

My goal is to now open a userform called DiscoII
The problem i am facing is when i use DiscoII.Show nothing happens.
Am i missing something here ?

Any help will put this knightmare to bed.
Thanks

Private Sub PurchasedKey_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
 
  With ActiveSheet
    If .Range("Q1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    If .Range("N1") = "M" Then
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("Q1").Value & " (SLS).pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else

    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If
    
    With ActiveSheet
    ' ActiveWindow.SelectedSheets.PrintOut copies:=1
    Unload PrinterForm
    
    .Range("B3").Select
    Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
ans = ActiveCell.Value
    Workbooks.Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
        Lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
    For Each c In Sheets("POSTAGE").Range("B1:B" & Lastrow)
        If c.Value = ans Then Application.Goto Reference:=Sheets("POSTAGE").Range(c.Address): Exit Sub
    Next
    
    Application.ScreenUpdating = True

 End With
 End With
    
End Sub

 

[bobsan42]

I don't see the relevance of the submitted code - it doesn't try to open any forms.

Check this for opening an userform from another workbook.

BTW, i don't know anything about your project, but code from one workbook can be used to manipulate many others w/o using any code from them.

 

[ipbr21054]

I don't see the relevance of the submitted code - it doesn't try to open any forms.

Check this for opening an userform from another workbook.

BTW, i don't know anything about your project, but code from one workbook can be used to manipulate many others w/o using any code from them.

Hi,
What you have advised i have read in the other post.
I did the module part.
I then put a command button on a sheet.
When using the command button my otherwork book opens & the userform is show PEREFCT CHEERS.

No now to add it to my code so it all runs in one go but i it then doesnt work.

Here is the code that is run & towards the bottom is the code to then open the userform once the code above has finished.
But it doesnt then open the userform.
It does all the above & thats it as if the code i added wasnt even there.

Private Sub PurchasedKey_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
  Dim wb As Workbook
  
  With ActiveSheet
    If .Range("Q1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    If .Range("N1") = "M" Then
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else

    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If
    
    With ActiveSheet
    'ActiveWindow.SelectedSheets.PrintOut copies:=1
    Unload PrinterForm
    
    .Range("B3").Select
    Application.ScreenUpdating = False
    Dim C As Range
    Dim ans As String
    Dim Lastrow As Long
        ans = ActiveCell.Value
        Workbooks.Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
        Lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
    For Each C In Sheets("POSTAGE").Range("B1:B" & Lastrow)
        If C.Value = ans Then Application.Goto Reference:=Sheets("POSTAGE").Range(C.Address): Exit Sub
    Next

 End With
 End With
          Set wb = Application.Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
          Application.Run ("'" & wb.Name & "'!openForm")
          Application.ScreenUpdating = True
End Sub

 

[AC PORTA VIA]

see if this is what you are looking for

Redirecting

 

[bobsan42]

I don't see any immediate reason why the UF will not show.
If no error is displayed I suspect it may be showing and closing if some other code runs from the form itself.
But the only way to be sure is debug, go through this part of the code step by step.

I would suggest some corrections to the last bit of the code. These two lines are doing almost the same thing:

        Workbooks.Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
....
          Set wb = Application.Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")

replace the first line with the second and remove the second

Ooops. I just figured why your form is not showing:

        If C.Value = ans Then Application.Goto Reference:=Sheets("POSTAGE").Range(C.Address): Exit Sub

The problem is at the end of this line - maybe you have to replace Exit Sub with Exit For
so the end of your code becomes something like this:

            Application.ScreenUpdating = False
            Dim C As Range
            Dim ans As String
            Dim Lastrow As Long
            
            ans = ActiveCell.Value
            Set wb = Application.Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
            Lastrow = wb.Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
            For Each C In wb.Sheets("POSTAGE").Range("B1:B" & Lastrow)
                If C.Value = ans Then Application.Goto Reference:=wb.Sheets("POSTAGE").Range(C.Address): Exit For
            Next
        End With
    End With
    
    Application.Run ("'" & wb.Name & "'!openForm")
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Hi.
All done & working thanks.

Exit For did the trick