Excel + VBA question from a "pre-Novice"

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

piamik2

Thread Starter
Joined
Mar 30, 2005
Messages
120
My husband and I are newly in charge of the house management for a small, non-profit community theater group. Our predecessor used a spreadsheet, but with no programming—just manually placing people in seats and then making a separate list of who was sitting where. Our theater puts on 3 productions per year, each production lasting for just 3 weekends. We have about 200 seats in our theater.

I first started putting this into Access with about 3 different tables to start with, but it wasn’t working as I’d like, I think because I really don’t know VBA. (Twenty years ago I programmed in SQL so I do have an understanding (if ancient) of databases, but the VBA thing I don’t know). Then I thought maybe I’d just start with a fairly simple spreadsheet and as I built knowledge of VBA I could move to Access. But, I see I really need a lot of VBA for Excel as well. My current thinking is that I might want to possibly—eventually—make a link between Excel and Access for this, but that is way out in the future.

I’m not even a novice when it comes to VBA. I have been playing with recording macros, and that’s helped some. I’ve done some tutorials too, but I still feel really ignorant. What I’m asking for is a little help to get me started.

I’ve been playing around with a hypothetical 6-seat theater with seats A1, A2, A3, B1, B2 & B3. (I'm trying to insert a picture of my spreadsheet here, but so far I'm just getting text. So, I attached it, or I tried to, so if there's an attachment, it's my hypothetical theater.) (OK, this is the 2d edit and I finally got the attachment on, don't know why I couldn't before.)Anyway, I plan to give each customer a customer #. What I want to do is then be able to either click on a seat or seat #, or enter a seat # elsewhere on the database and automatically be told either which customer # or customer name is in that seat. I keep getting stuck in the VBA because I don’t know how to tell the computer to move around the spreadsheet. I’m sure this is really simple to do, but I’m getting no where, and feeling more stupid by the minute. Of course, this is just the tip of the iceberg with this project, but it’s a good project with which I can learn a lot of new stuff. If anyone can recommend any good books/tutorials on VBA I’d welcome that too.
 

Attachments

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
First get your file uploaded. Hit the Go advanced button under the reply window here, then type a few words, then hit Manage attachments. Then hit Browse and get your filename into the box, then hit Upload, then hit Close this window, then hit submit reply. :)
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Okay, got it.

Bad news...

Before you start doing a layout, I suggest you do a "database"--you know, a flat file. After you get your flat file working the way you want, to return names, etc., THEN make your layout and set up links to the flat data, you know?

So, I would just put Date, Seat Number, Name.

Then, in your layout (later), you should be able to do a vlookup to the seat number, and return the name.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
We'll probably want to get Rollin' to give us some VBA to ensure you don't assign duplicate seats. :)
 
Joined
Aug 30, 2003
Messages
2,702
Maybe something like the attached would help.

Rightclicking in the grid area on Sheet1 brings up the customer from Sheet2.

(NB: haven't done much testing).
 

Attachments

piamik2

Thread Starter
Joined
Mar 30, 2005
Messages
120
Well, XL Guru, that's pretty neat. I'd have never thought to try something like that (and if I had, wouldn't have been able to program it). I can see why someone else suggested the flat file, but in my situation, this is better because the theater is very informal. We have sort of open seating, but people can call to reserve particular seats so what happens is a continual rearranging of the seating depending on patrons' needs--hearing impaired, adding one more to the group, etc. That's why I want to be able to see who I've placed where so I can see who I can move, if need be, and who can't be moved. I want to have customer #s so that I can fit the whole seating grid on one screen (numbers take up less space than names).

There's a whole lot more I want to do to this, but this is a great start. Now I just need to figure out what all that VBA means! Thanks a lot.

Ann
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top