# My Theatre Booking System

#### Dark RyNo

##### New Member
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
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
3AvailableConcessionAvailable
4AvailableAvailableFamily
6AvailableConcessionAvailable
7AvailableFamilyFamily
9AvailableConcessionAvailable
10FamilyAvailableFamily
11ConcessionAvailableAvailable
12AvailableConcessionAvailable
13AvailableAvailableFamily
15FamilyConcessionConcession
16AvailableFamilyFamily
17
18SeatsBooked
20Concession7
21Family9
22
23SeatsRemaining25
Sheet1

Hope that helps,

Smitty

#### Dark RyNo

##### New Member
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
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
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

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
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

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
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
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
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.

#### Dark RyNo

##### New Member
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.

Replies
3
Views
156
Replies
3
Views
426
Replies
20
Views
275
Replies
3
Views
232
Replies
2
Views
95

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.

### Which adblocker are you using?

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

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