Excel - Help With A Project

[Dr_Phil]

hi,

Im doing a college project in excel which i have to design a booking
sytm for a school theater performance. I am having trouble wit the seat
selection method. At the moment i have a layout ot the seats similar to
this:

__a b c d e f g h (these are not cell references)
0
1
2
3
4

5
6
7
8
9

In each of the cells there is either an "F" or "A" or "C", F being free
A being Adult and C being Child. At the moment to book a seat the user
Changes the letter in the apropriate cell but i have no way of finding
out what cell has been changed so i can not put the seat that has ben
booked on to a ticket.

Any help would be much apriciated, thanx
Dr Phil

 

[Don Lloyd]

Hi,
One way using VBA

In the Worksheet_Change (Worksheet Module) enter the following

Application.EnableEvents = False
Select Case Target.Value
Case is = "F", "A", "C"
Target.Interior.ColorIndex = 35
Case Else
Target.Interior.ColorIndex = xlColorIndexNone
Target = ""
End Select
Application.EnableEvents = True

If F, C, or A is entered, the cell colour will change to a light green.
If anything else is entered, including delete, the cell will be without
colour and empty.

Regards,
Don

 

[Dr_Phil]

Hi,

Thanx 4 ur reply. but i dont follow any of the code that u gave me an
im not sure exactly where to put it!!. is there no way to do it in
cell

 

[Bob Phillips]

Do you want the cells that are completed to be highlighted, or just the last
one changed? I was not clear from your OP.

--

HTH

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

 

[Dr_Phil]

neither. I want excel to rember which were changed and for that cell re
(seat ref) to be shown on a seperate shee

 

[Bob Phillips]

Aah, you didn't say that.

This event code will store the address of changed cells on the next sheet

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:H50")) Is Nothing Then
Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1,
0).Value = Target.Address
End If

ws_exit:
Application.EnableEvents = True
End Sub

you wil need to set your target range.

--

HTH

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

 

[Tom Ogilvy]

Right click on the sheet tab of the sheet that contains your seat listing
(where the user changes the letter). This is event code which fires when a
cell is changed. If you want to know about events, see Chip Pearson's page

http://www.cpearson.com/excel/events.htm

As Bob has written it, it will record the address of cell that has changed
in the next empty cell in column A. I suspect you need the letter and row
of the seat and need to place it in a specific cell on the "ticket" sheet.
Assume your seat range is in F10:Z50, with the row numbers in E10:E50 and
the seat letters in F9:Z9 (as an example)

Further assume you want the number , hypen, letter in cell B9 of Sheet2 (the
image of the ticket (assume A1:M30))

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count > 1 then exit sub
' check to see it the cell changed is one in the range of cells with seat
assignments
If Not Intersect(Target, Range("F10:Z50")) Is Nothing Then
' Check if a seat is being issued (Adult or Child)
if Ucase(Target.Value) = "A" or Ucase(Target.Value) = "C" then
' get the row of the seat
rw = Intersect(target.EntireRow, Range("E10:E50)).Value
' get the letter of the seat
ltr = Intersect(Target.EntireColumn,Range("F9:Z9")).Value
' write the ticket
Worksheets("Sheet2").Range("B9").Value = rw & " - " & ltr
' Print the tickect
Worksheets("Sheet2").Range("A1:M30").Printout
End if
End If
End Sub

--
Regards,
Tom Ogilvy

Aah, you didn't say that.

This event code will store the address of changed cells on the next sheet

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:H50")) Is Nothing Then
Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1,
0).Value = Target.Address
End If

ws_exit:
Application.EnableEvents = True
End Sub

you wil need to set your target range.

--

HTH

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

 

[Dr_Phil]

Hi. I added your code and changed it to the apropriate cell refs. Bu
wen i do change the letter i get :

"Object Variable or With block variable not set"

This is the changed code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
' check to see it the cell changed is one in the range of cells wit
seat assignments
If Not Intersect(Target, Range("H1424")) Is Nothing Then
' Check if a seat is being issued (Adult or Child)
If UCase(Target.Value) = "A" Or UCase(Target.Value) = "C" Then
' get the row of the seat
rw = Intersect(Target.EntireRow, Range("H1313")).Value
' get the letter of the seat
ltr = Intersect(Target.EntireColumn, Range("G14:G24")).Value
' write the ticket
Worksheets("Sheet2").Range("B9").Value = rw & " - " & ltr
' Print the tickect
Worksheets("Sheet2").Range("A1:M30").PrintOut
End If
End If
End Sub


The error is on the line that begins rw = .......

Than

 

[Dave Peterson]

I haven't followed this thread, but you need to adjust these two lines:

rw = Intersect(Target.EntireRow, Range("H1313")).Value
and
ltr = Intersect(Target.EntireColumn, Range("G14:G24")).Value

the intersection of a row (14:24) and 13 is nothing, hence the error.

I think you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Variant
Dim ltr As Variant

If Target.Count > 1 Then Exit Sub
' check to see it the cell changed is one in the range of cells with
' seat assignments
If Not Intersect(Target, Range("H1424")) Is Nothing Then
' Check if a seat is being issued (Adult or Child)
If UCase(Target.Value) = "A" Or UCase(Target.Value) = "C" Then
' get the row of the seat
ltr = Intersect(Target.EntireColumn, Range("H1313")).Value
' get the letter of the seat
rw = Intersect(Target.EntireRow, Range("G14:G24")).Value
' write the ticket
Worksheets("Sheet2").Range("B9").Value = rw & " - " & ltr
' Print the tickect
Worksheets("Sheet2").Range("A1:M30").PrintOut
End If
End If
End Sub

 

[Dr_Phil]

Thanks a ton to you all for the help.....its all up an
running....thanks: