Displaying cell contents based on input?

[Guest]

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.

 

[Bob Phillips]

Hi Fleone,

Here is some event code that works for A1:A100

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
Select Case .Value
Case 9 / 24: .NumberFormat = "@"
.Value = "9 - 6"
Case 10 / 24: .NumberFormat = "@"
.Value = "10 - 7"
Case 12 / 24: .NumberFormat = "@"
.Value = "12 - 9"
Case Else: .Value = "Invalid input"
.NumberFormat = "hh:mm"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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).

 

[Guest]

Bob,
Thanks for the information. I copied this code and then pasted in according
to the instructions by right-clicking the tab, choosing view code, then
pasting it in.
I did modify the code to use D11000 instead of A1:A1000 and when I enter
09:00, or 9:00 in one of the cells it presents the following error:

Compiler Error
Expected: identifier or bracketed expression

and behind this dialog the ThisWorkbook (Code) module is showing with
ActiveWorkbook. listed in red.

 

[Guest]

Tom,
I got the same error as I did with Bob's script.

 

[Guest]

Sorry about the previous post, I fat-fingered the enter key <G>
I did get the same error as when using Bob's code.
I changed the column reference to column 4 ( D ) and got the compiler error.

Compiler Error
Expected: identifier or bracketed expression

and behind this dialog the ThisWorkbook (Code) module is showing with
ActiveWorkbook. listed in red.

Just to be sure it wasn't me, I opened a brand new workbook and pasted this
code into "Sheet1" and it worked flawlessly, so it must be a problem with my
workbook, something to do with other code I am using in there I imagine.

Thank you for this code as it appears to work exactly.
One additional question, If I want to use multiple columns, can I use a
comma to separate them like this: If Target.Column = 2, 3, 4, 5 Then
or would I need to enter an If Target.Column = entry for each column I want
to be affected?

Thanks again!

 

[Guest]

Bob,
I pasted this code into a new blank workbook and it worked fine. Just as
with Tom's code, it appears to be a problem with my current workbook.
Thank you very much for a prompt solution to my question.
YOU GUYS ROCK!

 

[Tom Ogilvy]

If Target.Column >= 2 and Target.Column <= 5 then