number to name

[Guest]

I want to do my schedule with Excell, and I was wondering if it's possible to
type a number and get a name instead... for example if on the 3rd of
february, Mark is working, and that I associate the number 1 to Mark. is
there a way that if I type "1" under the 3rd of february, that converts it
directly to Mark?
thanks
Gaetan

 

[Bob Phillips]

Bart,

Using event code you can do it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2:H10")) Is Nothing Then
With Target
Select Case .Value
Case 1: .Value = "Mark"
Case 2: .Value = "John"
Case 3: .Value = "Bill"
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.

Extend the list as far as you want.

--

HTH

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

 

[Jim May]

Bob,
Side question..
I notice that
Application.EnableEvents = True << only appears under ws_exit:
and not elsewhere,
This says to me that ONLY if there is an error does the status of
EnableEvents
get "turned-back" to "On", or TRUE;
Is this because EnableEvents whenever set to False, defaults back to True on
successful
completion of any/all SUB(s)?
TIA,
Jim May

 

[JulieD]

Hi Jim

as Bob has not coded an "end sub" prior to the error handler, the code under
the error handler runs in all circumstances.

if you turn application.enableevents off you need to turn them back on as it
does not happen automatically ever.

Cheers
JulieD

 

[Jim May]

Thanks for the clarification.
Jim

Hi Jim

as Bob has not coded an "end sub" prior to the error handler, the code under
the error handler runs in all circumstances.

if you turn application.enableevents off you need to turn them back on as it
does not happen automatically ever.

Cheers
JulieD

 

[Bob Phillips]

Jim,

To add a couple of points to Julie's response that may be helpful in general
understanding.

When code is executing, it always progresses to the next line unless there
is some statement that 'diverts' it, such as an If ... n/l ... statements
.... n/l ... Else ... etc, a loop construct (which sends it back), a function
or Sub call, or a Goto (sorry, bit basic, but I need to spell it out for the
rest ). My real point here is that a label does not divert the code, it
just gets passed over and ignored. The label only comes into play should
there be a Goto, and the code executes the Goto. So my code passes over the
label.

The purpose of that code is to ensure that events are always re-enabled,
whether an error is encountered or not. The answer to your second question
is most definitely not, exiting a sub does not re-enable events, you must do
so, or potentially create havoc.

As a side note, as I was writing the second paragraph above, I wondered
about Gosub. My old BBC Basic supported Gosub (in a similar manner to Call
in VBA), but I had never seen anyone use it in VBA. So I looked it up in
help, and VBA does support it. Look it up in help, it's weird, no wonder
no-one ever uses it.

Regards

Bob

 

[Jim May]

Tks Bob,
always appreciate your going the extra mile to offer
what doesn't get written ...
Jim

 

[Guest]

Thanks a lot...it's working great... have a nice sunday...