problem repeat rowsorce combobox every time click another cell

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,163
Office Version
  1. 2010
hello
i have a problem i insert activeX combobox into sheet and i add this code every thing is ok in the first time but when i move from combobox to any cell i found the list in combobox is repeat continuously when i move to any cell
VBA Code:
Private Sub ComboBox1_DropButtonClick()
With ComboBox1
.AddItem "jan"
.AddItem "feb"
.AddItem "mar"
.AddItem "apr"
.AddItem "may"
.AddItem "jun"
.AddItem "jul"
.AddItem "aug"
.AddItem "sep"
.AddItem "oct"
.AddItem "nov"
.AddItem "dec"
End With

End Sub

i hope any body help
thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,206
Office Version
  1. 2007
Platform
  1. Windows
I don't understand why you load the combo every time you select it, but with the following you can solve it:

VBA Code:
Private Sub ComboBox1_DropButtonClick()
  With ComboBox1
    .Clear
    .AddItem "jan"
    .AddItem "feb"
    .AddItem "mar"
    .AddItem "apr"
    .AddItem "may"
    .AddItem "jun"
    .AddItem "jul"
    .AddItem "aug"
    .AddItem "sep"
    .AddItem "oct"
    .AddItem "nov"
    .AddItem "dec"
  End With
End Sub
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,163
Office Version
  1. 2010
hi, Dante actually i don't understand this statement

I don't understand why you load the combo every time you select it
my code supposes load the combobox one time and works normally without any repeat every time i select it
but when add ".clear" it become the combobox is empty when i choose item not show the selected item
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,206
Office Version
  1. 2007
Platform
  1. Windows
Try this
VBA Code:
Private Sub ComboBox1_DropButtonClick()
  Dim dato
  With ComboBox1
    dato = .Value
    .Clear
    .AddItem "jan"
    .AddItem "feb"
    .AddItem "mar"
    .AddItem "apr"
    .AddItem "may"
    .AddItem "jun"
    .AddItem "jul"
    .AddItem "aug"
    .AddItem "sep"
    .AddItem "oct"
    .AddItem "nov"
    .AddItem "dec"
    .Value = dato
  End With
End Sub
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,163
Office Version
  1. 2010

ADVERTISEMENT

yes, that works , could you explain me why my code doesn't work correctly in the normal situation and when you define the variable what's the type and what means this at begening code and end code?
dato = .Value
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,206
Office Version
  1. 2007
Platform
  1. Windows
I declare the variable, you should always declare variables. It should actually be
Dim data as string
I was missing that part.

At the beginning dato = .value, it passes the value of the combo to the variable "dato" and at the end the content of the "dato" is returned to the combo.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,206
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,958
Members
425,653
Latest member
UNSING

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
Top