Identifying last selected ComboBox

O

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
61
Hello everyone,

I have a userform with 20 Comboboxes and I need your advice to identify which ComboBox it was selected last time. If users wants to use this shortcut they will use a doubleclick on Combobox because the single click it needs also to enter a value. I put on doubleclick action this code:

VBA Code: 
Private Sub ComboBox5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Range("E1").Value = "ComboBox5"
End Sub


Every ComboBox will have a code like this. After this, i need to use this value to autocomplete, with a command button, a value but i don`t know how to declare this variables.

VBA Code: 
Private Sub CommandButton17_Click()
Dim X As Integer
X = Range("E1").Value
X.Value = "1"

I don`t know what is wrong here, can you help me with an advice ? It will be great if you have also another solution for this.

Thank you for your help !
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Not sure what you're trying to achieve in the end but you could store each combo box on enter in an object variable. That way the last entered combo box will be stored.

VBA Code: 
Option Explicit

Private Type TLocals
    LastEnteredComboBox As MSForms.ComboBox
End Type
Private this As TLocals

Private Sub ComboBox1_Enter()
    Set this.LastEnteredComboBox = ComboBox1
End Sub

Private Sub ComboBox2_Enter()
    Set this.LastEnteredComboBox = ComboBox2
End Sub

Private Sub ComboBox3_Enter()
    Set this.LastEnteredComboBox = ComboBox3
End Sub
 
Upvote 0
Solution
GWteB said:
Not sure what you're trying to achieve in the end but you could store each combo box on enter in an object variable. That way the last entered combo box will be stored.

VBA Code: 
Option Explicit

Private Type TLocals
    LastEnteredComboBox As MSForms.ComboBox
End Type
Private this As TLocals

Private Sub ComboBox1_Enter()
    Set this.LastEnteredComboBox = ComboBox1
End Sub

Private Sub ComboBox2_Enter()
    Set this.LastEnteredComboBox = ComboBox2
End Sub

Private Sub ComboBox3_Enter()
    Set this.LastEnteredComboBox = ComboBox3
End Sub
Click to expand...
Thank you for the Enter function idea. Is working great ! Can you give me also an example of what to use this information ? For example this:

VBA Code: 
Private Sub ComboBox4_Enter()
Set this.LastEnteredComboBox = ComboBox4
End Sub

I want to put on a command button a function to autocomplete something, let`s say value 1 on "Combobox4" but this Combobox i need to take it from your previous answer.

Thank you !
 
Upvote 0
You could put this code in the code module. Rename Userform1 to match your situation.
This is an custom class object approach to the problem, there is no need to use different code for each combobox.
VBA Code: 
Option Explicit

Public MyComboBoxes As Collection
Public WithEvents ClickedComboBox As MSForms.ComboBox
Public LastDoubleClickedComboBox As MSForms.ComboBox

Private Sub ClickedComboBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Set Me.UFParent.LastDoubleClickedComboBox = Me.ClickedComboBox
    Range("E1").Value =  Me.ClickedComboBox.Name
End Sub

Private Sub UserForm_Activate()
    Dim oneControl As MSForms.Control
    Dim newCCB As UserForm1

    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "ComboBox" Then
            Set newCCB = New UserForm1
            newCCB.Tag = "c"
            Set newCCB.ClickedComboBox = oneControl
            MyComboBoxes.Add Item:=newCCB
            oneControl.AddItem "x"
        End If
    Next oneControl
    Set newCCB = Nothing
End Sub

Private Sub UserForm_Initialize()
    Set MyComboBoxes = New Collection
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim oneObject As UserForm1
    For Each oneObject In MyComboBoxes
        Unload oneObject
        Set oneObject = Nothing
    Next oneObject
    Set MyComboBoxes = Nothing
End Sub

Public Function UFParent()
    Set UFParent = Me.ClickedComboBox
    If UFParent Is Nothing Then Set UFParent = Me
    On Error Resume Next
    Do Until Err
        Set UFParent = UFParent.Parent
    Loop
    On Error GoTo 0
End Function
Note that in the ClickedComboBox events, one needs to use Me.UFParent to refer to the userform that the user sees.
 
Upvote 0
otrava18 said:
Thank you for the Enter function idea. Is working great ! Can you give me also an example of what to use this information ? For example this:

VBA Code: 
Private Sub ComboBox4_Enter()
Set this.LastEnteredComboBox = ComboBox4
End Sub

I want to put on a command button a function to autocomplete something, let`s say value 1 on "Combobox4" but this Combobox i need to take it from your previous answer.

Thank you !
Click to expand...
I have tried this but it doesn`t work. i receive an error. "Run-time error '380' : Could not set the Value property."

VBA Code: 
Private Sub CommandButton17_Click()
this.LastEnteredComboBox.Value = "1"
End Sub

Do you have any idea ?

Thank you !
 
Upvote 0
@otrava18, I'm really sorry, but I'm afraid that I don't understand what you're finally trying to achieve.

otrava18 said:
I need your advice to identify which ComboBox it was selected last time.
Click to expand...

To that query we supplied two possible solutions. Mine wasn't the most efficient solution but it was meant to give you an idea, after all you let us know that…
otrava18 said:
i don`t know how to declare this variables.
Click to expand...

... and @mikerickson's solution was particularly focused on code reuse because you told us the following…
otrava18 said:
I have a userform with 20 Comboboxes
Click to expand...

Combo boxes are usually used to present the user with a (limited) choice.
A combo box should therefore have a population of items from which the user can choose.
If the MatchedRequired property of that particular combo box is set to True, any value other than a value from the population will error.
It looks like that's causing your current issue.
 
Upvote 0
mikerickson said:
You could put this code in the code module. Rename Userform1 to match your situation.
This is an custom class object approach to the problem, there is no need to use different code for each combobox.
VBA Code: 
Option Explicit

Public MyComboBoxes As Collection
Public WithEvents ClickedComboBox As MSForms.ComboBox
Public LastDoubleClickedComboBox As MSForms.ComboBox

Private Sub ClickedComboBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Set Me.UFParent.LastDoubleClickedComboBox = Me.ClickedComboBox
    Range("E1").Value =  Me.ClickedComboBox.Name
End Sub

Private Sub UserForm_Activate()
    Dim oneControl As MSForms.Control
    Dim newCCB As UserForm1

    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "ComboBox" Then
            Set newCCB = New UserForm1
            newCCB.Tag = "c"
            Set newCCB.ClickedComboBox = oneControl
            MyComboBoxes.Add Item:=newCCB
            oneControl.AddItem "x"
        End If
    Next oneControl
    Set newCCB = Nothing
End Sub

Private Sub UserForm_Initialize()
    Set MyComboBoxes = New Collection
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim oneObject As UserForm1
    For Each oneObject In MyComboBoxes
        Unload oneObject
        Set oneObject = Nothing
    Next oneObject
    Set MyComboBoxes = Nothing
End Sub

Public Function UFParent()
    Set UFParent = Me.ClickedComboBox
    If UFParent Is Nothing Then Set UFParent = Me
    On Error Resume Next
    Do Until Err
        Set UFParent = UFParent.Parent
    Loop
    On Error GoTo 0
End Function
Note that in the ClickedComboBox events, one needs to use Me.UFParent to refer to the userform that the user sees.
Click to expand...
Hello Mike,

I think i miss somthing, because for me is not working. I started a new workbook and a new userform1 but i receive an error when i put your code and i open the userform. The error is Run-time error '70': Permision denied. And debugging goes me to "oneControl.AddItem "x""
 
Upvote 0
GWteB said:
@otrava18, I'm really sorry, but I'm afraid that I don't understand what you're finally trying to achieve.



To that query we supplied two possible solutions. Mine wasn't the most efficient solution but it was meant to give you an idea, after all you let us know that…


... and @mikerickson's solution was particularly focused on code reuse because you told us the following…


Combo boxes are usually used to present the user with a (limited) choice.
A combo box should therefore have a population of items from which the user can choose.
If the MatchedRequired property of that particular combo box is set to True, any value other than a value from the population will error.
It looks like that's causing your current issue.
Click to expand...
I will try to explain better. I have let`s say 3 comboboxes in a userform, with possibility to select a numbers from 1 to 10. I want a button (to create a shortcut) for users, when they click on , the last selected userform to be autofilled, without dropdown, with a fixed value, let`s say 5. Your code even if in the dropdown i have a value, when i click the button i receive that error.
 
Upvote 0
otrava18 said:
Hello Mike,

I think i miss somthing, because for me is not working.
Click to expand...
It's working for me without issues.

From distance it's hard to tell what's going wrong on your side.
 
Upvote 0
GWteB said:
It's working for me without issues.

From distance it's hard to tell what's going wrong on your side.
Click to expand...
Did you use RowSource or your CB is empty ? For me is working if is it empty but i want to have something there.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
ComboBoxes - check if a value is selected.
Replies
3
Views
274
dreid1011
dreid1011
L
  • Solved
Dynamic Named Range not working with Userform ComboBox
Replies
6
Views
445
NoSparks
N
B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
228
BWL388
B
M
  • Question
ComboBox List selection to trigger code
Replies
1
Views
219
MichaelRSnow
M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
382
AndyEd
A

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

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