ExcelBanter - View Single Post

stnkynts

I wasn't sure how much freedom you are allowed to have in making this spreadsheet. That and I dont want to do your homework for you. (Nor should you want others to completely do everything for you). So I have decided to do everything in a macro so you can see some of the neat things that can be done. In a new spreadsheet in column A1, B1, C1, D1 respectively put Seat Type, Age, Ticket Price, Name. Add this macro to your spread sheet:

Code:

Option Explicit

Sub Macro1()
Dim seattest As String
Dim agetest As Integer
Dim nametest As String
Dim lastprice As Integer

Part1:
seattest = InputBox("What type of seat do you want to purchase ie Box Seat, Lower Deck, Upper Deck?")
Select Case seattest
    Case "Box Seat"
        Range("A6000").End(xlUp).Offset(1, 0).Value = "Box Seat"
        agetest = InputBox("How old is the the attendee (in years)?")
            Select Case agetest
                Case Is < 17
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 98 * 0.5
                Case Is  64
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 98 - 12
                Case Else
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 98
            End Select
    Case "Lower Deck"
        Range("A6000").End(xlUp).Offset(1, 0).Value = "Lower Deck"
        agetest = InputBox("How old is the the attendee (in years)?")
            Select Case agetest
                Case Is < 17
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 54 * 0.5
                Case Is  64
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 54 - 12
                Case 17 To 64
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 54
                Case Else
                    MsgBox ("Incorrect Entry")
                    GoTo Part1
            End Select
    Case "Upper Deck"
        Range("A6000").End(xlUp).Offset(1, 0).Value = "Upper Deck"
        agetest = InputBox("How old is the the attendee (in years)?")
            Select Case agetest
                Case Is < 17
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 32 * 0.5
                Case Is  64
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 32 - 12
                Case 17 To 64
                    Range("B6000").End(xlUp).Offset(1, 0).Value = agetest
                    Range("C6000").End(xlUp).Offset(1, 0).Value = 32
                Case Else
                    MsgBox ("Incorrect Entry")
                    GoTo Part1
            End Select
    Case Else
        MsgBox "Incorrect Entry"
        GoTo Part1
End Select
    
nametest = InputBox("What is your name?")
    Range("D6000").End(xlUp).Offset(1, 0).Value = nametest

lastprice = Range("C6000").End(xlUp).Value
    MsgBox ("Your ticket price is $" & lastprice)

End Sub

Create a command button on the spreadsheet and assign the macro to it. Run the macro to see what happens. Pretty cool huh. Now here comes the great part. If you just turn this in your teacher may know you didnt come up with it. So take the time to ask and figure out what each portion of the code does. Figure out why and how things work. If you can explain that to your teacher you will get a great grade.