Help needed, Input box doing 2 tasks?

[Simon Lloyd]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

Simon,

There shouldn't be a problem, they will both fire okay.

Don't put the code in 20 sheets, merge the two bits of event code.

--

HTH

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

 

[Simon Lloyd]

I already have an in this workbook sheets selection change working, an
the range on each sheet varies so i will have to paste it in to eac
sheet, will the events.active work for all events or just this code
can you make it specifice to worksheet selection change?

Anyway, ive had enough of your time already...couldnt have got there s
quick with out you!

Lifes easier now (well for the mean time)!

Simon

 

[Simon Lloyd]

Bob,

I dont think i can merge the code as 1 is a workbook event and works o
the same range on each sheet but the worksheet event works on differen
ranges on each sheet, here is the work book code it's out of the tes
book and not the working program but you will get the gist!

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVa
Target As Range)
Dim myrange As Range
Dim isect As Range
If Sh.Name = "Hidden" Then Exit Sub
Set myrange = Sh.Range("E1:G20")


Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then

Exit Sub
End If
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Dim t1 As String
Dim I1 As Integer
Dim res As Variant

t1 = InputBox("Only Valid Skill Titles Will Be Allowed!", "Skil
Addition Box", "")
With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

Exit Sub



Worksheets("hidden").Visible = False
End If

I1 = MsgBox("Please try again " & Chr(13) & "Skill " & " Entry no
recognised " & "Please Contact Training Dept to Add Skill Title!!")
If ActiveCell = "shift " Then
Exit Sub
Else
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

End If
End Sub

if you would like to have all the code i have for all macro's in th
working program i can either paste them into an e-mail or if you want
can attatch the whole program its 5.5 meg and when it updates o
shutdown (thats the way i have designed it)it takes about 5 mins to d
20 sheets!

E-mail me and let me know.

p.s are you a professional programmer or just have a great intrest i
VB?

Simon

 

[Simon Lloyd]

Bob,

I dont think i can merge the code as 1 is a workbook event and works o
the same range on each sheet but the worksheet event works on differen
ranges on each sheet, here is the work book code it's out of the tes
book and not the working program but you will get the gist!

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVa
Target As Range)
Dim myrange As Range
Dim isect As Range
If Sh.Name = "Hidden" Then Exit Sub
Set myrange = Sh.Range("E1:G20")


Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then

Exit Sub
End If
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Dim t1 As String
Dim I1 As Integer
Dim res As Variant

t1 = InputBox("Only Valid Skill Titles Will Be Allowed!", "Skil
Addition Box", "")
With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

Exit Sub



Worksheets("hidden").Visible = False
End If

I1 = MsgBox("Please try again " & Chr(13) & "Skill " & " Entry no
recognised " & "Please Contact Training Dept to Add Skill Title!!")
If ActiveCell = "shift " Then
Exit Sub
Else
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

End If
End Sub

if you would like to have all the code i have for all macro's in th
working program i can either paste them into an e-mail or if you want
can attatch the whole program its 5.5 meg and when it updates o
shutdown (thats the way i have designed it)it takes about 5 mins to d
20 sheets!

E-mail me and let me know.

p.s are you a professional programmer or just have a great intrest i
VB?

Simon

 

[Bob Phillips]

Simon,

Wing it over (the workbook), and I will take a look (probably not until the
weekend though I am afraid, I have a couple of deadlines).

No, I am not a programer, was once back in the days of Assembler, Pascal,
and Apple IIe,, but haven't been for over 20 years ( before anyone else
says, I know it probably shows).

--

HTH

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

 

[Simon Lloyd]

I will send it Bob but i need your e-mail address as i cant find yo
under members!

A couple of words....the workbook has an Auto_open which turns of
excel toolbars (all of them!) but dont panic (as if you would!) whe
you shut the program down it does its updating (currently takes 5min
unless you know a way of speeding it up!) it turns the worksheet men
bar back on. because most of the people who will work with this progra
are technophobes i have only allowed them to do the "click" the red
and then the book updates, saves and shutsdown...by the way i a
working with excel xp and saving it down to 97 as nearly all my plac
of work runs this at the moment. Anyway i'm having to implement th
workbook tomorrow and will be online all day but if you get to look a
it later than tomorrow i will cut n paste all the info that will b
entered after tomorrow and let you know how i got on!

You're a generous sole aint ya? when im down that way next i'll brin
you a bottle of the good stuff!

Simo

 

[Bob Phillips]

You won't find me under members as I never go on ExcelForum, I read the NGs
direct from the NG server.

My address is

bob . phillips @ tiscali . co . uk

remove the spaces - it's a spam thing.

--

HTH

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