Macro needed to enable and disable an Active X ComboBox

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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

    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

    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

    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... :thumbup:

    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

    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.

    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

    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.


    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login