Generic Sub for Image control in Userform

My final version will have more than 40 images with all the same code: they will all increment a cooresponding SpinButton with 1

Please Login or Register  to view this content.

I would like to limit the need to write that many subs and tried to create a Class Module. This works for my Textboxes & SpinButtons but not for my Images. What did I do wrong?

Please Login or Register  to view this content.

I added following code in UserForm1. It works for the textboxes & spinbuttons.

Please Login or Register  to view this content.

Are you sure you don't mean aImage_click in your class?

Hello Kyle123, I created a class module and changed the name into "clsCommonImage" (see code above). In that module I placed the Private Sub aImage_Change() and added then the code as described in the Private Sub UserForm_Initialize(). This functions perfectly for my TextBoxes ans SpinButtons but doesn't seems to be the right code for Images

I'm changing the values of the Spinboxes by clicking on the Images. I changed now the Class Module code as shown below (modified Image_Change into Image_Click) and now it works partially.
I do stiil have following issues:
1. The start value of all my SpinBoxes (or linked TextBoxes) is now 1 in stead of empty
2. Clicking on one of the Images increments all of my Spinboxes to 2 (clicking again and everything becomes 3 e.g.)
3. Clearing the form sets the value of My Spinboxes again to 1 (and not empty as I wanted them to be)
When I change the pictures into commandbuttons with a picture I get a similar result, so there must still be something wrong
[CODE]
Public WithEvents aImage As MSForms.Image

Private Sub aImage_Click()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ColNum As Integer

ColNum = Sheet1.Range("Table1").Cells(2, Columns.Count).End(xlToLeft).Column '# columns in Table1
k = ColNum - 3
For i = 4 To k
UserForm1.Controls("SpinButton" & i).Value = UserForm1.Controls("SpinButton" & i).Value + 1
Next i
End Sub
[\CODE]

Indeed, the images do not change. I did try the Sub you suggested but doing this changes all the spinbutton values instead of just the one connected to the spinbutton. Any idea how to solve this?

If forgot to anwer your first question: If a click on an image (for example image4), the value of the spinbutton4 and textbox4 should go up with 1. Idem for all the other images/spinbuttons/textboxes.
Making private sub for all these images works perfectly but I wanted a generic code so if images are added or deleted I do not have to change my codes. It works with textboxes and spinbuttons (tested) but so far not for images or commandbuttons

You need to merge your spin button and text box classes with your image class and update the pin button in the class

Do you mean that I have to make only 1 class module with 3 private subs named aTextbox_change, aSpinButton_Change and aImage_change?

Yes, but you want the image click rather than change wouldn't you?

Sure (my mistake). I'll give it a try tomorrow and give you feedback. Thanks a lot for the tips!

Unfortunately no change. Clicking on any of the images still increments ALL the TextBoxes and SpinButtons
I made a new class1 named clsSharedControl and merged as you suggested. Used CommandButtons with images in stead of Images, but this should not make a difference I guess
[CODE]
Public WithEvents aTextBox As MSForms.Textbox
Public WithEvents aSpinButton As MSForms.SpinButton
Public WithEvents aCommandButton As MSForms.CommandButton


Private Sub aTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Rem integer entry only

If Not (Chr(KeyAscii) Like "#") Then
KeyAscii = 0
MsgBox "Numbers Only !"
End If
End Sub

Private Sub aTextbox_Change()

Dim i As Integer ' i = Textbox number indicator, starts at column #6 ( Column F)
Dim j As Integer
Dim k As Integer
Dim ColNum As Integer
Dim xSum As Double


ColNum = Sheet1.Range("Table1").Cells(2, Columns.Count).End(xlToLeft).Column '# columns in Table1
xSum = 0

k = ColNum - 3

For i = 4 To k ' Start calculating from Textbox4, stop calculating with last Textbox (in this case PR3)

xSum = xSum + Val(UserForm1.Controls("TextBox" & i).Object.Value) * Cells(1, i + 2).Value
UserForm1.Controls("SpinButton" & i).Value = Val(UserForm1.Controls("TextBox" & i).Object.Value)
Next i

UserForm1.Label2.Caption = Format(xSum, "0.00 €")


End Sub

Private Sub aSpinButton_Change()

Dim i As Integer ' i = SpinButton number indicator, starts at column #6 ( Sheet1 Column F = the first product)
Dim j As Integer
Dim k As Integer
Dim ColNum As Integer



ColNum = Sheet1.Range("Table1").Cells(2, Columns.Count).End(xlToLeft).Column '# columns in Table1
xSum = 0

k = ColNum - 3

For i = 4 To k ' Start calculating from SpinButton4, stop calculating with last Textbox (in this case PR3)

If UserForm1.Controls("SpinButton" & i).Value = 0 Then

UserForm1.Controls("TextBox" & i).Text = ""

Else

UserForm1.Controls("TextBox" & i).Text = UserForm1.Controls("SpinButton" & i).Value

End If

Next i



End Sub


Private Sub aCommandButton_Click()

Dim i As Integer ' i = CommandButton number indicator, starts at column #6 ( Sheet1 Column F = the first product)
Dim j As Integer
Dim k As Integer
Dim ColNum As Integer



ColNum = Sheet1.Range("Table1").Cells(2, Columns.Count).End(xlToLeft).Column '# columns in Table

k = ColNum - 3

For i = 4 To k ' Start from CommandButton4, stop calculating with last CommandButton (in this case PR3)

UserForm1.Controls("SpinButton" & i).Value = UserForm1.Controls("SpinButton" & i).Value + 1


Next i


End Sub
[\CODE]
And in my Userform I've put following code
[CODE]
Option Explicit
Dim myTextBoxes As Collection
Dim mySpinButtons As Collection
Dim myCommandButtons As Collection

Private Sub UserForm_Initialize()

Dim aCommonTextBox As clsSharedControl
Dim aCommonSpinButton As clsSharedControl
Dim aCommonCommandButton As clsSharedControl
Dim oneControl As MSForms.Control


Set myTextBoxes = New Collection

For Each oneControl In Me.Controls
If TypeName(oneControl) = "TextBox" Then
If oneControl.Name <> "TextBox3" Then
Set aCommonTextBox = New clsSharedControl
Set aCommonTextBox.aTextBox = oneControl
myTextBoxes.Add Item:=aCommonTextBox
End If
End If
Next oneControl
Set aCommonTextBox = Nothing


Set mySpinButtons = New Collection

For Each oneControl In Me.Controls
If TypeName(oneControl) = "SpinButton" Then
Set aCommonSpinButton = New clsSharedControl
Set aCommonSpinButton.aSpinButton = oneControl
mySpinButtons.Add Item:=aCommonSpinButton

End If
Next oneControl
Set aCommonSpinButton = Nothing


Set myCommandButtons = New Collection

For Each oneControl In Me.Controls
If TypeName(oneControl) = "CommandButton" Then
Set aCommonCommandButton = New clsSharedControl
Set aCommonCommandButton.aCommandButton = oneControl
myCommandButtons.Add Item:=aCommonCommandButton

End If
Next oneControl
Set aCommonCommandButton = Nothing


TextBox1.SetFocus

End Sub
[\CODE]

Thanks for the tips. It works now

[SOLVED]