Snafu
08-29-2016, 02:04 PM
Hi,
I am new to using VBA in Excel and trying to crash course myself into it the same way I did when first learning JavaScript many years ago. Unfortunately unlike JavaScript there seems to be a lacking of websites online with great advice for how to complete my specific task, so I ask you to help. I would like to describe my task in full to help you understand my problem.
My task
I am trying to create an excel document with 11 tabs. On each tab is a series of cells that must be 'ticked' or crossed off to indicate the task has been checked. (This can be done by any input so in this case I have chosen a font and not a tick box system or macro as it would take too long)
Of these 11 tabs, 8 have identical rows, each row (right to left) is used to display over time the checks completed. The first column ('A', the column with the task names) does not change in any situation and must not be allowed to be changed while every other cell on the document must be editable.
On top of this task there should be a drop down box with names of personnel who carry out these tasks. A way to timestamp a cell would be needed so when the person enters/selects their name from the drop down box it inputs the time in which this was completed. (timestamp doesn't need to be a locked cell)
Things I have already completed:
- Getting a name dropdown cell for personnel using Data Validation list drop down.
- Timestamp function to work in unison with the drop down list.
I need help on the following for this task:
- A way to lock column 'A' that doesn't throw up an error. Currently if I lock the sheet and make all but column 'A' unlocked (via cell formatting) it provides me with an error. Or a way of coding the sheet so this doesn't cause an issue.
- A set of code for the future if the timestamp row needs to be locked from editing once first input to avoid tampering. (this currently isn't required but it may be in the future)
My code I currently use:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B2:ZZ2"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(13, 0)
.Value = Now
.NumberFormat = "dd/MM/yyyy - hh:mm:ss"
End With
Else
rCell.Offset(13, 0).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Thank you all for your time reading this and helping.
-
Snafu
I am new to using VBA in Excel and trying to crash course myself into it the same way I did when first learning JavaScript many years ago. Unfortunately unlike JavaScript there seems to be a lacking of websites online with great advice for how to complete my specific task, so I ask you to help. I would like to describe my task in full to help you understand my problem.
My task
I am trying to create an excel document with 11 tabs. On each tab is a series of cells that must be 'ticked' or crossed off to indicate the task has been checked. (This can be done by any input so in this case I have chosen a font and not a tick box system or macro as it would take too long)
Of these 11 tabs, 8 have identical rows, each row (right to left) is used to display over time the checks completed. The first column ('A', the column with the task names) does not change in any situation and must not be allowed to be changed while every other cell on the document must be editable.
On top of this task there should be a drop down box with names of personnel who carry out these tasks. A way to timestamp a cell would be needed so when the person enters/selects their name from the drop down box it inputs the time in which this was completed. (timestamp doesn't need to be a locked cell)
Things I have already completed:
- Getting a name dropdown cell for personnel using Data Validation list drop down.
- Timestamp function to work in unison with the drop down list.
I need help on the following for this task:
- A way to lock column 'A' that doesn't throw up an error. Currently if I lock the sheet and make all but column 'A' unlocked (via cell formatting) it provides me with an error. Or a way of coding the sheet so this doesn't cause an issue.
- A set of code for the future if the timestamp row needs to be locked from editing once first input to avoid tampering. (this currently isn't required but it may be in the future)
My code I currently use:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B2:ZZ2"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(13, 0)
.Value = Now
.NumberFormat = "dd/MM/yyyy - hh:mm:ss"
End With
Else
rCell.Offset(13, 0).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Thank you all for your time reading this and helping.
-
Snafu