Textbox and SpinControl - Class

[sarndt]

I created code in vba that will handle increasing/decreasing time values
using textboxes and spinbutton controls. The code works great if I reference
the textbox and spinbutton control exactly (for instance textbox1 and
spinbutton1). I then tried converting the code to a class to handle multiple
combinations of textboxes and spinbutton controls. But it doesn't work. Any
suggestions appreciated. Also how would I make sure the right textbox and
spinbutton controls are always used together?

Thanks

Steve

Private WithEvents TB As MSForms.TextBox
Private WithEvents SB As MSForms.SpinButton

Public iCur As Integer
Public strControl As String
Public strTimeChange
Public i As Integer

Public Property Set TBControl(obtNewTB As MSForms.TextBox)
Set TB = obtNewTB
End Property
Public Property Set SBControl(obtNewSB As MSForms.SpinButton)
Set SB = obtNewSB
End Property
Private Sub SB_Change()

Dim dtTime As Date
Dim Y As Integer

'If strTimeChange = "" Then
' MsgBox "Please click on a time to modify it"
' Exit Sub
'End If

dtTime = Format(TB.Value, "hh:mm AM/PM")

Y = SB.Value
If Y > i Then
TB.Value = Format(dtTime + TimeValue(strTimeChange), "hh:mm AM/PM")
Else
TB = Format(dtTime + 1 - TimeValue(strTimeChange), "hh:mm AM/PM")
End If
i = SB.Value

HighlightTime (iCur)

End Sub
Sub HighlightTime(iCur As Integer)

Dim iPos1 As Integer
Dim iPos2 As Integer

iPos1 = InStr(1, TB.Value, ":")
iPos2 = InStr(1, TB.Value, " ")

If iCur >= iPos2 Then
strTimeChange = "12:00:00"
TB.SelStart = 6
TB.SelLength = 2
ElseIf iCur >= iPos1 Then
strTimeChange = "00:01:00"
TB.SelStart = 3
TB.SelLength = 2
Else
strTimeChange = "01:00:00"
TB.SelStart = 0
TB.SelLength = 2
End If

TB.HideSelection = False

End Sub
Private Sub TB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift
As Integer)

KeyCode.Value = vbKeyReturn
strControl = TB.Name
HighlightTime (iCur)

End Sub

Private Sub TB_MouseDown(ByVal Button As Integer, ByVal Shift As Integer,
ByVal X As Single, ByVal Y As Single)

strControl = TB.Name
iCur = TB.SelStart
HighlightTime (iCur)

End Sub
Private Sub Class_Terminate()
Set TB = Nothing
Set SB = Nothing
End Sub

 

[joel]

How ar eyou initializing the Class? Every class need to get initialize
from a non class module. Since a class can't get called from th
worksheet directly you need to make the first call from a non clas
module

 

[sarndt]

I'm initalizing it in a module...Here's the code.

---------------------

Option Explicit
Dim mcolEvents As Collection
Sub InitializeEvents()
Dim objTextBox As OLEObject
Dim objSpinButton As OLEObject
Dim osh As Worksheet
Dim clsEventsTB As TBClass
Dim clsEventsSB As TBClass
Set osh = ThisWorkbook.Worksheets(1)
If mcolEvents Is Nothing Then
Set mcolEvents = New Collection
End If
'Loop through all the controls
For Each objTextBox In osh.OLEObjects
If TypeName(objTextBox.Object) = "TextBox" Then
'Create a new instance of the event handler class
Set clsEventsTB = New TBClass
'Tell it to handle the events for the text box
Set clsEventsTB.TBControl = objTextBox.Object
'Add the event handler instance to our collection,
'so it stays alive during the life of the workbook
mcolEvents.Add clsEventsTB
End If
Next
For Each objSpinButton In osh.OLEObjects
If TypeName(objSpinButton.Object) = "SpinButton" Then
'Create a new instance of the event handler class
Set clsEventsSB = New TBClass
'Tell it to handle the events for the text box
Set clsEventsSB.SBControl = objSpinButton.Object
'Add the event handler instance to our collection,
'so it stays alive during the life of the workbook
mcolEvents.Add clsEventsSB
End If
Next
End Sub

Sub TerminateEvents()
'Here the collection of classes is destroyed so memory will be freed up:
Set mcolEvents = Nothing
End Sub

---------------------

And the names on the textbox/spinbuttons on the control is textbox1 and
spinbutton1, textbox2 and spinbutton2.

What happens is the strTimeChange and dtTimes don't have values when
SB_Change occurs. Also, I would want to make sure that when a user
highlights a portion of a textbox such as TextBox1, they can only use SB1 to
update TextBox2 - not SB2 which should only update TextBox2, etc.

 

[joel]

I think your problem is very simple. Look at this line of code in th
Module code:

Set clsEventsTB = New TBClass

Everytime you create a new instance you destroy the old instanc
because clsEventsTB is a single variable. Make it a two dimensiona
array with the class assigned to one index and the name of the contro
as the second index in the array. When the event is triggered you nee
to get the name of the control and then look up the control name to fin
the class object in the array

 

[sarndt]

So if I have 8 textboxes on the worksheet, is the two dimensional array sized
as
clseventstb(1 To 8, 1 To 8) - with the class name in all 8 positions of the
first index and the textbox name in all 8 position of the second index?

 

[joel]

You can do it any way you want to as long as you save each instance o
the class along with the name. What you said is correct. I did cod
like this back college 30 years ago and a few times since. the Theor
hasn't changes, just the programming languages.

What you are doing is creating a min-database and adding new record
(in your case classes). The theory behind databases never really chang
over the years

 

[sarndt]

Hi Joel
I've been playing with this all day and can't figure out what you are
suggesting...Would you be able to provide code that you do what you are
suggesting?

Thanks
Steve

So if I have 8 textboxes on the worksheet, is the two dimensional array sized
as
clseventstb(1 To 8, 1 To 8) - with the class name in all 8 positions of the
first index and the textbox name in all 8 position of the second index?

I think your problem is very simple. Look at this line of code in the
Module code:

Set clsEventsTB = New TBClass

Everytime you create a new instance you destroy the old instance
because clsEventsTB is a single variable. Make it a two dimensional
array with the class assigned to one index and the name of the control
as the second index in the array. When the event is triggered you need
to get the name of the control and then look up the control name to find
the class object in the array.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=164025

Microsoft Office Help

.

 

[joel]

It was easier than I though. You want Textbox1 and Spinbutton1 assigne
to the same class then Textbox2 and Spinbutton2. Make sure your name
of the associated textboxes and the Spinbutton have the same numbe
assigned. Then use the code I modified below. You need only one clas
assigned for each set of textboxes and spinbuttons.


Option Explicit
Dim mcolEvents As Collection
Sub InitializeEvents()
Dim objTextBox As OLEObject
Dim objSpinButton As OLEObject
Dim osh As Worksheet
Dim clsEventsTB As TBClass
Dim clsEventsSB As TBClass
Dim bxName As String
Dim bxNumber As Integer
Dim SpinName As String

Set osh = ThisWorkbook.Worksheets(1)
If mcolEvents Is Nothing Then
Set mcolEvents = New Collection
End If

'Loop through all the controls
For Each objTextBox In osh.OLEObjects
If TypeName(objTextBox.Object) = "TextBox" Then
'Create a new instance of the event handler class
Set clsEventsTB = New TBClass

'Tell it to handle the events for the text box
Set clsEventsTB.TBControl = objTextBox.Object

'get testbox name
bxName = objTextBox.Name
bxNumber = Val(Replace(bxName, "TextBox", ""))

'get spinbuton name
SpinName = "SpinButton" & bxNumber
Set objSpinButton = ActiveSheet.OLEObjects(SpinName)
Set clsEventsTB.SBControl = objSpinButton.Object

'Add the event handler instance to our collection,
'so it stays alive during the life of the workbook
mcolEvents.Add clsEventsTB
mcolEvents.Add clsEventsSB
End If
Next
End Sub

Sub TerminateEvents()
'Here the collection of classes is destroyed so memory will be free
up:
Set mcolEvents = Nothing
End Su

 

[sarndt]

This worked great! I had been playing with it for awhile and still hadn't
figured it out (though I could get it to work with a UserForm and class
module. Just couldn't get it to work with a worksheet and class module. But
once I read thru your code, it all made total sense. I really appreciate the
time you put in to it...

Thanks

Steve

 

[joel]

There is one extra line of code

from
mcolEvents.Add clsEventsTB
mcolEvents.Add clsEventsSB

to
mcolEvents.Add clsEventsTB

I eliminate the clsEventsSB object. The TB object now handles bot
cases.

You may have more textboxes than spin controls on the worksheet and ma
want to reverse the code by searching for each spin control (instead o
each textbox) and then taking the same text box number as the spi
control

 

[sarndt]

Thanks for the additional information. While reviewing your modified code, I
had realized the same thing. So I went thru and cleaned up all of the code
to be cleaner and more consistent with what was really happening. Everything
works like I wanted.

Thanks again!

 

[sarndt]

Hi Josh

how would I modify the code to work with a textbox and spinbutton within a
frame (instead of having the textbox and spinbutton directly on the
worksheet)?

Thanks for the additional information. While reviewing your modified code, I
had realized the same thing. So I went thru and cleaned up all of the code
to be cleaner and more consistent with what was really happening. Everything
works like I wanted.

Thanks again!

There is one extra line of code

from
mcolEvents.Add clsEventsTB
mcolEvents.Add clsEventsSB

to
mcolEvents.Add clsEventsTB

I eliminate the clsEventsSB object. The TB object now handles both
cases.

You may have more textboxes than spin controls on the worksheet and may
want to reverse the code by searching for each spin control (instead of
each textbox) and then taking the same text box number as the spin
control.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=164025

Microsoft Office Help

.

 

[joel]

Attached at Thecodecage I have a very simple userform (what you called
frame). Look at the VBA project pane to see the code and the userform.

http://www.thecodecage.com/forumz/newreply.php?do=newreply&p=59807

+-------------------------------------------------------------------
|Filename: Userform Example.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=408
+-------------------------------------------------------------------

 

[sarndt]

Hi Joel

Just got to this after the holidays. Actually what I was looking for was
how to modify the previous code to handle multiple frames - each frame
containing a textbox and spinbutton. The code we worked on together handles
the textbox and spinbutton. But when these are included in the frame, I
can't seem to access the events/properties of the textbox and spinbutton
inside the frame - only the frame.

Thanks

Steve

 

[joel]

The Frames are meant for Radio Buttons so you can only select one at
time. You could all a Frame for physically associating your Spin butto
with the text box so the user can tell which button goes with whic
box.

I tried a few years ago to do what you are attempting by getting al
the objects in the Frame. It wasn't easy and didn't have any advantage
I was able to find the Frame name from each object but wasn't able t
get the all the objects in a Frame

 

[sarndt]

Sounds like you ran into the same problems that I had (getting references to
the objects in the frame). One thought I had was to get the event call for
the frame and then loop thru the objects in the frame to get the reference to
the right object. But I couldn't figure out a way to get the events for the
objects in the frame once I had control of the frame. And everytime I did a
property call against the frame when creating the events for the frame in the
module, the call came back with a reference to the textbox - not the frame
(based on .name). And because of this I couldn't ever actually get control
of the spinbutton in the frame. Unless this triggers some other thoughts - I
will go with the code you already provided. Thanks for all your help again!