Mass amounts of buttons

[David Pick]

I am working on software that will do ticking for a theatre I work at.
I was hoping to have each seat represented by a button, I know
there's a way to make a control array in VB but I'm not sure how to
do it in VBA. Or if there's a way that I could create all the
buttons using code instead of the standard way that would be great too.
Any help would be greatly appreciated

 

[Peter T]

As you say a Control-array is not available in VBA. But an array of controls
or rather an array of class's and Withevents as control-type can be made to
work in a similar kind of way. Maybe you can adapt this example -

http://tinyurl.com/nsa6q

How big is your theatre !

Regards,
Peter T

 

[David Pick]

It seats around 700 people. Thanks for the help.

-David

 

[David Pick]

While that did work what I was hoping to do was when a seat was clicked
have it change color then when the user tells the computer to enter the
customers data into the table those seats would disappear is there any
way to do that? Thanks again.

 

[Guest]

Why not forget the buttons and just use the Worsheet_SelectionChange event to
do whatever you want? So if you click on a cell within a specified range
representing the seats, then they change colour and/or do whatever. To
disable a cell (seat), you can lock the cell and at the same time gray its
interior colour. If worksheet protection is set and EnableSelection is set to
xlUnlockedCells then you won't be able to click them. 700 buttons is a lot of
overhead and maintenance headache.

Greg

 

[Peter T]

Lightly tested following gives you 700 labels (seats) on a form to click &
book or unbook and change colour, with read/write to cells (table) on a
sheet.

700 controls on a form might raise a few eyebrows around here!

add a normal module, Class1, empty Userform1

Run SeatBooker to load the form

'normal module
Public grngSeats As Range

Sub SeatBooker()

Set grngSeats = ThisWorkbook.Worksheets("Sheet1").Range("A1:Y28")

UserForm1.Show

End Sub

Function fAvailSeats() As String
Dim n As Long, t As Long
Dim vSeats, v
Dim s As String

vSeats = grngSeats.Value

For Each v In vSeats
t = t + 1
If Len(v) Then n = n + 1
Next
fAvailSeats = t & " Seats : Remaining: " & t - n

End Function


' code in Class1

Public WithEvents lab As msforms.Label
Public rw As Long, col As Long
Public sSeatNo As String

Private Sub lab_Click()
Dim s As String
Dim v
v = grngSeats(rw, col).Value

If Len(v) Then
lab.BackColor = RGB(210, 210, 210)
If MsgBox("Un-book seat " & lab.Caption, vbYesNo) = vbYes Then
v = ""
End If
Else
'maybe an inputbox here to get & write
'customer details, ie the value v
v = 1
End If

grngSeats(rw, col) = v
lab.BackColor = IIf(Len(v), vbBlue, vbGreen)
lab.Parent.Caption = fAvailSeats
End Sub


' code in Userform1
Dim clsLabels() As Class1

Private Sub UserForm_Initialize()
Dim ctr As msforms.Label
Dim r As Long, c As Long, rr As Long, cc As Long
Dim vSeats
Const cLabW As Single = 21
Const cLabH As Single = 13.5
Const cGap As Single = 1.5

vSeats = grngSeats.Value
rr = grngSeats.Rows.Count
cc = grngSeats.Columns.Count
ReDim clsLabels(1 To rr, 1 To cc)
Me.BackColor = vbWhite
Me.Height = rr * (cLabH + cGap) + 21
Me.Width = cc * (cLabW + cGap)

For r = 1 To rr
For c = 1 To cc
Set clsLabels(r, c) = New Class1
Set ctr = Me.Controls.Add("Forms.Label.1")
With ctr
.Left = (c - 1) * (cLabW + cGap)
.Top = (r - 1) * (cLabH + cGap)
.Height = cLabH
.Width = cLabW
.BorderStyle = fmBorderStyleSingle
.TextAlign = fmTextAlignCenter
.Caption = Chr$(64 + c) & r
.BackColor = IIf(Len(vSeats(r, c)), vbBlue, vbGreen)
End With
Set clsLabels(r, c).lab = ctr
clsLabels(r, c).rw = r
clsLabels(r, c).col = c
Next
Next
Me.Caption = fAvailSeats
End Sub

Regards,
Peter T

 

[David Pick]

That does sound like a better idea than the buttons. Thanks for the
help and I'll give it a try.

-David

 

[David Pick]

Thanks Peter that works really well. The only problem I'm having now is
that I want to be able to select more than one seat at a time and then
I have all those seats put under one name. I tried using a command
button to do this but for some reason it stops selecting the seats when
I tried. Here's the code I tried. Any help would be greatly appreciated

'This is in Class1


Public WithEvents lab As MSForms.Label
Public WithEvents generate As MSForms.CommandButton
Public rw As Long, col As Long
Public sSeatNo As String


Private Sub lab_Click()
Dim s As String
Dim v
v = grngSeats(rw, col).Value

If Len(v) Then
lab.BackColor = RGB(210, 210, 210)
If MsgBox("Un-book seat " & lab.Caption, vbYesNo) = vbYes Then
v = ""
End If
'Else

'myMessage = InputBox("Costumer Name", Title, "Last Name, First
Name")
' v = myMessage
End If


grngSeats(rw, col) = v
lab.BackColor = IIf(Len(v), vbBlue, vbWhite)
lab.Parent.Caption = fAvailSeats
End Sub

Private Sub generate_Click()
Dim v
v = grngSeats(rw, col).Value

If Len(v) Then
myMessage = InputBox("Costumer Name", Data, "Last Name, First Name")
v = myMessage
End If

End Sub

 

[David Pick]

Much of the code you wrote is a bit beyond me as I am self taught from
just playing around in Excel so bear with me if the code I wrote seems
a little stupid. Thanks

-David

 

[David Pick]

I actually figured it out myself. Thanks for all the help.

-David

 

[Peter T]

Glad you've managed to get this working but it is important you understand
the Class method. Start with a small number of controls. You may find it
easier to store the array reference to the Class's as global in a normal
module rather than in the userform

Public clsLabels() As Class1

Then you can refer to a control from anywhere in your project

clsLabels(r,c).lab

If you do that in the forms unload event -
Erase clsLabels

In the example I only gave the Label click event. However the mouse down
event might give you more flexibility say for selecting multiple seats. Eg
if control is pressed change the label (seat) colour and store its location
in an array declared globally in a normal module. Then click on any on these
to bring up another msgbox asking to book all these temporarily selected
seats.

I think there's quite a lot you could add, eg different blocks of seats with
different prices, layout with aisles.

With so many controls be aware you are in uncharted territory. In the
original thread I referred you to Tom Olgivy mentioned a possible limit of
411 in Excel97. This limit doesn't hit me in any version, possibly because
the events are trapped in individual classes rather than all in the Userform
module.

After a period of time working with this could you drop a quick follow up
and share your experience as to whether or not so many controls have worked
successfully or caused problems

Regards,
Peter T

 

[David Pick]

Actually thats what I did for multiple seats. I switched to a MouseDown
event and after every seat was clicked I put a 1 into the corresponding
cell. Then when a button was pressed I ran a loop to see which cells
had a 1 in them and inputed a user given name to those cells.

As of right now it seems to be working rather well. I showed it to my
teacher today (I'm a high school student doing this to save my school
some money on a commerical project) and he really liked it and so far
its running very fast and I haven't had a crash yet.

My plans right now are to make the seats look like our actual
auditorium which might be a little challanging because most of our rows
are not the same length but I think I can figure out a way to do it.
Though any ideas would be appriciated. When thats finished we have a
ticket printer which I hope to get working with my program.

Once again thanks for all the help and I'll keep you posted on how
things are going.

- David

 

[David Pick]

Oh I forgot to mention I'm working with Excel 2007 (beta) on one
machine and Excel 2003 on the rest. So far I haven't hit any limit but
if I do I'll let you know.

- David

 

[Peter T]

Trust your teacher is impressed enough to mark a higher grade next time!

Actually thats what I did for multiple seats. I switched to a MouseDown
event and after every seat was clicked I put a 1 into the corresponding
cell. Then when a button was pressed I ran a loop to see which cells
had a 1 in them and inputed a user given name to those cells.

I wouldn't change cells at this stage, redim preserve an array to store
temporarily selected seats, also at same time change colour. Add another
button to the form to press to 'book' these seats(s) or cancel. Update.

To cater for irregular sheet rows work out how to put labels on the form in
a logical layout perhaps include aisles. Instead of storing the ref's to the
class's in a 2-D array you could store them in a collection.

'in a normal module
Public gColSeats as collection

in the form's initialize event
dim cls as Class1

set gColSeats = new collection

in the loop
set cls = new Class1
(declare further properties such a variable for the Seat-ref in the class
module, eg Public sRef as string).
add all the properties to the cls ref as before, particularly the label but
other things such as seat-ref, eg
cls.sRef = "T20"

also in the loop add a reference to the class to the collection -
gColSeats.additem cls, seat-Ref

From now on you can refer to items in the collection as
colSeats(n).lab ' n being the index in the collection, or by Key -
colSeats("T20").lab

In the form's unload event
set colSeats = Nothing

I've been messing around with this, switch to different theatre events on
the same form, add & store booking references, multiple bookings, seat
pricing, etc. It has potential !

Regards,
Peter T

 

[David Pick]

I wouldn't change cells at this stage, redim preserve an array to store
temporarily selected seats, also at same time change colour. Add another
button to the form to press to 'book' these seats(s) or cancel. Update.

To cater for irregular sheet rows work out how to put labels on the form in
a logical layout perhaps include aisles. Instead of storing the ref's to the
class's in a 2-D array you could store them in a collection.

I did a bit of googling and I think I know what a collection what never
having worked with one before I'm not exactly how it works or what the
advantages of it are. If you could explain what your suggesting with
the collection a little further that would be great. Thanks

As for the multiple seat thing when a user clicks a seat a second time
it removes the 1 so for temporarily selected seats what I wrote seems
to be working fine. I guess I could test weather that seat that was
clicked had actually been sold or not and not bother asking the user if
they want to deselect it when it hasn't been sold yet.

Another Issue I'm now looking into is adding more information than
simply a costomers name, such as an email address, phone number, or
weather it was an adult ticket or student, I considered just adding the
info on after their name but that doesn't seem to work to well. Is it
possible to have multiple lines inside of a cell? Thanks

- David

 

[Peter T]

Try searching this ng for "withevents new-collection" (without the quotes).
I think I've posted some examples so you could include my name, but that
would only limit your search.

The main advantages to using a collection vs an array is you can refer to an
item both by index and by 'Key', also easier to add new items instead of
Redim-Preserve the array (though latter not relevant with a pre-determined
number of items as in this case). On reflection probably easier to go with a
1-D array, add an additional property in the class

Public id as long

in the form initialise event
redim clsLabels(1 to knownNoOfSeats)
in the loop
ctr = ctr + 1
set clsLabels(ctr) = new Class1
clsLabels(ctr).id = ctr

Later when you click on a label you can store id somewhere, then later refer
back to the label
clsLabels(stored-id).lab

Rather than booking a seat on each click, temporarily select them storing
their id's. From another button click to book them or cancel.

Increment a booking reference, some text boxes to input fields such as name,
email etc. On confirming the booking store the booking ref & fields in same
row on another sheet (next row down each time). Put only the booking ref in
the booked cells (same ref in multiple cells if multiple booking), IOW
change v = 1 to v = ref

Regards,
Peter T

 

[David Pick]

Hmm I was actually thinking that since we have we showings of each
production we do I could use each sheet to be a different night. Then I
could create a drop down menu so when the user wanted to sell for a
different night it could just change the active sheet.

- David

 

[Peter T]

Absolutely, I had implemented a just that the other day when I said "switch
to different theatre events on
the same form".

A list of Events (showings) in a cell column to populate the combo, in an
adjacent column location in the wb for the event. This might be a dedicated
sheet, or a range in a sheet (same sheet perhaps to store multiple events).

First to load the seat plan with a few additional controls, such as the
combo. Select the event from the combo and label colours change to show
booked or free sheets for that event, continue booking.

In the original example the cell range simulated the seat plan layout.
That's not necessary, particularly if the layout is not a regular single
block as is typically the case. Assign an index for each seat, say 1 to 700.
Each seat index can then be assigned it's theatre seat ref, eg T20. Ie just
a single column to store the booking references assigned to the seat
'index', serving the secondary purpose of indicating that seat booked. The
booking ref and associated details (fields) stored elsewhere.

On sheet in the wb devise a mapping system purely for positioning the labels
on the form and linking the respective seat index's.

You've probably moved on from the example I posted but it would be an idea
to change following (in the form's initialize code previously posted)

..Caption = Chr$(64 + c) & r
to
..Caption = Chr$(64 + r) & c

Excel's letter-number notation is the reverse of how seats are numbered in a
theatre!

Regards,
Peter T

 

[David Pick]

So i've been trying to create new sections of seats by making different
ranges of cells that were the same size as the number of seats I
needed.
'in module1

Set grngSeats = ThisWorkbook.Worksheets("Sheet1").Range("A1:I1")
Set BRSeats = ThisWorkbook.Worksheets("Sheet1").Range("A2:L13")

Then when I tried loading them into the userform I got an object
required error. Heres the code I used in the userform

vSeats = BRSeats.Value
rr = BRSeats.Rows.Count
cc = BRSeats.Columns.Count
ReDim clsLabels(1 To rr, 1 To cc)
'Me.BackColor = vbWhite
'Me.Height = rr * (cLabH + cGap) + 200
'Me.Width = cc * (cLabW + cGap) + 200


For r = 1 To rr
For c = 1 To cc
'Set cls = New Class1


Set clsLabels(r, c) = New Class1
Set ctr = Me.Controls.Add("Forms.Label.1")
With ctr
.Left = (c - 1) * (cLabW + cGap) + 30
.Top = (r - 1) * (cLabH + cGap)
.Height = cLabH
.Width = cLabW
.BorderStyle = fmBorderStyleSingle
.TextAlign = fmTextAlignCenter
.Caption = Chr$(64 + r) & c
.BackColor = IIf(Len(vSeats(r, c)), vbRed, vbWhite)
End With
If ActiveSheet.Cells(r, c).Text = 0 Then
With ctr
.BackColor = vbWhite
End With
ActiveSheet.Cells(r, c).Value = ""
End If
If ActiveSheet.Cells(r, c).Text = 1 Then
With ctr
.BackColor = vbWhite
End With
ActiveSheet.Cells(r, c).Value = ""
End If
Set clsLabels(r, c).lab = ctr
clsLabels(r, c).rw = r
clsLabels(r, c).col = c
Next
Next

Any ideas on what I did wrong. Thanks

- David

 

[Peter T]

Many ways to do this, personally I'd only use a single column in the sheet
as I suggested earlier. Here's one way for your multiple range area approach
(only partial code).

'normal module
Public grngSeats As Range
Public clsLabels() As Class1 ' will be a 1-D array

' Class1
Option Explicit
Public WithEvents lab As msforms.Label
Public rSeat As Range
Public sRef
Public id As Long

Private Sub lab_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' limited test

rSeat = rSeat.Value + 1
MsgBox id & vbCr & rSeat.Address & vbCr & sRef
lab.BackColor = IIf(Len(rSeat.Value), vbBlue, vbGreen)

End Sub

'Userform1
Option Explicit
Private Sub UserForm_Initialize()
Dim ctr As msforms.Label
Dim r As Long, c As Long
Dim i As Long, cnt As Long
Dim sRef As String
Dim sAddr As String

Dim cel As Range, ra As Range
Dim maxCols As Long, nRows As Long
Dim lt As Single, tp As Single

Const cLabW As Single = 21
Const cLabH As Single = 13.5
Const cGap As Single = 1.5
Const cHaisle = 11, cVaisle = 7

sAddr = "e1:I3, c4:K10, a11:M15"
Set grngSeats = ThisWorkbook.Worksheets("Sheet1").Range(sAddr)

cnt = grngSeats.Count
ReDim clsLabels(1 To cnt)
nRows = 0
For Each ra In grngSeats.Areas
If ra.Columns.Count > maxCols Then
maxCols = ra.Columns.Count
End If
nRows = nRows + ra.Rows.Count
Next

ReDim clsLabels(1 To grngSeats.Count)
Me.BackColor = vbWhite
Me.Height = nRows * (cLabH + cGap) + (2 * cGap) + 21 + cLabH / 2
Me.Width = maxCols * (cLabW + cGap) + (2 * cGap) + cLabW / 2

For Each ra In grngSeats.Areas
For Each cel In ra
r = cel.Row: c = cel.Column
i = i + 1
Set clsLabels(i) = New Class1
Set ctr = Me.Controls.Add("Forms.Label.1")

With ctr
lt = (c - 1) * (cLabW + cGap)
If c >= cVaisle Then
lt = lt + cLabW / 2
End If
.Left = lt
tp = (r - 1) * (cLabH + cGap)
If r >= cHaisle Then
tp = tp + cLabH / 2
End If
.Top = tp
.Height = cLabH
.Width = cLabW
.BorderStyle = fmBorderStyleSingle
.TextAlign = fmTextAlignCenter
sRef = Chr$(64 + r) & c
.Caption = sRef
.BackColor = IIf(cel, vbBlue, vbGreen)
End With

Set clsLabels(i).lab = ctr
Set clsLabels(i).rSeat = cel
clsLabels(i).id = i
clsLabels(i).sRef = sRef
Next
Next
End Sub

Private Sub UserForm_Terminate()
Erase clsLabels
Set grngSeats = Nothing
End Sub

(only some of this code would normally be in the initialize event)

In usage, store the id on clicking a label in some public array, then later
to refer back
clsLabels(stored-id).lab

Regards,
Peter T