Mass amounts of buttons

[David Pick]

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

 

[Peter T]

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]

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

-David

 

[David Pick]

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

 

[David Pick]

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

 

[Peter T]

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]

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

 

[Peter T]

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]

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

- David