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

 

[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.

 

[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?

 

[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

.

 

[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

 

[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

.

 

[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

 

[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!