Displaying cell contents based on input?
Tom,
I got the same error as I did with Bob's script.
"Tom Ogilvy" wrote:
In the worksheet where you want this behavior, right click on the sheet tab
and select view code.
in the resulting module, paste in this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim num As Integer
Dim sStr As String
If Target.Count 1 Then Exit Sub
If Target.Column = 2 Then
If IsNumeric(Target.Value) Then
If Target.Value < 1 Then
num = Hour(Target.Value)
Else
num = Target.Value
End If
Select Case num
Case 9
sStr = "'9 - 6"
Case 10
sStr = "'10 - 7"
Case 12
sStr = "'12 - 9"
End Select
Application.EnableEvents = False
Target.Value = sStr
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
Assuming you only want this behavior in 1 column, where it says
If Target.Column = 2 Then
Change the 2 to reflect the column where you will enter the numbers (A is
1, B is 2, C is 3 etc).
Now go back to excel and see if that does what you want.
You can enter
9 or 9:00 or 9:
10 or 10:00 or 10:
12 or 12:00 or 12:
and it should make the correction. (if done in the appropriate column).
--
Regards,
Tom Ogilvy
"Fleone" wrote in message
...
I posted this question a few weeks back and wasn't able to come up with a
viable solution, so I post it again in hopes that some fresh eyes might
come
across it.
What I would like to do is have a cell display differently than the data
input.
Specifically I am working on a schedule. There are three shifts, one 9 am
to
6 pm, one 10 am to 7 pm and one 12 pm to 9 pm.
Here is what I would like to do:
If I enter 09:00 in a particular cell, I would like the cell itself to
display 9 - 6, the same thing would apply for the other shifts, 10:00
entered
would display 10 - 7, and 12:00 entered would display 12 - 9.
I am looking for this type of solution because I will be having several
other worksheets refering to a "master" schedule worksheet.
Anyway, I have never written a visual basic script of my own, through the
kindness and expertise of others here, I have been able to take some and
modify them to my needs, but have to admit to being relatively LOST when
confronted with the task of starting from scratch.
If anyone can point me in the proper direction, or give me some ideas on
where to start, I would greatly appreciate it.
Thanks.
|