My Theatre Booking System

[Dark RyNo]

Im Making a theatre booking system for a project in my ICT class.
What I have so far, is the layout of my theatre with each seat as a cell. They have drop down boxes that I can set as Available, Adult, Concessions or Family Ticket. Then formulas and stuff figure out how much money I bring in.
This all works.
I would like to do 2 things.
Firstly, make a macro on a button to reset all the cells in the theatre layout back to "Avilable"
Any help?

Secondly, I would like a second sheet that, when I change a cell to Adult or the other "sold" options, I want it to tell me which tickets I need to write out.
So it would have a list of sold seat numbers and whether they are Adult, Concession or Family.
Is there any way to do this?

Thanks, this website already gave me the help on the drop down boxes.

 

[Smitty]

Welcome to the Board!

Here's the code:<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> MakeAvailable()
    <SPAN style="color:#007F00">'   Adjust range to suit</SPAN>
    Range("A1:C15") = "Available"<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

And here's an example of a way to count seats:

Book11
	A	B	C	D
1	Row1	Row2	Row3
2	Adult	Available	Available
3	Available	Concession	Available
4	Available	Available	Family
5	Adult	Available	Available
6	Available	Concession	Available
7	Available	Family	Family
8	Adult	Available	Available
9	Available	Concession	Available
10	Family	Available	Family
11	Concession	Available	Available
12	Available	Concession	Available
13	Available	Available	Family
14	Adult	Available	Available
15	Family	Concession	Concession
16	Available	Family	Family
17
18	SeatsBooked
19	Adult	4
20	Concession	7
21	Family	9
22
23	SeatsRemaining	25
Sheet1

Hope that helps,

Smitty

 

[Dark RyNo]

Thanks. The reset thing worked, I just had to change the "Available" to "" as there are gaps between different areas of the theatre and they came up with Available.
Is there a way to say, if the cell is blank, then set it to "Available"?
As for the other bit.
I already have the Countif set up.
I need my second sheet to tell me exactly which seats have been sold and for which group so I can easily find out which tickets to print.
So the first sheet is like yours.
The second would be:

Seats Sold | Type | Price
C5 ---------- adult - £3
G7-G11----- Family £10
and so on.
(Family Tickets are a row of 4)
It would have to identify which cells ive made sold and put it into the next available row in this table.

 

[Dark RyNo]

Here is my sheet.
http://get.filehosting.org/pq23158.xls
You can see what I want to do with the second sheet. That is just an example.
Ignore the third sheet.
That second sheet, th values were typed by hand, is there a way of making that happen by a formula?
They correspond to the sold seats on the first sheet if you hadnt noticed.

 

[Zack Barresse]

..I need my second sheet to tell me exactly which seats have been sold and for which group so I can easily find out which tickets to print...

Hi,

How about using autofilter? I have not looked at your xls book yet, but that sounds something close to what you want. Maybe?

(Hiya Smitty!)

 

[Dark RyNo]

No.
I appreciate the help but that is not what i need.
I think that analyses a table from what I can figure of it.
I have a graphical representaion of my theatre.
Take a look at the sheet, it will make things clearer.

 

[Smitty]

Sorry Bud, but your wb wouldn't open for me.

Take a look tot eh bottom of the page for the link to Colo's HTML Maker, which will allow you to post a shot of your sheet(s) like I did.

Smitty

(Heya Zack! Wait 'til I tell you about Romper Room!)

 

[tusharm]

You should reconsider the design of your system. This is the perfect problem for a a userform shown modeless. It requires very little work.

Of course, since this displays a userform modeless, it will only work with XL2000 or later.

For my tests I did the following. If you want, I can email you the workbook. Ask for the 'theatre booking system' workbook. This email offer is good for at least a few days. After that, it's good until I empty my 'temporary' folder.

The rest of this note describes the system I designed and tested.

The front of the theatre was row 2.

The seats were one to a cell starting with B4. I had an aisle in columns A and F and rows 3, 15, and 16.

An available seat has a status of U and no background. A seat sold to an adult has a status of A and the cell a light yellow background. A concession sale: C and light blue, and a family seat: F and light green. Ticketed seats have a pattern of light-gray-8.

Given this visual scheme, the overall status of theatre sales is immediately obvious to the user!

In my tests, cells B4:E14, G4:J14, B17:E27, and G17:J27 were the seats and contained the formula
=CHAR(ROW()-ROW($B$4)+65)&(COLUMN()-COLUMN($B$4)+1)&"-U"

This meant that the seats were labeled A1...A9, B1...B9,...X1...X9. The last part '-U' is the status indicator. The cells were formatted 'centered' and 'bold'

The next part of the system was a userform. It had one label reading
"Select one or more seats
Then click the appropriate button"

and six buttons, the names of which can be inferred from the code below. The userform module code was:

Option Explicit
Const ColorIdxConcession As Long = 34, _
    ColorIdxAdult As Long = 19, _
    ColorIdxFamily As Long = 35, _
    PatternTicketed As Long = xlGray8

    Sub doCells(Sel As Range, CharCode As String)
        Dim aCell As Range
        For Each aCell In Sel
            aCell.Formula = Left(aCell.Formula, Len(aCell.Formula) - 2) & CharCode & """"
            Next aCell
        End Sub
Private Sub Adult_Click()
    Selection.Interior.ColorIndex = ColorIdxAdult
    doCells Selection, "A"
    End Sub

Private Sub Available_Click()
    Selection.Interior.ColorIndex = xlNone
    Selection.Interior.Pattern = xlNone
    doCells Selection, "U"
    End Sub

Private Sub Concession_Click()
    Selection.Interior.ColorIndex = ColorIdxConcession
    doCells Selection, "C"
    End Sub

Private Sub Done_Click()
    Me.Hide
    End Sub

Private Sub Family_Click()
    Selection.Interior.ColorIndex = ColorIdxFamily
    doCells Selection, "F"
    End Sub

Private Sub Ticketed_Click()
    Selection.Interior.Pattern = xlGray8
    End Sub

The final part of the system was the main module (a standard module) with the code

Option Explicit

Sub main()
    UserForm1.Show vbModeless
    End Sub

 

[Dark RyNo]

Any other ideas? I need it to scan the seats looking for ones that are not blank or available. Then, put that cell name into the first row of a table, then it looks at that cell and finds if its Adult, Cheap or Family. It then puts that into the second row of the table. Then it takes that second row and reads the prices off my prices list.
Download the sheet, all will be come clear.

 

[Dark RyNo]

I have a new problem.
Ive sacrificed the drop down boxes for a way to randomise it. But that doesnt really matter because I still have my other one saved. This just means I can simulate it without having to put in loads of values.
The only problem is that, Because my family tickets are in 4s, I keep coming up with values like 478.5 tickets sold. (my countif function counts the number of family tickets and divides by 4). Is there any way to make it random, but only put families as 4 next to each other, or just so it always makes the number of family tickets a multiple of 4.