My Theatre Booking System

Dark RyNo

New Member
Joined
Jan 21, 2005
Messages
8
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
ABCD
1Row1Row2Row3
2AdultAvailableAvailable
3AvailableConcessionAvailable
4AvailableAvailableFamily
5AdultAvailableAvailable
6AvailableConcessionAvailable
7AvailableFamilyFamily
8AdultAvailableAvailable
9AvailableConcessionAvailable
10FamilyAvailableFamily
11ConcessionAvailableAvailable
12AvailableConcessionAvailable
13AvailableAvailableFamily
14AdultAvailableAvailable
15FamilyConcessionConcession
16AvailableFamilyFamily
17
18SeatsBooked
19Adult4
20Concession7
21Family9
22
23SeatsRemaining25
Sheet1


Hope that helps,

Smitty
 

Dark RyNo

New Member
Joined
Jan 21, 2005
Messages
8
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

New Member
Joined
Jan 21, 2005
Messages
8
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Dark RyNo said:
..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

New Member
Joined
Jan 21, 2005
Messages
8
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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:
Code:
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
Code:
Option Explicit

Sub main()
    UserForm1.Show vbModeless
    End Sub
 

Dark RyNo

New Member
Joined
Jan 21, 2005
Messages
8
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

New Member
Joined
Jan 21, 2005
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top