Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
How ar eyou initializing the Class? Every class need to get initialized from a non class module. Since a class can't get called from the worksheet directly you need to make the first call from a non class module. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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" wrote: How ar eyou initializing the Class? Every class need to get initialized from a non class module. Since a class can't get called from the worksheet directly you need to make the first call from a non class module. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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/sh...d.php?t=164025 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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" wrote: 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/sh...d.php?t=164025 Microsoft Office Help . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
You can do it any way you want to as long as you save each instance of the class along with the name. What you said is correct. I did code like this back college 30 years ago and a few times since. the Theory hasn't changes, just the programming languages. What you are doing is creating a min-database and adding new records (in your case classes). The theory behind databases never really change over the years. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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 "sarndt" wrote: 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" wrote: 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/sh...d.php?t=164025 Microsoft Office Help . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
It was easier than I though. You want Textbox1 and Spinbutton1 assigned to the same class then Textbox2 and Spinbutton2. Make sure your names of the associated textboxes and the Spinbutton have the same number assigned. Then use the code I modified below. You need only one class 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 freed up: Set mcolEvents = Nothing End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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" wrote: You can do it any way you want to as long as you save each instance of the class along with the name. What you said is correct. I did code like this back college 30 years ago and a few times since. the Theory hasn't changes, just the programming languages. What you are doing is creating a min-database and adding new records (in your case classes). The theory behind databases never really change over the years. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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/sh...d.php?t=164025 Microsoft Office Help |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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! "joel" wrote: 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/sh...d.php?t=164025 Microsoft Office Help . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
Attached at Thecodecage I have a very simple userform (what you called a frame). Look at the VBA project pane to see the code and the userform. http://www.thecodecage.com/forumz/ne...reply&p=598073 +-------------------------------------------------------------------+ |Filename: Userform Example.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=408| +-------------------------------------------------------------------+ -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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" wrote: Attached at Thecodecage I have a very simple userform (what you called a frame). Look at the VBA project pane to see the code and the userform. http://www.thecodecage.com/forumz/ne...reply&p=598073 +-------------------------------------------------------------------+ |Filename: Userform Example.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=408| +-------------------------------------------------------------------+ -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
The Frames are meant for Radio Buttons so you can only select one at a time. You could all a Frame for physically associating your Spin button with the text box so the user can tell which button goes with which box. I tried a few years ago to do what you are attempting by getting all 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 to get the all the objects in a Frame. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox and SpinControl - Class
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! "joel" wrote: The Frames are meant for Radio Buttons so you can only select one at a time. You could all a Frame for physically associating your Spin button with the text box so the user can tell which button goes with which box. I tried a few years ago to do what you are attempting by getting all 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 to get the all the objects in a Frame. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpinControl on PopUp CommandBar ? | Excel Programming | |||
Class modules: parametrize class object fields | Excel Programming | |||
How can I expose the TextBox BeforeUpdate event to my user class? | Excel Discussion (Misc queries) | |||
Class module to filter textbox entry | Excel Programming | |||
MSForms.TextBox Exit event isn't available in Excel class mosule | Excel Programming |