'New sub routine created with name reflective of the work to be done
Sub ChangeSheetName()
'Define variable to hold input from user
Dim NewName As String
'Firstly populate the variable with the user input then process
NewName = InputBox("Enter the new name of the sheet")
'Check if the variable is empty if so exit the sub
'If the value input by user is not empty then set the active sheet to this value
'Put a line of text in the first cell of the sheet
'then add current date to next cell
'then add the value that the sheet name was set to in the next cell
'in each of those processes do an autofit for the column so that log note
'looks like a sentence
If NewName = "" Then
Exit Sub
Else
With ActiveSheet
.Name = NewName
.Range("A1").Select
Selection.Value = "This sheet's name was last changed on the :"
Columns("A:A").EntireColumn.AutoFit
Range("B1").Select
Selection.Value = "=now()"
Columns("B:B").EntireColumn.AutoFit
.Range("C1").Select
Selection.Value = "To: " & NewName
Columns("C:C").EntireColumn.AutoFit
End With
End If
End Sub
Wednesday, 14 March 2012
Using VBA
In Excel for example you can use VB code to manipulate certain things within the rows/columns and objects or even work with external data sources to import data and display it on the sheet. For example here is a simple bit of code to press a button on the sheet, bring up a dialog box where the user inputs a new sheet name and then a log is written in the spreadsheet of when the name was last changed and to what value. Here I am using 2003 Excel.
Subscribe to:
Posts (Atom)