Hi again Luc,
To add the button in Excel 2007: (see below for other versions)
If Developer Ribbon not displayed then see help under Developer ribbon to
turn it on.
You might also have to see how to set macro security to allow the macro to
run and when you save the workbook, save as a macro enabled workbook (.xlsm)
Developer ribbon -> Insert and Select button from ActiveX controls and then
click on worksheet and hold left mouse button and drag button out to required
size.
To add the button in earlier versions of Excel:
On the Controls Toolbox Toolbar (Not the forms toolbar) Select command
button and then click on worksheet and hold left mouse button and drag button
out to required size.
Note while doing the above the Ribbon or Toolbar Design mode button (Like a
set square, ruler and pencil) is activated (orange color). Leave it like that
for time being.
Right click the new button and select View code.
Copy the below code (between asterisk lines) and paste between the sub and
end sub in the VBA editor.
Click the X with red background top right to close the VBA editor.
Click the Design Mode button to turn it off.
You can now click the button to run your code.
If you want to make changes to the button design then click the Design mode
button and then right click the button and select properties.
Set the Caption property to what you want to display on the button.
To open the VBA editor again use Alt/F11 and then in the project explorer on
the left select the worksheet that has the button (If correct worksheet code
is not already displayed.)
Note that lines in green after copying into the VBA editor are comments
only. Also a space and underscore at the end of a line is a line break in an
otherwise single line of code.
'*************************************
'Uses a specified range to test
'In this example, the worksheet
'is named by value in cell A5
Dim rngToTest As Range
Dim strNewShtName As String
Dim wsNewSht As Worksheet
'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
'Edit "B5:V5" to your range
Set rngToTest = .Range("B5:V5")
End With
'Assign proposed new worksheet name to variable
strNewShtName = rngToTest _
.Cells(1, 1).Offset(0, -1).Value
'Test for new sheet name already existing
'by attempting to assign to a variable
On Error Resume Next
Set wsNewSht = Sheets(strNewShtName)
'If no error then the attempt to assign to variable
'was successful so worksheet already exists
If Err.Number = 0 Then 'No error
MsgBox "Worksheet " & strNewShtName _
& " already exists"
Exit Sub
End If
On Error GoTo 0 'Resume error trapping ASAP
'If get to here worksheet does not exist
'so create and name
If WorksheetFunction.CountA(rngToTest) > 0 Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = _
rngToTest.Cells(1, 1).Offset(0, -1).Value
End If
'Return to original worksheet.
Sheets("Sheet1").Select
Range("A1").Select
'*****************************************
Note that example will fail if the range starts in column A because there is
no cell to the left of the range.
--
Regards,
OssieMac
|
Similar Threads
