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

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

It seats around 700 people. Thanks for the help.

-David

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.

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.

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.

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

-David

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