I want to create an input box for a range on a sheet (i understand
have to do this on sheet code worksheet selection change) that will d
two things, firstly from inputing a number 1-4 format the cell
different colour for each number then using the same input box allo
the user to enter any letter which will be put in the selected cell. S
i would like the input section of the box to be able to read somethin
like:- 1,h where 1 is the selection they made from 1-4 and the h is an
letter they wish!

Am i clear? im not sure...i know in my head what i want to do bu
putting it into practice is difficult as i have only been using excel
for about 2 months and programming is relatively new!

All help no matter what appreciated.

Simo

--
Message posted from http://www.ExcelForum.com

You could use worksheet change code.

Right click on the sheet tab, select View Code, and then past this code in

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
Select Case Left(.Value, 1)
Case 1:
.Interior.ColorIndex = 3
.Value = Mid(.Value, 2, 99)
Case 2:
.Interior.ColorIndex = 5
.Value = Mid(.Value, 2, 99)
Case 3:
.Interior.ColorIndex = 10
.Value = Mid(.Value, 2, 99)
Case 14:
.Interior.ColorIndex = 35
.Value = Mid(.Value, 2, 99)
Case Else: MsgBox "Invalid"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Lloyd " wrote in message
...
I want to create an input box for a range on a sheet (i understand i
have to do this on sheet code worksheet selection change) that will do
two things, firstly from inputing a number 1-4 format the cell a
different colour for each number then using the same input box allow
the user to enter any letter which will be put in the selected cell. So
i would like the input section of the box to be able to read something
like:- 1,h where 1 is the selection they made from 1-4 and the h is any
letter they wish!

Am i clear? im not sure...i know in my head what i want to do but
putting it into practice is difficult as i have only been using excell
for about 2 months and programming is relatively new!

All help no matter what appreciated.

Simon


---
Message posted from http://www.ExcelForum.com/

Cheers Bob,

I tried that and indeed it does coulor the cell, but i need this t
happen from an input box as well as at the same time being able t
enter in to the input box a letter which will be put in to the cell.

Haha i thought i wasnt clear in my explanation!

Simo

--
Message posted from http://www.ExcelForum.com

Simon,

I think you were clear, it just seemed odd and I thought you may not know
about my alternative <vbg

See if this gets closer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim val
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
val = InputBox("Input value")
With Target
Select Case Left(val, 1)
Case 1:
.Interior.ColorIndex = 3
.Value = Mid(val, 2, 99)
Case 2:
.Interior.ColorIndex = 5
.Value = Mid(val, 2, 99)
Case 3:
.Interior.ColorIndex = 10
.Value = Mid(val, 2, 99)
Case 14:
.Interior.ColorIndex = 35
.Value = Mid(val, 2, 99)
Case Else: MsgBox "Invalid"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Lloyd " wrote in message
...
Cheers Bob,

I tried that and indeed it does coulor the cell, but i need this to
happen from an input box as well as at the same time being able to
enter in to the input box a letter which will be put in to the cell.

Haha i thought i wasnt clear in my explanation!

Simon


---
Message posted from http://www.ExcelForum.com/

Thanks Bob,

No i didnt get me closer!!....it got me spot on i can now integrat
that with the rest of my code. Your a life saver!

Sorry about reposting the message but i couldnt find you on the member
list to e-mail you so i had to try a different direction.

One other question, if you wouldnt mind?....the other macros i hav
running...well especially the Auto_close runs throught every sheet (2
of them) and updates the information in a range on each sheet (thi
operation is essential) the range it updates (update meaning it check
for entry and then adds a date if there is an entry in that cell to
cell 36 cells away, this is to keep track of who was trained when) als
happens to be the range that the code you kindly provided will work on
how do i turn of the worksheet code for each sheet in my Auto_close o
do i do it in each sheet code?

Simon

Once again Thanks!!!

Simon

--
Message posted from http://www.ExcelForum.com

Simon,

If I undedrstand correctly, there are two things to note here.

Firstly, the code I gave you is for one worksheet. So it needs to be put in
every worksheet that yoiu wish to apply it to. But, and I think this will be
good news to you, you can apply it to the whole workbook as a different
event. This is the code and it goes in the ThisWorkbook module instead of
the sheet module (there are a couple of small but important differences)

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim val
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, sh.Range("A1:A10")) Is Nothing Then
val = InputBox("Input value")
With Target
Select Case Left(val, 1)
Case 1:
.Interior.ColorIndex = 3
.Value = Mid(val, 2, 99)
Case 2:
.Interior.ColorIndex = 5
.Value = Mid(val, 2, 99)
Case 3:
.Interior.ColorIndex = 10
.Value = Mid(val, 2, 99)
Case 14:
.Interior.ColorIndex = 35
.Value = Mid(val, 2, 99)
Case Else: MsgBox "Invalid"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

And, this code is event driven (the evnt being a selection within the
worksheet. So if you want it not to happen in your Auto_Close macro, turn
events off

Application.EnableEvents = False

and at the end

Application.EnableEvents = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Lloyd " wrote in message
...
Thanks Bob,

No i didnt get me closer!!....it got me spot on i can now integrate
that with the rest of my code. Your a life saver!

Sorry about reposting the message but i couldnt find you on the members
list to e-mail you so i had to try a different direction.

One other question, if you wouldnt mind?....the other macros i have
running...well especially the Auto_close runs throught every sheet (20
of them) and updates the information in a range on each sheet (this
operation is essential) the range it updates (update meaning it checks
for entry and then adds a date if there is an entry in that cell to a
cell 36 cells away, this is to keep track of who was trained when) also
happens to be the range that the code you kindly provided will work on,
how do i turn of the worksheet code for each sheet in my Auto_close or
do i do it in each sheet code?

Simon

Once again Thanks!!!

Simon.


---
Message posted from http://www.ExcelForum.com/

Bob,

I forgot to say i already have a workbook sheet selection change goin
on in this work book but the range it works on does not match with th
one your code will work on so there shouldn't be any conflicts when
paste this code into all 20 sheets should there

--
Message posted from http://www.ExcelForum.com