Use of Class to cover many similar controls?

AliWood

New member
Joined
May 1, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
Office365
Hello,
I have a VBA User Form with several numeric fields, with spin buttons against each one. I have SpinUp and SpinDown routines for each field and within these routines there is a mixture of code specific to the field and code that is common to all the Spin buttons (say to call a subroutine Update_figures). Is there a way of describing the buttons as a Class and holding the common code within the class? I am a beginner wrt the use of Classes but would like to learn more. Thank you.
Here's an example of one of the subroutines:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub SpnDueMonth_SpinDown()
If CDate(lblDueDate) - 30 >= CDate(lblRampDate) Then lblDueDate = UsualFormat2(CDate(lblDueDate) - 30): Update_figures Else Beep
End Sub


Many thanks[/FONT]
Ali
 
Make it easier for us to formulate a response by attaching a simple workbook with things set up with a user form, several spin buttons that you want to to behave similarly; it will allow us to trial and prevent us guessing (probably wrongly) what you have.
 
Assuming that the textboxes are called Textbox1, Textbox2, etc., the spinners are SpinButton1,
SpinButton2, etc, and the form is called Userform1.

Set the tag property for each spinbutton to its corresponding textbox ordinal number, 1, 2, etc.

Then in the userform, this code

Code:
[FONT=Verdana]Dim mcolEvents As Collection

[/FONT]
[FONT=Verdana]Private Sub UserForm_Initialize()
Dim BtnEvents As UserformEventSink
Dim ctl As msforms.Control
    
    Set mcolEvents = New Collection
    
    For Each ctl In Me.Controls
    
        If TypeName(ctl) = "SpinButton" Then
        
            Set BtnEvents = New UserformEventSink
            Set BtnEvents.mButtonGroup = ctl
            mcolEvents.Add BtnEvents
        End If
    Next
End Sub[/FONT]

Add a class module, mine is called UserformEventSink, wiuth this code

Code:
[FONT=Verdana]Public WithEvents mButtonGroup As msforms.SpinButton[/FONT]
[FONT=Verdana]Private Sub mButtonGroup_SpinDown()
    With UserForm1.Controls("Textbox" & mButtonGroup.Tag)
        .Value = Val(.Value) - 1
    End With
End Sub
Private Sub mButtonGroup_SpinUp()
    With UserForm1.Controls("Textbox" & mButtonGroup.Tag)
        .Value = Val(.Value) + 1
    End With
End Sub
[/FONT]
 
To
p45cal -
Spent an hour or two reducing the excel file to produce an extract for you , but the size of my file remains above 900kb (can't understand why, there's only one form and one subroutine!) so I cannot send.
To Bob - Thankyou very much will look at your code.
 
but the size of my file remains above 900kb (can't understand why, there's only one form and one subroutine!)
!!
Try saving as .xlsb, they're usually smaller.
 
saved as xlsb but now over the limit for xlsb files!! ( roughly 600 kb > 430 kb). It is as I say just one short module with one user form, this is crazy
 
Is there a lot of conditional formatting?

Or maybe a lot of shapes have somehow encroached, Home>Editing>Find & Select>Selection Pane.
 
Have applied your code carefully but must have a bug there. I watched the initialize routine go through its paces, but the spin button code is not being executed when I click.
Have thoroughly cleaned the file, but it is still 693kb on binary save, so can't send.
:eek:hwell: Limit seems harsh
Cheers for your patience,
Ali
 
Export all of your coide and form modules and post those.
 
Back
Top