Multiple User Forms to use a Single Module

B

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:

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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi,
you can have one common code for each of your userforms to call - you just pass the userform object to the code.


Place Code in a STANDARD module

VBA Code: 
Sub RET_CAL(ByVal Form As Object)
    
    'Add up the number of RET selected and turn red if over 6
    
    Dim X           As Double
    
    X = 0
    
    With Form
        If Val(.RET1) > 0 Then X = X + Val(.RET1.Value)
        If Val(.RET2) > 0 Then X = X + Val(.RET2.Value)
        If Val(.RET3) > 0 Then X = X + Val(.RET3.Value)
        If Val(.RET5) > 0 Then X = X + Val(.RET5.Value)
        If Val(.RET10) > 0 Then X = X + Val(.RET10.Value)
        If Val(.RET15) > 0 Then X = X + Val(.RET15.Value)
        If Val(.RET20) > 0 Then X = X + Val(.RET20.Value)
        If Val(.RET25) > 0 Then X = X + Val(.RET25.Value)
        If Val(.RET40) > 0 Then X = X + Val(.RET40.Value)
        
        .RETTOT = X
        
        If Val(.RETTOT.Value) <= 6 Then
        
            .RET1.BackColor = RGB(255, 255, 255)
            .RET2.BackColor = RGB(255, 255, 255)
            .RET3.BackColor = RGB(255, 255, 255)
            .RET5.BackColor = RGB(255, 255, 255)
            .RET10.BackColor = RGB(255, 255, 255)
            .RET15.BackColor = RGB(255, 255, 255)
            .RET20.BackColor = RGB(255, 255, 255)
            .RET25.BackColor = RGB(255, 255, 255)
            .RET40.BackColor = RGB(255, 255, 255)
            
        Else
            
            .RET1.BackColor = RGB(255, 163, 163)
            .RET2.BackColor = RGB(255, 163, 163)
            .RET3.BackColor = RGB(255, 163, 163)
            .RET5.BackColor = RGB(255, 163, 163)
            .RET10.BackColor = RGB(255, 163, 163)
            .RET15.BackColor = RGB(255, 163, 163)
            .RET20.BackColor = RGB(255, 163, 163)
            .RET25.BackColor = RGB(255, 163, 163)
            .RET40.BackColor = RGB(255, 163, 163)
            
        End If
    End With
    
End Sub

From any userform code needs to call you should just need this line of code

VBA Code: 
 RET_CAL Me

Dave
 
Upvote 0
dmt32 said:
Hi,
you can have one common code for each of your userforms to call - you just pass the userform object to the code.


Place Code in a STANDARD module

VBA Code: 
Sub RET_CAL(ByVal Form As Object)
   
    'Add up the number of RET selected and turn red if over 6
   
    Dim X           As Double
   
    X = 0
   
    With Form
        If Val(.RET1) > 0 Then X = X + Val(.RET1.Value)
        If Val(.RET2) > 0 Then X = X + Val(.RET2.Value)
        If Val(.RET3) > 0 Then X = X + Val(.RET3.Value)
        If Val(.RET5) > 0 Then X = X + Val(.RET5.Value)
        If Val(.RET10) > 0 Then X = X + Val(.RET10.Value)
        If Val(.RET15) > 0 Then X = X + Val(.RET15.Value)
        If Val(.RET20) > 0 Then X = X + Val(.RET20.Value)
        If Val(.RET25) > 0 Then X = X + Val(.RET25.Value)
        If Val(.RET40) > 0 Then X = X + Val(.RET40.Value)
       
        .RETTOT = X
       
        If Val(.RETTOT.Value) <= 6 Then
       
            .RET1.BackColor = RGB(255, 255, 255)
            .RET2.BackColor = RGB(255, 255, 255)
            .RET3.BackColor = RGB(255, 255, 255)
            .RET5.BackColor = RGB(255, 255, 255)
            .RET10.BackColor = RGB(255, 255, 255)
            .RET15.BackColor = RGB(255, 255, 255)
            .RET20.BackColor = RGB(255, 255, 255)
            .RET25.BackColor = RGB(255, 255, 255)
            .RET40.BackColor = RGB(255, 255, 255)
           
        Else
           
            .RET1.BackColor = RGB(255, 163, 163)
            .RET2.BackColor = RGB(255, 163, 163)
            .RET3.BackColor = RGB(255, 163, 163)
            .RET5.BackColor = RGB(255, 163, 163)
            .RET10.BackColor = RGB(255, 163, 163)
            .RET15.BackColor = RGB(255, 163, 163)
            .RET20.BackColor = RGB(255, 163, 163)
            .RET25.BackColor = RGB(255, 163, 163)
            .RET40.BackColor = RGB(255, 163, 163)
           
        End If
    End With
   
End Sub

From any userform code needs to call you should just need this line of code

VBA Code: 
 RET_CAL Me

Dave
Click to expand...
Many thanks for the reply Dave, I'll have a play this evening.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Need help trying to colour / uncolour multiple text boxes at once in a userform
Replies
2
Views
575
dmt32
D
Jaafar Tribak
  • Question
Blurring images using Direct2D & Windows Imaging Component
Replies
3
Views
283
Dan_W
D
M
  • Question
Need to add _AfterUpdate events to a dynamically-created class
Replies
1
Views
904
Matrovsky
M
Jaafar Tribak
  • Question
MultiColor + Drag N Drop ListBox Class (Win32)
Replies
8
Views
2K
Hasson
H
dss28
  • Question
multiple row data transfer from user form to same worksheet
Replies
0
Views
926
dss28
dss28

Forum statistics

Threads
1,215,515
Messages
6,125,279
Members
449,220
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top