none
Excel VBA - change the value of a combobox

    Question

  • Hello all

    I have a sheet with a combobox from the forms toolbar. I can't change the type of combobox (unfortunately !).
    When you change the selection in the combobox, it launch a macro (set by "Assign Macro ...") .

    I would like to be able to change the selection in the Combobox to launch the macro through an other macro (I can do that with other kind of comboBox/DropList).
    I can select the combobox with this code :
    ActiveSheet.Shapes("Drop Down 160").Select

    But I can't change its value.

    Any help will be greatly appreciate

    ericc
    Friday, April 10, 2009 11:40 AM

Answers

  • Hi,

    The following code will set the value of the dropdown box.

    ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value=1

    What will be displayed is the first item in the list.

    Changing the value will not trigger the assigned macro. You can call the private routine if you place you code in the same code module.
    Cheers
    Friday, April 10, 2009 12:51 PM

All replies

  • Ah aha ... sorry I found the solution ...

    To do that, I just have the change the value of the "Cell link"  O_o
    (take the problem in the reverse way)
    Only problem, the macro is not trigger automatically as I expected ... not a big deal, I will call it through my code !!

    If someone have a better solution ........

    ericc
    Friday, April 10, 2009 11:53 AM
  • Zut !! Sometime I feel like as I have no luck :-(
    The macro is declared as Private !!

    So back to the start : How to change the selected value in the ComboBox ?

    ericc
    Friday, April 10, 2009 12:29 PM
  • Hi,

    The following code will set the value of the dropdown box.

    ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value=1

    What will be displayed is the first item in the list.

    Changing the value will not trigger the assigned macro. You can call the private routine if you place you code in the same code module.
    Cheers
    Friday, April 10, 2009 12:51 PM
  • Hi Andy

    Thanks for your answer.
    I found several way to change the value in the dropdown box but as you say it's not trigger the assigned macro.
    And for some various reason, I can't place my code in the same module code ! :-(

    So now the question is :
    How to call a private sub from a different module (sheet in this case) ?
    Or how to trigger the assigned macro when I change the value ?

    ericc
    Friday, April 10, 2009 12:56 PM
  • Not sure which of these you will be allow to do.

    1. change assigned routine from private to public
    2. add public routine to sheet which calls private routine
    3. copy code in private routine to a public routine

    If you have no control over code, it's location or access then I guess the question then becomes why are you trying to manipulate it via code?
    Cheers
    Friday, April 10, 2009 1:06 PM
  • It's relatively simple.
    The Workbook was done by someone else.
    It's used to generate some financial statement so I prefer to not modify anything existing in anyway (if something wrong is discover I want to be sure that they can't put it on my fault)
    However, I would like to automate some tasks like exporting several report in one action by example.

    So I have created a separate Sheet with some routine inside, and I import it in the workbook. I have access to all the procedure from my own sheet.

    From your suggestion, the only possible way seems to copy the existing routine to my sheet !!

    Thanks

    ericc
    Friday, April 10, 2009 1:14 PM
  • Unless you can get the original creator to agree to make the routine public then yes.
    Of course you will need to make sure your routines match thiers whenever you get a new workbook.
    Cheers
    Friday, April 10, 2009 1:29 PM
  • Thanks, worked just fine...

     

    "ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value=1"

    Friday, January 13, 2012 2:05 PM