Object programming in Excel

[Scott]

I am trying to setup a simulation for a process flow of carts on a
factory floor and I want to make a simulation in Excel. What we do is
have cards that represent carts and we move the carts from machine to
machine according to the rules at each machine. This is all done with
paper cards on a desk. What I am thinking is putting obejects on the
screen of Excel, like command buttons or flow chart shapes, and then
visually move them from 1 location (cell) to another as my VB code
runs thru each increment of time and what is supposed to happen with
each cart. This is where I am struggling. I would like to have the
cart "object", what ever that is, have some additional properties.
Something like this:
Cart_1.movetime
Cart_1.timeatmachine
Cart_1.partcount
etc.
How and what would be the best "object" to use to be able to add
properties to like above and then change them as the VB program walks
thru a time range and moves the carts according to their individual
parameters.
I am not looking for someone elses program to do this, as I have seen
them, I want to set it up to run the way I want it to work.
I am pretty good with VBA so do hold back on a solution that would be
great but complicated to create.

I appreciate any help or direction you can give me.
Thanks
Scott

 

[Joel]

Type Cart

movetime As Integer
timeatmachine As Date
partcount As Long

End Type

Sub main()

Dim Cart_1 As Cart

Cart_1.movetime = 10
Cart_1.timeatmachine = DateValue("1:00")
Cart_1.partcount = 100

End Sub

 

[Scott]

I like the code you posted. I can definately use that and expand on
it.
What do you think would be the best object to use for a visual look? I
have been thinking of using a command button (activeX control) as I
can change a lot of its properties like color, text to better show
what each is and what is happening to each cart as it moves through my
system.

Thanks for the code! Most appreciated.
Scott Riddle

 

[Joel]

Have you ever built a VBA user form. On VBA menu : Insert - Userform

 

[Scott]

I have made several userforms. Why?
I was playing around with a command button to represent a cart and one
thing I noticed is that you can not select them and move them around
easily (with out being in design mode). I think I would like to have
the user be able to move the carts around manually if needed. I am
thinking of creating named ranges for each machine and then move the
carts into the named range area. I would have a sub to sort and stack
them at each machine and each would have a seperate que number to
control the order in which they move on to the next machine.

 

[Joel]

A userform has two modes modal and modeless. I think you want modeless. The
uwserform I'm referering to is not on the worksheet. Go to VBA and add a
userform. Put a couple of controls on the user form. The go to a VBA module
and run the userform. The first userform will automatically be called
userform1.

Sub Main

userform1.show

end main

[object.]Show modal



Settings

The settings for modal are:

Constant Value Description
vbModal 1 UserForm is modal. Default.
vbModeless 0 UserForm is modeless