Macro needed to enable and disable an Active X ComboBox

Paul103 · Feb 26th 2015

Hi,
I need a macro to enable and disable an Active X ComboBox that actually works. I have tried the code below but it does not work - any ideas would be greatly appreciated.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("R2").Value = True Then ComboBox1.Enabled = True
    If Range("R2").Value = False Then ComboBox1.Enabled = False
End Sub

ashu1990 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

tried your code it worked for me....

try giving the sheet name before the combobox name.. eg:- sheet1.combobox1.enabled = true

Paul103 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

Ashu

I have tried your suggestion but it still does not work so have uploaded the workbook. The idea behind this is to be able to edit the addresses that the ComboBox throws up as we delivery to many companies that have multiple departments at the same address. So why have to search through 600 addresses when 60 will do the job with a little editing. Have a look and see what you make of it.
Paul

ashu1990 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

sure currently in office can downolad your file here will look when i get back home...

ashu1990 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

there's some problem in your file the code doesn't work at all tried deleting the combobox but the excel crashes every time i tried...

suggest you to create a new workbook and try it as the code is proper

Paul103 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

Ashu,
Thanks for taking a look and I will recreate a new workbook as per your suggestion, however this will be at the weekend now but I will keep you informed of how I get on.

**royUK** · Feb 27th 2015

Re: Macro needed to enable and disable an Active X ComboBox

Try this code

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$R$2" Then Exit Sub
 Me.Shapes.Range("ComboBox1").Enabled = Target.Value
End Sub

Paul103 · Feb 27th 2015

Re: Macro needed to enable and disable an Active X ComboBox

Hi,

Thanks Ashu and thanks Roy!

Both your codes work as long as I remove the code I have for the ComboBox, however I had brain wave this morning and came up with the following get around which works perfectly and does exactly what I want. Firstly I removed the listbox and associated data and then put in the sub below which is started by a shape.

Code

Sub CopyAddress()
' CopyClientDetails Macro
    Application.ScreenUpdating = False
    Range("C5:C12").Copy
    Range("AA5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ComboBox1.Value = ""
        Range("AA5:AA12").Copy
        Range("C5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Exit Sub
    Application.ScreenUpdating = True
    
End Sub

Display More

I thought I may be able to just copy the address, then reset the ComboBox value and finally paste back but this also does not work hence the copy/paste reset ComboBox copy/paste sequence.

Macro needed to enable and disable an Active X ComboBox

Participate now!

Share