Mister_joe
01-27-2014, 07:46 AM
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.
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.