Bobbybazza
New Member
- Joined
- Mar 7, 2013
- Messages
- 5
Hi,
I have multiple userforms that each have a frame that contain a number of identically named Textboxes. For each of these Userforms I have a corresponding Module that contains VBA for carrying out a calculation or enabling the textboxes for example that the Userform calls. Is it possible to have a single module that each of the userforms can call. The problem I have, and you must make allowances for my lack of VBA knowledge because to be honest I'm winging it, is that each textbox in the module is referred to by UserformX.textboxname. If I remove the UserformX part the problems begin.
The way I have it works, but I'm sure there must be a better way.
Here is an example of the module code, where the text box is called RET1, RET2 etc:
Thanks in Advance
BB
I have multiple userforms that each have a frame that contain a number of identically named Textboxes. For each of these Userforms I have a corresponding Module that contains VBA for carrying out a calculation or enabling the textboxes for example that the Userform calls. Is it possible to have a single module that each of the userforms can call. The problem I have, and you must make allowances for my lack of VBA knowledge because to be honest I'm winging it, is that each textbox in the module is referred to by UserformX.textboxname. If I remove the UserformX part the problems begin.
The way I have it works, but I'm sure there must be a better way.
Here is an example of the module code, where the text box is called RET1, RET2 etc:
VBA Code:
Sub RET_CAL()
'Add up the number of RET selected and turn red if over 6
Dim X As Double
X = 0
If Len(UserForm1.RET1) > 0 Then X = X + UserForm1.RET1.Value
If Len(UserForm1.RET2) > 0 Then X = X + UserForm1.RET2.Value
If Len(UserForm1.RET3) > 0 Then X = X + UserForm1.RET3.Value
If Len(UserForm1.RET5) > 0 Then X = X + UserForm1.RET5.Value
If Len(UserForm1.RET10) > 0 Then X = X + UserForm1.RET10.Value
If Len(UserForm1.RET15) > 0 Then X = X + UserForm1.RET15.Value
If Len(UserForm1.RET20) > 0 Then X = X + UserForm1.RET20.Value
If Len(UserForm1.RET25) > 0 Then X = X + UserForm1.RET25.Value
If Len(UserForm1.RET40) > 0 Then X = X + UserForm1.RET40.Value
UserForm1.RETTOT = X
If UserForm1.RETTOT.Value <= 6 Then
UserForm1.RET1.BackColor = RGB(255, 255, 255)
UserForm1.RET2.BackColor = RGB(255, 255, 255)
UserForm1.RET3.BackColor = RGB(255, 255, 255)
UserForm1.RET5.BackColor = RGB(255, 255, 255)
UserForm1.RET10.BackColor = RGB(255, 255, 255)
UserForm1.RET15.BackColor = RGB(255, 255, 255)
UserForm1.RET20.BackColor = RGB(255, 255, 255)
UserForm1.RET25.BackColor = RGB(255, 255, 255)
UserForm1.RET40.BackColor = RGB(255, 255, 255)
End If
If UserForm1.RETTOT.Value > 6 Then
UserForm1.RET1.BackColor = RGB(255, 163, 163)
UserForm1.RET2.BackColor = RGB(255, 163, 163)
UserForm1.RET3.BackColor = RGB(255, 163, 163)
UserForm1.RET5.BackColor = RGB(255, 163, 163)
UserForm1.RET10.BackColor = RGB(255, 163, 163)
UserForm1.RET15.BackColor = RGB(255, 163, 163)
UserForm1.RET20.BackColor = RGB(255, 163, 163)
UserForm1.RET25.BackColor = RGB(255, 163, 163)
UserForm1.RET40.BackColor = RGB(255, 163, 163)
End If
End Sub
Thanks in Advance
BB