Displaying cell contents based on input?

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.

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)


"Fleone" <Fleone@discussions.microsoft.com> wrote in message
news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> 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,
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 D1:D1000 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.

"Bob Phillips" wrote:

> 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)
>
>
> "Fleone" <Fleone@discussions.microsoft.com> wrote in message
> news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> > 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,
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!

"Bob Phillips" wrote:

> 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)
>
>
> "Fleone" <Fleone@discussions.microsoft.com> wrote in message
> news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> > 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.
>
>
>

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" <Fleone@discussions.microsoft.com> wrote in message
news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> 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.

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" <Fleone@discussions.microsoft.com> wrote in message
> news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> > 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.
>
>
>

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!
"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" <Fleone@discussions.microsoft.com> wrote in message
> news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> > 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.
>
>
>

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

--
Regards,
Tom Ogilvy

"Fleone" <Fleone@discussions.microsoft.com> wrote in message
news:A1E8C7BE-6AD3-44B0-990A-64B443B076E2@microsoft.com...
> 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!
> "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" <Fleone@discussions.microsoft.com> wrote in message
> > news:B4E378AA-B25D-4B78-AD6C-E1F78AB24774@microsoft.com...
> > > 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.
> >
> >
> >