Mass amounts of buttons
 

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

Mass amounts of buttons
 

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" wrote in message
ps.com...
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

Mass amounts of buttons
 

It seats around 700 people. Thanks for the help.

-David

Mass amounts of buttons
 

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.

Mass amounts of buttons
 

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

"David Pick" wrote:

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.

Mass amounts of buttons
 

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" wrote in message
oups.com...
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.

Mass amounts of buttons
 

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

-David

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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

-David

Mass amounts of buttons
 

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" wrote in message
ps.com...
I actually figured it out myself. Thanks for all the help.

-David

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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" wrote in message
oups.com...
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

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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" wrote in message
ups.com...


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

Mass amounts of buttons
 

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

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" wrote in message
ups.com...


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

Mass amounts of buttons
 

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" wrote in message
ps.com...
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 wrote:

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" wrote in message
ups.com...


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

Mass amounts of buttons
 

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

Mass amounts of buttons
 

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


"David Pick" wrote in message
oups.com...
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

Mass amounts of buttons
 

Now that I have everything else working I wanted to have seats that
were selected and then ordered change from blue/green to red. To do
this I decided I would try and store there id to an array called
selectedseats() inside of the class but I couldn't get the counter to
work right. Here's my code, any ideas? Thanks.

Public WithEvents lab As MSForms.Label
Public rSeat As Range
Public sRef
Public id As Long
Public rw As Long, col As Long
Public sSeatNo As String
Public z As Integer

Private Sub lab_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
Dim s As String
Dim v, b, d
Dim sprice, aprice
Dim r, c, f
sprice = 5
aprice = 10


Dim selectedseats()
'ReDim selectedseats(30)
d = d + 1
If Button = 2 Then
v = rSeat.Value
'MsgBox (sAddr(rw, col).Value)
If Len(v) Then
lab.BackColor = RGB(210, 210, 210)
If MsgBox("Un-book seat " & lab.Caption, vbYesNo) = vbYes Then
rSeat.Value = ""
v = rSeat.Value
End If
Else
'v = v + 1
rSeat.Value = 1

End If



'MsgBox id & vbCr & rSeat.Address & vbCr & sRef
lab.BackColor = IIf(Len(rSeat.Value), vbGreen, RGB(210, 210, 210))
End If

If Button = 1 Then
v = rSeat.Value

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

End If


'MsgBox id & vbCr & rSeat.Address & vbCr & sRef
lab.BackColor = IIf(Len(rSeat.Value), vbBlue, RGB(210, 210, 210))
End If

ReDim Preserve selectedseats(30)
selectedseats(d) = id
For f = 1 To 3
MsgBox (selectedseats(f))
Next
MsgBox (d)

End Sub


-David

Mass amounts of buttons
 

Just change the colour of a 'temporarily' selected seat(s)/label(s) to say
amber. When the booking is confirmed/cancelled change to booked/available
colour.

I haven't followed your code but there's no point to maintain an
'availability or 'booked' array in in each of your 700 class's, would need
to update changes in one to all the other 699. Maintain as Public in a
normal module, or in another 'single' class that stores all global
properties.

Did the example I posted over a week ago answer your issue at that time.

Regards,
Peter

"David Pick" wrote in message
oups.com...
Now that I have everything else working I wanted to have seats that
were selected and then ordered change from blue/green to red. To do
this I decided I would try and store there id to an array called
selectedseats() inside of the class but I couldn't get the counter to
work right. Here's my code, any ideas? Thanks.

Public WithEvents lab As MSForms.Label
Public rSeat As Range
Public sRef
Public id As Long
Public rw As Long, col As Long
Public sSeatNo As String
Public z As Integer

Private Sub lab_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
Dim s As String
Dim v, b, d
Dim sprice, aprice
Dim r, c, f
sprice = 5
aprice = 10


Dim selectedseats()
'ReDim selectedseats(30)
d = d + 1
If Button = 2 Then
v = rSeat.Value
'MsgBox (sAddr(rw, col).Value)
If Len(v) Then
lab.BackColor = RGB(210, 210, 210)
If MsgBox("Un-book seat " & lab.Caption, vbYesNo) = vbYes Then
rSeat.Value = ""
v = rSeat.Value
End If
Else
'v = v + 1
rSeat.Value = 1

End If



'MsgBox id & vbCr & rSeat.Address & vbCr & sRef
lab.BackColor = IIf(Len(rSeat.Value), vbGreen, RGB(210, 210, 210))
End If

If Button = 1 Then
v = rSeat.Value

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

End If


'MsgBox id & vbCr & rSeat.Address & vbCr & sRef
lab.BackColor = IIf(Len(rSeat.Value), vbBlue, RGB(210, 210, 210))
End If

ReDim Preserve selectedseats(30)
selectedseats(d) = id
For f = 1 To 3
MsgBox (selectedseats(f))
Next
MsgBox (d)

End Sub


-David

Mass amounts of buttons
 

Yes the example helped and everything is working the way I want it to
now. Thanks so much for all your help.

-David

Mass amounts of buttons
 

Now that I have the userform working the way I want it to I wanted to
create a listbox that would hold every customer who had bought tickets.
So far I've been able to add all the names but I can't get the listbox
not to add a name if it already exists in the listbox. What I want it
to do is count how many duplicates it finds and then put that number in
a column next to the name in the listbox to represent the number of
seats they had bought. Here's the code I've tried so far.


Private Sub UserForm_Initialize()
Dim c, r, f
f = 0
For c = 1 To 39
For r = 1 To 28
If ActiveSheet.Cells(r, c).Value = Empty Then
Else
f = f + 1
If ListBox1.List(f) = ActiveSheet.Cells(r, c).Value Then
Else
ListBox1.AddItem ActiveSheet.Cells(r, c).Value
End If

End If
Next
Next
End Sub


Thanks again for any help you can give.

- David

Mass amounts of buttons
 

I got the listbox to display the names of the costumers by doing this:

Private Sub UserForm_Activate()
Dim data()
Dim f
f = 0
'For c = 1 To 39
' For r = 1 To 28
' If ActiveSheet.Cells(r, c) = Empty Then
' Else
' data(c, 1) = ActiveSheet.Cells(r, c).Value
' End If
' Next
'Next

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("A1:AM23")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

NoDupes.Remove (1)
ReDim data(1 To NoDupes.Count, 1 To 2)
For Each Item In NoDupes
f = f + 1
data(f, 1) = Item
'data(f, 2) = f
Next Item


'MsgBox NoDupes.Count


ListBox1.List = data
ListBox1.ColumnCount = 2
End Sub

But I can't get it to count how many instances of that costumers name
there are. Any help would be great. Thanks

- David

Mass amounts of buttons
 

For the duplicates issue search this ng for "duplicates collection" and
"duplicates dictionary". The simpler collection method is probably enough,
in essence attempting to add a duplicate throws an error that you can trap.

If I follow you are putting customer names in the grid of cells as relates
to seats. As it seems you are developing a more sophisticated system I
wouldn't do that. Try and break down into a set of linked databases

DB1 Seats
Index, Block, Row (letter), Col (number), Price code, and maybe physical
coordinates for layout onto the userform.

No need to lay seats in cell rows/columns as in the original example posted
way back. In the code only a 1-D array of 'with-events label' classes,
properties include Index (id), Event, booked/unbooked.

DB2 Events
Index/ref, Name, Date/Time, Category, Price1, Price2

DB3 Customers
Index/ref (eg SMITHX001), Name, Initial, Address1, Email, Notes

DB4 Booking ref's
Index/ref, DB3 Cust-ref, DB2 Event-Index, Seats (ie DB1-Index's as comma
separated single string), Cost, Paid Y/N, Payment method

DB5 Seats/Events/Booking refs
DB1 Seat-index, DB2 Event-Index1, Event-Index2, etc

This might be a single table with seats index's in col-A and events in Col's
B, C etc. Or separate two column tables for each event.

As seats are booked the booking ref is entered under the event against the
seat index.

As you can see each table has at least one 'key' in common leading to a
pseudo relational database.

DB's don't need to be in the same workbook and that doesn't require
'linking' wb's. Each DB will require it's own method of handling and within
the constraints of Excel (row/col limits, not too much on any sheet or
Workbook).

Back to your unique customers, when it comes to booking start typing
customer's name. 'Lookup' DB3 populating a small list that of partial
match's if any. If no match make a new ref, eg first 6 characters of name
(padded with first initial & X's if less 6) followed by unique three digit
number. Lookup requires sorted ref's, could maintain two customer DB's, main
sorted and a temporary small list of new customers to be added & sorted into
the main DB later.

When you have forthcoming Beethoven and wrestling events you'll be able to
contact customers who've previously booked similar categories.

Something for you to consider !

Regards,
Peter T


"David Pick" wrote in message
oups.com...

I got the listbox to display the names of the costumers by doing this:

Private Sub UserForm_Activate()
Dim data()
Dim f
f = 0
'For c = 1 To 39
' For r = 1 To 28
' If ActiveSheet.Cells(r, c) = Empty Then
' Else
' data(c, 1) = ActiveSheet.Cells(r, c).Value
' End If
' Next
'Next

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("A1:AM23")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

NoDupes.Remove (1)
ReDim data(1 To NoDupes.Count, 1 To 2)
For Each Item In NoDupes
f = f + 1
data(f, 1) = Item
'data(f, 2) = f
Next Item


'MsgBox NoDupes.Count


ListBox1.List = data
ListBox1.ColumnCount = 2
End Sub

But I can't get it to count how many instances of that costumers name
there are. Any help would be great. Thanks

- David

Mass amounts of buttons
 

Hmmmm thanks for the advice. I do like the idea of implementing linked
databases though I am unsure of how to go about doing it. If you could
give me some sort of simple example that would great. I also did get
the unique costumer function to work using this code. Though I'm sure
it's going to be extreamly slow it has worked well with the minimal
testing I've done so far.

Dim data()
Dim f, d
f = 0
UserForm2.Caption = "Print Tickets"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("A1:AM23")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

NoDupes.Remove (1)
ReDim data(1 To NoDupes.Count, 1 To 4)
For Each Item In NoDupes
f = f + 1
For c = 1 To 39
For r = 1 To 28
If ActiveSheet.Cells(r, c).Value = Item Then
d = d + 1
If UCase(Item) = Item Then
data(f, 1) = Item
data(f, 2) = d
data(f, 3) = "Paid"
data(f, 4) =
ThisWorkbook.Worksheets("Sheet5").Cells(r, c).Value
Else
data(f, 1) = Item
data(f, 2) = d
data(f, 3) = "Not Paid"
data(f, 4) =
ThisWorkbook.Worksheets("sheet5").Cells(r, c).Value
End If
End If
Next
Next
d = 0
Next Item


'MsgBox NoDupes.Count


ListBox1.List = data
ListBox1.ColumnCount = 4

Mass amounts of buttons
 

Linked Database's, as this is so far removed from the original subject it
would be much better to start a new topic. Not least since this thread is
getting old by now there's probably only you and me here so I'm sure you'll
get a range of suggestions from others in a new thread.

Describe the sets of data (tables) with a sample of fields in each and how
they should be linked. It's a fairly typical task, like sales / parts /
customers / invoices / inventory.

Having got some ideas for a basic method may then lead you to various
sub-topics, eg

- Method for entering customers and avoiding duplicates if already exist.
Depending on the size of your data that Collection method might indeed be
slow. What I alluded to previously is a starter, Lookup in a sorted list is
very fast even in a large list.

- Where to store & read/write databases, multiple sheets same workbook,
multiple workbooks. Possibly even plain text delimited files which are quick
to retrieve as an array & dump or append and 'safe'.

Looks like you are embarking on quite a project, good luck!

Regards,
Peter T

"David Pick" wrote in message
oups.com...
Hmmmm thanks for the advice. I do like the idea of implementing linked
databases though I am unsure of how to go about doing it. If you could
give me some sort of simple example that would great. I also did get
the unique costumer function to work using this code. Though I'm sure
it's going to be extreamly slow it has worked well with the minimal
testing I've done so far.

Dim data()
Dim f, d
f = 0
UserForm2.Caption = "Print Tickets"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("A1:AM23")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

NoDupes.Remove (1)
ReDim data(1 To NoDupes.Count, 1 To 4)
For Each Item In NoDupes
f = f + 1
For c = 1 To 39
For r = 1 To 28
If ActiveSheet.Cells(r, c).Value = Item Then
d = d + 1
If UCase(Item) = Item Then
data(f, 1) = Item
data(f, 2) = d
data(f, 3) = "Paid"
data(f, 4) =
ThisWorkbook.Worksheets("Sheet5").Cells(r, c).Value
Else
data(f, 1) = Item
data(f, 2) = d
data(f, 3) = "Not Paid"
data(f, 4) =
ThisWorkbook.Worksheets("sheet5").Cells(r, c).Value
End If
End If
Next
Next
d = 0
Next Item


'MsgBox NoDupes.Count


ListBox1.List = data
ListBox1.ColumnCount = 4

Mass amounts of buttons
 

Well Peter thanks for all your help and I'll guess I'll start a new
thread about linked databases.

- David