Fuctions based on control

E

electronicsgeek

New Member
Joined
Apr 3, 2002
Messages
28
I'm stuck on trying to accomplish the following:

I have a multipage form with identical controls. What I want to do is to create a sub or function that will only change items on the page which the contol is on. For example:

On MultiPage1 I have a combobox control named S1Type as well as another combo box named S1Option andon Multipage2 there are corresponding controls named S2Type and S2Option. The list goes on for several MultiPages.

Instead of having a function for each SxType combocontrol that changes the rowsource value for SxOption, I want to call a separate sub that takes in the value of which control called the sub and performs the changes to the other controls on the same multipage.

Basically if S1Type calls the sub then the changes are performed on S1Option but if S2Type calls the sub then only S2Option is changed... so on and so on for S3Type, S4Type, etc.

Is this possible?

Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Yes, its possible... I'do it in one of two ways:

1. Use a generic macro, passing the name of the "action" control as an argument, or the number, or some sort of identification that you can use.

2. Use a class module to handle the events of all those controls, and take actions based on the name of the control as well.
 
Upvote 0
You will need a Class module.

Code: 
'   Class module named CtrlClass

Public WithEvents CBGroup As MSForms.ComboBox

Private Sub CBGroup_Change()
    MsgBox "ComboBox " & CBGroup.Name & " changed"
End Sub

'    UserForm module

Dim CBs() As New CtrlClass

Private Sub UserForm_Initialize()
    Dim CBCount As Integer
    Dim Ctrl As Control
    CBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "ComboBox" Then
            If Right(Ctrl.Name, 4) = "Type" Then
                CBCount = CBCount + 1
                ReDim Preserve CBs(1 To CBCount)
                Set CBs(CBCount).CBGroup = Ctrl
            End If
        End If
    Next Ctrl
End Sub

In the code the Change event just puts up a message box giving the name of the Control. You will need to expand that to make the changes to the other Controls on the page.

Good luck.
 
Upvote 0
Thanks Andrew but I'm not quite sure if I understand fully.

What I'm trying to accomplish is on each page of a form all of the controls (Comboboxes) have the same first 2 characters
ie
Page1
S1Type
S1Color
S1Weight
S1Smell

Page2
S2Type
S2color
S2Weight
S2Smell

Page3
S3...


What I want to do is to write a separate sub that each SxType ComboBox would call on change. The sub would then go through and change the color, weight, and smell combo boxes with a default values based on what Type is selected. The sub take in the value of which SxType that called it so if the function was activated from S1Type then it would change S1Color, S1Weight, and S1Smell. If S2Type called the sub then it would change S2Color, S2Weight, S2Smell and so on and so on.

How can I get the sub to control the Comboboxes I need by changing the first two characters of each combobox? hope this is clear.

Thanks
 
Upvote 0
Well, I've given you the code that fires when an SxType ComboBox is changed. The first two characters can be assigned to a variable like this:

x = Left(CBGroup.Name, 2)

It's up to you to write the code to change the other Controls, eg:

UserForm1.Controls(x & "Color").RowSource = "Whatever"

I would use a Select Case construct.

Or you can do what Juan Pablo suggested - write a procedure that accepts the changed Control as an argument and call it from the ComboBox_Change event procedure.
 
Upvote 0
Thanks Andrew.... the problem I was running into was that I wasn't sure how to insert the variable for the control name.... what you showed above definitely helped out.

UserForm1.Controls(x & "Color").RowSource = "Whatever"

Thanks a ton!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Activating dynamically created checkboxes
Replies
2
Views
1K
Bosquedeguate
Bosquedeguate
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
398
AndyEd
A
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
326
pure vito
P
A
  • Question
Autofilter code based on username
Replies
1
Views
171
aabrazil1
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A

Forum statistics

Threads
1,214,940
Messages
6,122,356
Members
449,080
Latest member
Armadillos

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