How to Trigger an event

[Mister_joe]

Hi all,
I need your help! I have a command button (ActiveX control) on a worksheet. I would like for the click event of this command button to trigger the Workbook_NewSheet event of the workbook, thereby adding a new worksheet to the workbook. The code in the Workbook_NewSheet event is as follows:


Private Sub Workbook_NewSheet(ByVal Sh As Object)Dim sht As Worksheet
Dim strName As String


'If a worksheet with a specific name already exists, delete it before creating the new worksheet
For Each sht In ActiveWorkbook.Sheets
strName = sht.Name
If (strName = "MySheet") Then
Application.DisplayAlerts = False
ActiveWorkbook.Sheets(strName).Delete
End If
Next

Application.DisplayAlerts = True
Sh.Activate
Sh.Name = "MySheet"

'Place a command button on the new worksheet
ActiveSheet.OLEObjects.Add ClassType:="Forms.Label.1", Left:=72, Top:=72, Height:=25, Width:=100
Set btnOk = ActiveSheet.OLEObjects(ActiveSheet.OLEObjects.Count).Object

With btnOk
.Caption = "Whatever"
.BackColor = vbRed
.Font.Bold = True
.ForeColor = vbBlue
End With
End Sub

Please, recommend what code I need to have inside the command button click event to be able to trigger the Workbook_NewSheet event.

[mikerickson]

Try
Sub CommandButtonA_Click()
ThisWorkbook.Worksheets.Add
End Sub

[Aflatoon]

Workbook_NewSheet is triggered by adding a new sheet, not the other way around.

[Mister_joe]

Much respect! I was beginning to get really stressed up about the matter. How did you guys get to this level of expertise? There are far too many things about VBA that I haven't been able to get straight out of the book I am reading. Makes me wonder, really!

Anyway, there is a twist to my issue. The Workbook_NewSheet event also places a command button on the new worksheet. When a user clicks this button, the code associated with the click event should run. Since this button is created at run time, I cannot have access to its click event procedure at design time. How should I go about this?

[Aflatoon]

Use a Forms button. Then you can set its OnAction property to the name of an existing routine. Either that or use a template worksheet that already has the button and code you need.

[mikerickson]

Write the button's code in a sub that is in a normal module
' in standard module

Sub mySub()
MsgBox "button clicked"
End Sub

then use your add button code to add a button from the Forms menu

With ActiveSheet.Buttons.Add(100, 75, 100, 20)
.OnAction = "MySub"
End With

[Mister_joe]

Thanks. The ActiveSheet.Buttons.Add(100, 75, 100, 20) gives me a CommandBarButton. This does not support the forecolor and font properties that I need on the button. I really would like to use CommandButton, but this does not support the OnAction property. Also, I would like to avoid creating a template worksheet.

[GTO]

...Also, I would like to avoid creating a template worksheet.

If I might ask - why? Keep it xlSheetVeryHidden. When wanting to create a new sheet, kill screen repaints, change the sheet's visibility, copy, return the template sheet's visibility to xlSheetVeryHidden.

[Aflatoon]

If you want ActiveX and you won't use a template sheet, you either have to use code to write code (bad idea, in my opinion) or use classes to hook the events of the controls, which has its own problems. If you really need font colours that badly, I suggest you use a shape rather than a button. Better yet, use a menu system instead. :)

[Mister_joe]

Thanks a lot. I will explore the option of hiding the relevant worksheet.