Open Userform by a SpreadSheet by clicking on the Tab

[Darren Smith]

I`ve created a Module Macro see below to open the user form.
Can`t work out how to activate the form.
Because I replace the worksheet with one of the same names I can`t put the code on to the worksheet itself.

Private Sub Open_UserForm()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Job Card Master")


If ws.Activate Then

Body_And_Vehicle_Type_Form.Show False

End If


End Sub

 

[Zot]

You need to use worksheet event to trigger macro. The macro is located in Worksheet class and you can select how to trigger it, not in standard module. Use

Private Sub Worksheet_Activate()

your code here

End Sub

 

[Darren Smith]

My issue is I can`t put this into a worksheet code. The tab gets replaced often with a tab of the same name.
I`ve put the Activate in the subline. Still no luck

Private Sub Worksheet_Activate()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Job Card Master")


If ws.Activate Then

Body_And_Vehicle_Type_Form.Show False

End If


End Sub

 

[Zot]

Let us know what is the process or what is your action?

What do you mean tab get replaced by same name? Why you want to replace if the name is the same? A bit confusing on what you were trying to achieve

 

[aRandomHelper]

Can you try placing this in ThisWorkbook's code instead?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Job Card Master" Then Body_And_Vehicle_Type_Form.Show False
End Sub

 

[Darren Smith]

Thanks very much this has worked

 

[Darren Smith]

Let us know what is the process or what is your action?

What do you mean tab get replaced by same name? Why you want to replace if the name is the same? A bit confusing on what you were trying to achieve

It`s a template so uses the same name sheet but varying amounts of pages in the workbook.
The code brings in a workbook with sheets with different No.s of pages and deletes the old one.

 

[Zot]

It`s a template so uses the same name sheet but varying amounts of pages in the workbook.
The code brings in a workbook with sheets with different No.s of pages and deletes the old one.

Glad that the problem is solved by @aRandomHelper already.

 

[Darren Smith]

Thanks for your help